Database Compacting and Archiving - MS Access Backend

Question

Scenario: There is a legacy program (Not sure what language) and I have been asked to "Compact and Archive forms in the database". At the moment when the user opens the application it is taking about 2-5min to load around 27000 Records!!! My theory is that it is loading all the records on start-up but that might not be the only reason. After doing some digging and finding an Access Back end that looks correct, I also found the same access files on 15+ other shares within the company. Now this application was created sometime around 1997 when I'm guessing Access was the norm, but would they really be grabbing data from 15+ Access databases? What seems to be the norm to speed up this program is to archive the older records in another access database (which is why I'm thinking it is loading everything at start-up.

Question: I have a meeting on Monday to discuss the program and was wondering if anyone could suggest some useful questions, theories, solutions, etc. It's not that I can't do this on my own, I just think another perspective couldn't hurt. Also another fun fact is that I may or may not be able to get the source code because it may have been created by a contractor and the code lost long ago.

Side Note: Would it be possible for Access to auto-archive old records? That would mean transferring them to another DB called XXXArch.

Thanks in advance. I will try to answer any questions you have.

EDIT:

Heres an update on the situation.

It looks like its only using one database as the main and one to archive. I still have yet to have my own user account to open the application but when looking into the database there is a user table with the login ID and the same password(PASSWORD) so I tried logging in as one of those users and simply selecting some data not modifying anything. When selecting I was able to get data almost instantly and wasn't seeing any of the slowdown that the other users were getting. I still haven't seen the source code but from what I can tell (taking the exe and putting it in notepad) it looks like it was coded in VBA and probably created using MS Access. Also it seems that the application creates a temp.mdb in a data folder. Currently it has nothing in it. No tables, nothing. I'm assuming/hoping that this is what is slowing the users down and can just be deleted to improve performance. I will post another update once I get the source code and have a better idea of what is slowing it down.

Answer 1

A couple of things to consider:

Access (MDB) databases tend to need regular compact/repair as you noted in the title if they are in frequent use. However I've rarely found that it helps performance more than minimally. If it has been a really long time the file can bloat really big and that might be part of the issue if users are accessing it over a slow network connection.

Someone is going to suggest upgrading to a "Bigger" DB like SQL server, either in your company or in this forum. Don't do that until you have isolated the problem or unless you have a reason other than performance. There is a reasonable chance the problems are caused by poor application design or DB architecture. Throwing a more powerful tool at the problem without changing the approach is unlikely to help.

An Access DB is going to max out on concurrent users long before it maxes out on data. Did a lot of users (30+) just start using the system? That could be part of the problem.

Archiving old records: You are going to have to build something to do this. The good news is that it isn't all that hard.

Accessing 15+ databases: Are you sure the front end GUI isn't written in Access. It is a common architecture with access to have an MDB front end loaded on the end user's machine (copied everywhere) connecting to a central MDB data file out on the network. The best way to tell is to open the databases and see if they contain just tables, or tables + forms/reports.

Answer 2

Seems to me your first order of business should be to resolve this issue:

I may or may not be able to get the source code because it may have been created by a contractor and the code lost long ago.

As it stands now, you're asking us to speculate about the causes of, and remedies for, the slowness ... without any knowledge about what's actually going on.

Answer 3

If you don't have source code, you can't change the back-end database to SQL Server nor anything else.

However, if you actually do have access to the data files and are able to edit them, why not check the indexing? 27K records is trivial for any database, including Access, and slowness in loading the data suggest to me that the tables are simply not indexed properly. If you examine the tables and see no indexes on obvious fields, then try adding them and see if it speeds things up.

If it doesn't, then it means the app is badly designed and since you lack source code, there's not a whole lot you can do about it.

All the above assumes, of course, that the networking environment is appropriate for Access/Jet/ACE. That is, if these database files are being accessed across anything other than a wired LAN, then there's nothing can be done about it (WAN and WiFi are completely out for Jet/ACE).

Last of all, on the subject of archiving, I think that there is no justification for ever archiving data unless you really are running up against hard limits on hardware/software. In this case, you're not even close to that.