Need opinions on Access 97 to 2007 migration

[BrianBurgit]

We have an Access 97 database application that is the heart of our business. 20 users. I inherited this system when I started working here 5 years ago. I’m considering upgrading to Access 2007, but my testing so far of the conversion has resulted in noticeably slower performance with 2007.

If my users deem the speed and other quirks of Access 2007 unacceptable and want to stay with Access 97, in your opinion, is this a bad idea? My concern is mainly that we’re using such old technology. I do have the Access 97 version using an Interop module so that I can use a bunch of .NET code that I’ve written. I just feel that the gap between Access 97 and where .NET is heading is getting bigger all the time, and that it would be a much better technical solution to be using .NET with something much more current. But maybe I’m making an issue out of something that really isn’t one. I’d appreciate any opinions you may have on this issue.

Thanks,

Brian

 

[smedvid]

Just some thoughts/observations...

1. While you are converting to a newer version, can this be an opportunity to revisit the design and optimize the schema perhaps? I would make an argument for this since the benefit can be a great performance gain. Breaking out tables into a more normal form would result in better performance. Adding indexes can also result in much better performance.

2. Did you consider MS Access 2010. I have been using it; and it seems a little faster than MS Access 2003 in my benchmarks that I have run. Plus it is always better to stay current (but no bleeding edge) as possible. Access 97; is just too old to manage plus support for that version from MS has to be non-existent, which is a risk, imho.

3. Since you mentioned .Net; I would also consider development in VS 2010 with latest .Net; again to stay current. Plus some of the improvements in performance are certainly notable imho between previous versions of VS.

Bottom line, this upgrade will need to be done one-way or the other. If not today; then in the very near future.

You did not specify what performance or quirks from Access-2007 the users encountered that were issues. Perhaps post some more details and others may have some thoughts.

Good Luck!


Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[BrianBurgit]

Thanks for the feedback Steve. We're not in a position to consider design changes at this time. Long term stratgey is to build a new .NET application to replace Access, but cuts in IT department and slow business means this will not happen any time soon. Therefore, I'm hoping to upgrade to Access 2007 so I can concentrate on more important business needs while not feeling like I have a time bomb with Access 97 waiting to become a major problem.

Specific issues I've seen with Acces 2007 is just that the app is slower everywhere. Most Forms are bound to tables or queries, and they open slower in 2007. Also running into some pieces of code that don't seem to work as they did in Access 97. Mostly small things that I can correct.

 

[lameid]

I have not used Access 2007 excet to help someone once or twice but when I firsted loaded it the computer I had at the time only had 1 GB of RAM and MS had not fixed the installation issue for switching between versions... All of Office 2007 crawled on that machine. Since you have resource issues, I have to ask if it is the hardware? Also have you considered using a 2000 or 2003 version file with it?

The next thought I have is does the database size grow significantly? Is the Server / Network setup to optimally for Access? I have had to put Access backends on their own logical drive for performance reasons.

A question I simply do not know the answer to... Is DAO still the fastest option with the new file format (or is it an option)?

 

[BrianBurgit]

My machine has 4GB of memory. I am now trying a conversion to 2003, we'll see what the results are. The database is about 350mb back end, and 200mb front end, does not grow quickly. Good thought about DAO, I will need to investigate that. Thanks for the ideas.

 

[boblarson]

A question I simply do not know the answer to... Is DAO still the fastest option with the new file format (or is it an option)?

Yes, it is. And DAO in the new file format isn't named DAO in the references - it is Microsoft Office Access Database Engine Object Library (just an FYI).

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[smedvid]

mmmmm... I thought ADO was faster; especially if you can VBA the code to use all SQL. I even recall doing some bench marking a couple of years ago; with a large data set (~2 million record table)... In my case for my design, ADO was the better solution over DAO. htwh.

Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[boblarson]

DAO should be faster than ADO because DAO is NATIVE and ADO is a translation layer.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[lameid]

Just to make sure it is completely un-ambiguous...

DAO is fastest if the backend is in Native Access format.

ADO is fastest for any other backend, like SQL Server.

 

[Hap007]

Hi Brian,

I am proposing a similar conversion for a client from Access 97 to a newer version of Access.

My experience with Microdoft has been, for every new release of a MS Product, you need a faster system with more memory. So if hardware is an issue, maybe you only want to upgrade to MS Access 2002(XP) or MS Access 2003.

One problem with Access 97 is the 1 gig database file size limit. So, moving to any version from 2000 up will increase the size limit to at least 2 gig.

For my project, I am try to decide if moving to 2007 is worth the extra hardware requirements and possible minor code issues since the client does not require any of the newer features.

Regarding support, if you just make sure that you have the latest updates for any Microsoft product, saved at your own location, you should be good to go. Seems Micrsoft removes product updates when they discontinue supporting a product and if all you have are the original CD's well you can be in trouble.

Good Luck,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[BrianBurgit]

Thanks for the advice Hap007. I'm in the process of testing a conversion to 2003 instead of 2007. 2003 definitely performs better and the conversion had less issues. And everyone here already has Office 2003, whereas I'd need to upgrade most people to 2007. So, 2003 looks like where I'll end up, and I'll feel better than being stuck at 97.

 

[assets]

When I up grade from 97 to 2003 I needed to save as 2000 first this solved problem. Also things are different so may not all work as expected.

Never give up never give in.

There are no short cuts to anything worth doing