MS-access front end with sybase Sql Anywhere database, pros and cons

Question

Am considering moving my simple ms-access 2003 client server desktop application to one with an ms-access interface and sybase 10 or 11 sql Anywhere backend database. Why? because i want to: - take advantage of the easy & quick way access can build forms and reports (which i am already familiar with ... meaning less learning curve for me) - take advantage of the reliable and efficient way sybase dbs can be set up to synchronize data across various types of networks using mobilink.

I dont want to use access replication because the data will eventually grow to more than 2GB in the back end.

So, - What are the Pros & cons of this approach? - can i just link to the sybase database tables using a DSN or connectionstring in VBA and proceed? - will navigating and updating/deleting records through the ms-access forms need any special considerations?

thanks for the help

Answer 1

Yes, you should be able to use ODBC linked tables to your Sybase database, assuming Sybase provides compatible ODBC drivers. However, I'd suggest not using a DSN, but a DSN-less connection instead. What I do is have a DSN defined on my programming PC, and after linking the tables, I run Doug Steele's code to convert to DSN-less connect strings.

The app should mostly just work, but there are some things that may need tweaking. Don't expect any performance improvement -- indeed, it's more likely you'll lose performance. You can then examine the parts of the app that aren't performing well and evaluate how to make them work better with a server back end. Usually this involves creating views on the server, or writing stored procedures (for things such as inserting or updating records), or using passthrough queries (so that Jet doesn't touch the SQL and just passes it off to the server for execution).

This can be complicated, though I found out that it wasn't as hard as I expected the first time I ever upsized.

Answer 2

Having done both, I can heartily recommend the linked table option as preferred.

In the past, I've worked with Access frontends to MS-SQL Server backends. In the main, it's the same as working with local access tables, but there are a few exceptions. I've not worked with SQL Anywhere, but MS_SQL had issues with things like Autonumber fields for instance (different behavior to Access).

I've also recently worked on an Access frontend to a MS-SQL backend, where the normal access connection methods weren't used, in favor of Table-based-functions and stored procedures...this was an absolute nightmare to code, and pretty much offset all of the upsides to working with Access as a Rad tool. If I were to do this again, I would instead insist on forgetting about Access, and using VB.Net for the frontend. Given how good MS Report Server is these days, there really is very little reason to use Access any more.