Forms slow to open, Front End, Back End question

[NeilT123]

My Access 2007 database is split Front End & Back End. The back end only contains tables and each user has their own version of the front end.

The back end is based on my computer and when I am using the database all the forms open quickly however the network users are complaining that some of the forms are slow to open.

The record sources for some of the slow opening forms are the linked (back end) tables and a recent google suggested that I should not do this and that the forms should be based on a query as that limits the amount of network traffic.

Do users here agree that this is correct? If so should the queries be based in the Back End? And if so what happens if two users are using the same form at the same time?

Thank you in advance for any replies.

Neil

 

[BobStubbs]

Can you place the database back end on a (hopefully fast!) network server? It seems to me that in your current setup, requests from other users' PCs are restricted by the speed of your PC's network connection - if this is busy with other tasks then they will see problems.

I would think that in any front-end / back-end split database, the back end should sit on a server, for fast access from all PCs.

Bob Stubbs (London, UK)

 

[MajP]

I interpreted you thread a little different. I read it to say, you do have the back-end on the network, but for test purposes you have it local. Once placed on the network it runs slow.
If that is the case where the each user has a copy containing forms, code, reports, macros and a single backend on the network containing tables you have done everything correctly. However, there are a lot of tweaks to get good performance. If the network is a dog then your performance will naturally suffer. If the network is solid then start tweaking. But you can only improve so much if the network is poor. Read the portion on multi user environment.

http://www.fmsinc.com/tpapers/faster/

http://www.granite.ab.ca/access/performancefaq.htm

Now you should always build your forms off of queries where you return the minimum amount of fields needed, filtered to return the minimum records needed, and properly sorted. This almost always increases performance in a split or local db. However, the following is not really true with Access

I should not do this and that the forms should be based on a query as that limits the amount of network traffic

Since Access is a file sharing database the processing still takes place on the client side requiring the all data to pull across the network. If this was SQL server or another true client server database it would be true because the processing takes place on the server side.

If you read the tips you will find one of the biggest improvements come from moving the backend higher up in the directory structure and even shortening the name of the db.

 

[NeilT123]

Hi, thanks for the responses so far and sorry for not being clear in the 1st post, there was a lot going on here this morning causing distraction.

We have a small office with 3 users of Access. My computer has a partitioned hard drive and the back end resides at v:\NP_25 so I have tried to cut down file names.

The other computers have shared network drives to v:\ and have their own front ends on their c drives. My front end is on my v drive.

I suspect there is a degree of network sluggishness but I am surprised at how slowly some of the forms open and hence my question about queries being based in the Back End but it seems that is not an issue. I will read through the links provided but I will gladly investigate any other suggestions.

Thanks

Neil