Access 2007 Slow on Network and Locally (1 Viewer)

[friedboudinball]

I have an access database that works like a charm as a single database on a local machine.

However, when i moved to a Server it was excruciatingly slow, and almost unusable. So, i split the database, thinking that would help, only to have it operate very slow there (as a local copy).

Any ideas on what I can do? Obviously, i do not have an impact to any server issues since they are ownded by IT department.

 

[shadow9449]

Splitting a database does somewhat reduce network traffic but on its own will not speed up the database. The goal of splitting is to address stability and corruption issues that are common when more than one user is sharing an access application.

The first thing you want to do to help speed is to create a persistent connection to the back end. There is a number of ways to do this but the way I do it is to create a table in the back end database with even just one record, create a form in the front end that is bound to that table with a field on that form from the table (perhaps the only field) and have that form open as hidden when the application is launched. This will noticably improve speed.

Another important point is that having Name Autocorrect turned on and tables with the Subdatasheet Name set to [Auto] will really slow things down. Have you looked at these or do you need a hand with them?

Other than that, there is a number of things to look at on the server that can cause the problem but first we would need to know which OS the server is using and the network speed.

SHADOW

 

[friedboudinball]

The first thing you want to do to help speed is to create a persistent connection to the back end. There is a number of ways to do this but the way I do it is to create a table in the back end database with even just one record, create a form in the front end that is bound to that table with a field on that form from the table (perhaps the only field) and have that form open as hidden when the application is launched. This will noticably improve speed.

Basically you immediately establish the connection and maintain that connection, so as to prevent the Front End from continually re-calling the database?

Another important point is that having Name Autocorrect turned on and tables with the Subdatasheet Name set to [Auto] will really slow things down. Have you looked at these or do you need a hand with them?

I do not know what this means. Please help! Thanks, in advance.

 

[shadow9449]

Basically you immediately establish the connection and maintain that connection, so as to prevent the Front End from continually re-calling the database?

Correct

I do not know what this means. Please help! Thanks, in advance.

See here:

http://office.microsoft.com/en-us/access-help/set-name-autocorrect-options-HA010256413.aspx

The last section explains how to disable it.

http://www.fmsinc.com/free/NewTips/Access/SubdatasheetName.asp

Here's some information about subdatasheets. Microsoft has some code that will set all subdatasheets in the database to none to save you some time so maybe look around and see if you can find it.

SHADOW

 

[thechazm]

Here is also a good reference and has helped me countless times.
http://www.fmsinc.com/tpapers/faster/#Tables%20Performance%20Tips

 

[Lightwave]

As a starting point I would make sure that the network isn't strangling your connection.

It really shouldn't make that much of a difference operating an access database over a LAN.

Is it a LAN your server is on or WAN

Does it take a lot of time to transfer other types of files

 

[friedboudinball]

so i have done all the things recommended and the database is still painfully slow...particularly when opening reports. A report takes about 60 seconds to open and it is only running one query on one table.

I do not think the network itself is stellar, but i would not think it would take that long to open.


any other ideas?

 

[DevastatioN]

Just to make sure, is your front end located directly on the local machine? Frontend should be on the local machine (not a shortcut, an actual copy), and the backend on the server.

If you're accessing the frontend on the server, with the backend also on the server, this will slow the database down quite a bit.

Can you ping the server to see if it's just the connection to the server being extremely slow? It could be the connection and not the access DB at all. Generally the persistent connection method makes a noticeable improvement.

 

[friedboudinball]

thanks for your help.

both are on the server ... this is due to users at multiple locations (across a network) needing access to the same data.

 

[DevastatioN]

This is where the problem comes in. Your backend is where all the data is stored in the tables, so there should be only one copy of the backend, on the server.

Each person who needs to access the data, should have a front end. That frontend needs to be a copy on each person's machine. The data will get written to the backend correctly.

Currently you have multiple people accessing the same frontend file, on the server. This will slow down the database massively, especially when more than one user is using it.

Keep a copy of the front end on the server, but make sure each user COPIES it to their local machine to run it. Nobody should use the frontend on the server. Try this out, and it should improve the system.

You should get into version control and all that stuff as well, each update to the frontend that is important, your users should be forced to upgrade their version on the local machine.

Let me know if this helps,

 

[friedboudinball]

even with the front end on my desktop, it still pulls up fairly simple reports in over 30 seconds. This seems to take too long for a simple query with a few hundred records.

how can i test the server speed? any other ideas?

 

[DevastatioN]

Just one additional note, even though you had the front end on your desktop, were all your other users off of the system? (Even if you have it on your desktop, if SOMEONE has the frontend open on the network drive, it'll slow everyone down) Just checking incase you tried to test it only yourself without disturbing your users.

If you're using it on the local machine, the backend on the server, are the only person in the database. Then it's definitely the connection to the server. Unfortunately I'm not sure what else you can do to test this, maybe someone else has an idea how to fix slow connections.

 

[friedboudinball]

Yes all users were off system.

Interestingly enough, when it is really slow, it seems to be slow in the report formatting, etc. Nothing complicated about the report, though.

 

[DevastatioN]

What is the size of the backend? What is the size of the frontend? How many records are in your biggest table?

If you create a very basic report, just generic one field to list something, is it still slow to load? (Try the report on frontend, AND try on the backend too, see if both are slow to load)

I think we're grasping at straws here, it seems like your system is developed properly, it runs great on local machine, and it's terrible on the server.

I would confirm with IT where that server is. Is it in the same building? Or is this a remote offsite server?

If the server is in the same building, try from a different location as it could be a problem with the network jack/switch you're plugged into. It may just be the way the server is setup though.

I have this same problem at work myself, and it's an issue with the location of the server and how it's setup, splitting on the network has minimized the loading for multiple users to something like only 5-10 seconds per advanced reports.

I hope something we've said can help you troubleshoot where the issue is, but it's looking like it's the server, and nothing you can change within Access.