Access 97 upgrade to 2007 or 2010... speed?

[accessi]

Hello there,

I have programmed in access 97 for over 5 years but I consider myself a newbie because... well I programmed in ACCESS 97 for 5 years. Excuse me as this is my first post.

Long story short, Yes i know i need to upgrade, and i convinced the bosses to let me do so. They didn't give me a price point, so now i am asking for some vetran/senior access programmers opinions.

We have a MS SQL server 2000 on a windows 2003 server backend and are using ACCESS 97 as a front end for our programming and data manipulation. We use mainly VB. I was wondering what you folks thought about ACCESS 2007 vs 2010. Besides the info I can read on wiki about ACCESS on wikipedia, can anybody tell me which they think is better?

We are mainly after speed. We are willing to consider upgrading SQL Server but that depends on pricing.

[pkstormy]

MSAccess 2010 brought back some of the menu looks that are somewhat similar to MSAccess 97 (and 2000/2003). The biggest gripe from developers about MSAccess 2007 is that Microsoft moved all the menu's around for 2007 which makes it a horrible mess for those developers familiar with previous MSAccess versions.

But keep in mind that 2010 still hasn't worked out all the bugs. My initial testing of 2010 showed that there was still quite a few kinks Microsoft still needs to work out yet with 2010. The biggest one being the Calendar control which Microsoft idiotically decided to change so previous MSAccess versions that used this control would no longer work and the control needed to be replaced (but Microsoft has done some pretty stupid things.) It's quite obvious though that Microsoft geared 2007 around new developers and disregarded the opinions of any veteran MSAccess developers (thanks Microsoft! (sarcasm here)). I personally won't use 2007 since it takes me twice as long to develop with it. The extra time involved being due to finding where MS decided to put all the options in the menu system.

If you want an MSAccess menu type system that is 'somewhat' close to 97, you'd want to go to 2010 (if you can deal with the kinks.) Otherwise 2007 has most of the bugs worked out and is more upsizing friendly if you can deal with the 'mess' of the menu system in 2007.

In regards to SQL Server, I still like SQL Server 2000. It is simple and non-bloated. But I'm getting used to SQL Server 2005/2008 and it offers a lot of new stuff which I doubt I'll ever use. Still, if you're heavy into SQL Server and want to do a lot of new things with the data (such as reporting services which I found a bit complicated), upsizing to SQL Server 2008 would be ideal. If you only need SQL Server to do the basics, I'd stick with the SQL Server you have. You'll need a dba who knows the ins/outs of SQL Server to really take advantage of the new features in 2005/2008. You won't get though a huge speed boost by upgrading. For that I would concentrate on vba coding techniques and utilizing 'unbound' forms in MSAccess for the best speed which would be close to an app designed in Visual Basic. (the 'unbound' forms technique will give you the best speed for your MSAccess app since the form would then open not bound to a table itself. You would then populate the values on the form via functions to open a recordset and send the values to/from the form - similar to designing a VB app except that you could link the SQL Server tables into the MSAccess app to make things easier and take advantage of queries.) For optimal speed, design your main data forms as 'unbound' and utilize simple popup type forms for relational tables where updating this data is easiest with a bound form. 'Unbound' forms will open extremely fast (unless you throw in a bunch of comboboxes/listboxes on the form - these will delay the unbound form since the rowsource for these controls is bound to a table.)

I designed unbound type forms for my app which had a backend SQL Server and had 4-5 million records. Using unbound forms, my external users could open the main data form quickly. Since the form was unbound, they could edit the data on the form without any hesitation and they would then click a 'Save' button to save the data. When they clicked the 'Save' button, I would then call a function which quickly wrote the values from the form to the table. (note: I utilized search type forms for users to quickly find data - this would then call a function to write values from the table to the form.) This is ideal since an unbound form is not 'bound' to a table (ie. it has no recordsource). A bound type form would need to maintain a constant type connection to the table and communicate to the table each time a field is updated. An unbound form would only establish a connection to the table when the user clicks a 'save' type button where it would then write 'all' values from the form to the table, hence offering the best performance.

Here's an example of how to create unbound forms: https://www.accessforums.net/code-re...mple-7552.html

[RuralGuy]

Though I have never used ac97, I believe the developers that say it is faster than any version since. It just seems that *all* software upgrades have more "features" and are slower.

[pkstormy]

Though I have never used ac97, I believe the developers that say it is faster than any version since. It just seems that *all* software upgrades have more "features" and are slower.

I think you are correct Allan. With each newer version of MSAccess, it has become more bloated and (probably) a tad slower (but not that slow that I think it is really that noticable unless things such as the 'auto-correct' feature is on). I believe it was with MSAccess 2000 where Microsoft changed the way forms are saved (saving the entire form/vba coding a different way than previous MSAccess versions). Although this caused the mdb file itself to become larger (ie. a 10 meg 97 file became a 20 meg 2000 file), I haven't noticed any dramatic reduction in the speed of the mdb file. This seems more based on the techniques used.

I use SQL Server and compared a 2003 mdb with the same coding/design in a 2000 mdb and didn't really notice any speed difference. My guess though would be that if I used MSAccess tables instead of SQL Server, there would be (or should be) a slight increase in speed with the newer MSAccess version versus the older version. I believe Microsoft has improved the table performance in their newer versions of MSAccess but then slowed down other things with the 'enhancement' type features such as auto-correct.

I can't recall if 97 had the 'auto-correct' which has been a night-mare for some developers and a slow, slow form performance (I've seen a lot of developers recommend turning off the auto-correct feature.) There are probably a few other 'enhancements' which tend to slow things down but this was the major one I've heard throughout the grape-vine that's been a big problem with performance.

[RuralGuy]

That's encouraging. Thanks for sharing. I do know that Help has gotten progressively worse with each version.

[accessi]

Thank you so very much for replying, you have given me some stuff to think about. Right now leaning towards 2010 as per your recommendation and hoping that the kinks will be worked out of 2010 in the future. If not maybe we can get a bulk upgrade license.

In regards to it being slower for you guys.

One of the biggest gripes I have about access 97 is the false CPU hit it has given us. ACCESS 97 will literally be sitting there doing nothing and sometimes your CPU gets hot and the fan gets loud. When you look in task manager it shows that ACCESS is taking up all of your P4 CPU. Granted now a days we are upgrade to dual, quad, and i7. I am hoping thats not the same with 2007 and 2010.