Slow Over Network

[dnelson33]

I have a database where the back end is located in a network folder and each user has a copy of the front end. This includes the main table (which is very small - less than 200 entries), a ZIP code latitude and longitude table (obviously very large), and a couple of lists that I use for combo boxes. Other than the main table, the other tables will most likely never be updated.



According to the users (which are spread out around the country), it is running very slowly on both open and ZIP code distance searches. They say it takes around a minute just to open the file whereas mine opens in less than 10 seconds.

My question is this - Would it be appropriate, since the large ZIP code table and the list tables never need to be updated or changed, to include these tables in the front end to speed things up? If not, what are some other ways that I can optimize this? I have never posted a database to a network folder before, and since I am having no problems with the version that is on my computer (even though it is still pulling the linked tables from the same network location), I am having a hard time understanding what to do next. I have done quite a lot of research and tried several suggestions (compacting and repairing both FE and BE, using network address instead of drive address, optimizing code, etc.) Thanks for any help you can give.

[RuralGuy]

Your local LAN runs at probably 100 times the speed of the Internet. If those tables are truly static then I see nothing wrong with moving them to the FE.

[dnelson33]

Would I see significant speed improvements by doing this alone or are there other measures I can take to ensure maximum optimization? I've already gone through a couple versions, and I would rather not release a new one until I know it is going to work.

I only have a few things going on in the database. On open it deletes and appends the information from the BE main table to a temp table in the FE, so after adding the other tables to the FE, all of the data would be on the users computer after loading. There is only one form with a search function that creates a string based off of user input (ZIP Code and radius among others) and inputs it as the form filter. The search button checks to see if there is a ZIP code entered and changes the RecordSource to a query that calculates the distance from the input ZIP code and adds the string to the form filter. Pretty simple stuff, but it is taking forever for the users to do anything over the network.

[RuralGuy]

Here is a link I usually suggest for performance issues: http://www.granite.ab.ca/access/performancefaq.htm
You would be surprised how much data is transfered to the FrontEnd for what you are doing. If you had SQL Server as your BackEnd then you could get the BE to do a lot of the work for you and save all of that network traffic.

[dnelson33]

Thank you! I've actually gone through a similar list and taken care of most of these performance issues. I'll address the ones that I haven't, and hopefully, transferring these tables to the FE will drastically decrease load and search times.