How to improve performance of an application written in Access 97

[PSUAlumni87]

I am working with a new department in our company that has an existing application written in Access 97. It is very large and has many many functions and subroutines written in VBA. The system has over 40,000 records in it and it is becoming increasing slower and slower. It is a great system but needs re-written to speed it up. Can anyone tell me what languages we should be looking at to convert it to? VB/SQL? C+? Would upgrading to Access 2000 help?

Any thoughts would be appreciated because I have no idea how to improve the performance of this application.

Thanks

 

[JoeMiller]

40,000 records is not a lot of data for "Ms. Access" (to quote MichaelRed). I would venture to guess that the system code was written poorly and with optimization you would see a large increase in speed. I have a system that I maintain with several tables in the tens of thousands of records and don't have any evidence of system slowdown.

If it must be rewritten from the ground up, I would suggest moving it to SQL server and writing the front end in VB6. Much of the code could then be easily adapted. Were you to switch to a new language (C++ etc...) much of the code would need to be reverse engineered.

HTH Joe Miller
joe.miller@flotech.net

 

[braindead2]

Just my 2 cents.
40,000 records should not be slowing down a well written/designed Access database. The things I'd look at are
do the fields using lookup (combo's, Listboxes) refer to a lookup table or just to themselves. My first database I had a combobox's source as "select distinct Area from Table" Table being the same table I was adding records to. As each record got added to the table it slowed to a snails pace. I fixed it by creating an area table and changed combo's source to "Select area from AreaTBL. Instead of looking thru thousands of records it now had to look thru a few hundred. Any time you can limit the records returned it will speed things up. Instead of "Select * from table" use "Select fields from table where criteria" the prior is easier to type but the later pays off in speed savings. Also if it is on a network splitting the database can also show speed increases. Also have you compacted the database lately. And lastly sometimes archiving old data is an option.
I don't see Access 2000 as faster but find it slower in most cases.
Good luck

 

[MichaelRed]

Hmmmmmmmmmmmm,

Having already been quoted, I do not know that I have much (more) to offer. I would definitly agree that 40K records is a "small" data base and should not be a consideration re speed of the db. In fact, for a db with this few records, I am forced to wonder WHY it would have any real degree of complexity (" ... very large and has many many functions and subroutines written in VBA ... &quot.

It is also quite interesting that an "old" system would only have 40K records. For a general business app, that is probably only a few months of data (or less). Even for a relatively 'slow' business process, one would 'expect' that many records in a year.

So, my knee jerk reaction is that no one knows to "compact" the db on a regular basis - and that it probably hasn't been done in a LONG time. My Other knee jerk (the one which caused me to fall down) is that you are adding users to the system, but then with only 40K records, how many users can there (practially) be? Do the compact thing. If that doesn't DRAMATICALLY improve performance, re-post with some general discussion about the app:

How many
[tab]Tables
[tab]Forms
[tab]Queries
[tab]Reports
[tab]USERS (Overall)
[tab]Users (Max simultaneous)
[tab]does the app use MACROS (extensively)?
[tab]How many Modules?
[tab]How many Procedures?

[tab]What is the general intent/use of the app?
[tab]Is it "Split" (e.g. Seperate .MDB file for the data(tables) and the 'remainder'?

[tab]Is it a 'Secure' db (do you have to "Log On" to use it)?

[tab]does it appear to be slow only in certain situations, such as particular time(s) of the day, when certain Users are 'on', for sppecific operations (Daily report generation)? ...

[tab]Is there a regular routine to compact the db?
[tab]Is the db backed up on a regular basis? By a Network process? By the db ADMIN? What was the most recent verification that a BACKUP copy of the db was SHOWN to be 'operable'?

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Guest_imported]

Is it possible the limitations seen are due to the limitations of the local computer not Access?

Here we have lots of old, slow computers and you really notice a performance hit due to the smaller amount of RAM on some machines.

Just a speculation,
Miranda