Networks·41,411 discussions

MS Access way too slow on server.

Locked

We have 10 users sharing Access 2000 database. Front end is on each workstation, all with Win98. Tables on the server – main table has 6,000 records linked to a table with 17,000. The main form has 2 tabs, 80 controls. The main form can take 5 minutes to open, except for the first person to long on. Most PC have 64 – 96 MB RAM.
Question: Could this be a server problem, do I need to convert to SQL, or some other option?

Topic

MS Access way too slow on server.

In reply to MS Access way too slow on server.

With 10 users on an Access database, you’re around the limit of concurrent connections. If the speed at which the app loads gets worse with each new logon, then it’s definatly a connection problem.

How powerful is your server? How congested is your network traffic? Both of these play a part, because Access isn’t an intelligent database server, it’s essentially just a file server.

Another huge factor is the design of the application. If your main form accesses a lot of table during theload operation, then the users are going to be fighting with each other for table access, slowing down everyone.

Before going to SQL Server, consider MSDE, it’s essentially SQL Server Desktop edition, with a lot more intelligence than Access. Itcould really speed up your application, but you may need to redesign parts of it to take advantage of it’s capabilities, i.e. stored procedures, clustered indexes, etc. The up side to trying MSDE is that if/when you have to go to SQL server, you don’t have to change anything, they’re 100% compatible.

Glen

MS Access way too slow on server.

In reply to MS Access way too slow on server.

The question was auto-closed by TechRepublic

MS Access way too slow on server.

In reply to MS Access way too slow on server.

To add to the above answer:
You might want to run a quick test by setting up the front and back end databases on one of the workstations, so it will run locally for testing. Then see how quickly the form loads. While it could take 2 or so times as long as running over the network, if it is a lot longer, then there are problems with your network backbone and/or server.

Try to cut down the size of your main form and the number of controls that require looking up data/description from another table.

If your main form opens with all records available, opening it with only one record selected should also help to speed it up. For example, if there is a record number that your users would normally know you might ask them to select the record number before opening the form and then open the form for only that record number.

SQL server is a good recommendation; however, it’s difficult to get much information about it. Be sure you check Microsoft’s web site and look up MSDE. There is at least one decent article that explains part of using it. I haven’t yet seen a good written explanation about setting it up on a server.

MS Access way too slow on server.

In reply to MS Access way too slow on server.

To correct my wording: Loading the form on the network could take 2 or so times as long as running it on the local system.

Obviously it will take longer over the network, not less time. Sorry about the reversal of the wording!

MS Access way too slow on server.

In reply to MS Access way too slow on server.

The question was auto-closed by TechRepublic

MS Access way too slow on server.

In reply to MS Access way too slow on server.

This question was auto closed due to inactivity

[Author]

Replies