(DAO) Performance Difference Between Versions?

[lameid]

I have several databases in Access 97 still that have a lot of DAO code (possibly 4 or 5 recordsets open concurrently). I am hoping to end of life the applications with a rewrite in SQL Server and a .NET flavor (no point in looking at ADO for the Access version). In the mean time (6 months to a year), forcing an office 97 app on some systems is taking a toll.

I have seen several different opinions about whether DAO is any slower in Access 2kX. In my case I would be converting my 97 databases for use with 2003 (option of 2000 and 2003 file format). Is there a speed difference between DAO of the two systems or is there some other performance gotcha? I think the 97 data format is smaller in size for the same data but I could be mistaken. This would of course mean network throughput becomes a bigger issue.

In the case where performance is a concern, there are already slow downs being noticed from concurrent users as well as database size.

Perhaps the difference is one of a threshold... as long as my systems have X RAM there is no difference?

I know that there are probably several variables but any insight as to how they affect performance differently between versions would be appreciated.

 

[BSman]

I've gone through the agony of not only Access 97 to 2000, but having Access 97, 2000, and 2003 all in existence at the same time among my many users at different locations in the company. The applications have included ones with Access (97) back ends as well as ones with SQL 7 and now SQL 2000 back ends. Fortunately, I have just recently been able to eliminate almost all Access 97 applications except for one or two very small ones (with Access back ends). But here are some things I've learned and now practice:

1. Yes, DAO works fine moving from 97 to 2000 to 2003.

2. An Access 97 back end works fine with 2000 or 2003 front ends. Obviously, once all users are upgraded you should upgrade the back end appropriately.

3. An Access 97 application should NEVER be opened with Access 2000 or 2003. In other words the question about converting or opening while in the older version should not be answered to keep it in the older version. You just will slow it down while keeping it in 97. Far better to create a second version which has been converted up to 2000 (or 2003) and make it available to your users with the later version. If the original application was MyApp.mde, then the others would be named MyApp2K.mde and MyApp2K3.mde. Normally the conversion is just the straight Access conversion with no extra work on your part.

4. Always have a separate version for Access 2000 and 2003 users (i.e., using the Access convert routine). I have had situations where a user might open a database using Access 2000 and if a 2003 user then tries to open it gets a message that it cannot be opened because it is locked by the Admin user (which is not true). This is a documented program in the Microsoft site, so having 2003 users use their own version (even if you just copy the MyApp2K.mde version to MyApp2K3.mde for them to use with running the conversion routine).

5. While there may be some differences in performance, the real problem occurs when you have users for the same application that have different version of Access.

6. I've also made it a standard practice to ALWAYS identify a single column primary key in every SQL table. If there is not a unique value in a single column in the regular data, I add an identify (autonumber) column to the table and set it as the primary key. This not only seems to speed things up, but it also insures that when you link a table (or re-link the table) you won't have to try remembering which columns constitute a unique value for Access to handle as a primary type key (when there is no single column primary key in SQL).

7. It's also a good idea to consider the use of pass through queries where possible when using a SQL back end, since it can dramatically cut down network traffic and the selection and sorting is performed at the SQL Server, not at the workstation. Of course, you must construct the statement to include any values, since references to a value in an Access control (on a form or report) will mean nothing to SQL Server.

Hope this helps.

Bob

 

[lameid]

BSman,

My question is specific to performance. My problem is our Billing system is in Access 97 and as I noted has already exhibited some signs of slow down. So what I really haven't seen you answer is if there are any appreciable performance differences for similar levels of complexity. As you probably know, if I simply upsize my database to SQL server without SPT queries and the like, performance will drop. This is not really a good option either as time should be spent on the new system (design flaws from the original author).

As you mentioned, the upgrade would of course be one of a phased rollout. My problem is that I can't afford an appreciable loss in performance for complex VBA modules. I guess I could load both versions on a system and let a user try both versions. I was just hoping for some good insight first.

 

[BSman]

I haven't seen a significant difference in performance except where users with Access 2000 use the same version as Access 97 users, rather than a version that was converted by Access to Access 2000.

Bob