Discussion Locked

MS Access - is it time to upgrade?

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I develpoed a database that has 18,000 records with a one to many relationship to a table with about 7,000 records. The data is on a network server running Windows 98. The main form is two tabs and 100 controls, including 15 combo boxes. Problem is that unless you're the first person to log on in the morning it can take up to 6 minues for the main form to load. Most workstations have 64 - 128 MB RAM. The front end is on each computer and the data is on the server.

On my computer with 256 MB RAM, with the data on the same machine as the front end, it takes 2 seconds for the main form to load.

Question: Is this a server/network problem or do I really have to simplify my main form, or do I have to dump Access and use another database?

5 replies

by stillatit

1. More RAM is better (always).
2. One approach I have seen work successfully is to put the tables (only) in a .mdb file on a server, and give each user their own .mdb file (or a compiled version) which LINKS to the data .mdb file using ODBC. This avoids having one station trying to both run the app and service the other users.
3. If you are executing an Access program on another machine, you can test for server slowness versus the importance of more memory fairly easily. Put the current fileon a server. With no other users running, start it from your machine, timing the startup. Disconnect. Now try it from another machine, and time it. If your machine is still significantly faster, memory is probably a big factor. Now move the file to one of the 64K machines. Start it locally and time it, disconnect, start it remotely from your machine and time it. Another test you might run is to have the file on your machine, start the program on another machine and time it, then start the program on a third machine and time it. Now disconnect both and try again, this time with you having the program running on your machine first. The timing difference on the third machine tells you how your machine is affecting the result.

Should you dump Access? Probably not. I have seen many multi-user full-enterprise accounting systems running on Access with no problems.

Should you simplify? Probably, especially if your controls have queries to populate pull-down lists. Consider that when youdo this, Access pulls some (possibly large) number of records across the wire to process your query (very fast locally, very ugly on a wan, moderately ugly on ethernet).

Good luck.

by bob sellman

If the 2 second time on your computer is achieved with the data on your computer (so you aren't running through the network), then your real problem appears to be a slow network. The network may be making numerous routing requests just to process one command that returns a number of records.

As part of testing, I would suggest trying the application on each computer with the data on the computer, versus on the network server. While you may see some time difference (particularly between the64 MB and the 128 MB RAM machines), I think the main difference will be between data on the server versus data on the local machine. That's not to say the data should be on each machine, just that you may need network analysis and support.

If your computer is fairly quick with the data on the server, then you may need changes in the client setup on the other computers. I've seen differences of 4-6 times as long running on a newer workstation that had a different network client setup. (Andit's not easy to figure out how to fix a client problem, since network communication is a complex system.)

I have encountered similar problems that were fixed with improvements in the network backbone.

This discussion is closed to new replies.