Slow forms...

[dsmaj]

One of my forms is so painfully slow to load/save/do absolutely anything with, that I can literally go get a coffee, and come back to my desk and it's still working. The database is currently being stored and access by me (for developement purposes only) over a 100mbit network, however I'm positive this is not a network problem. The table that this form is dealing with doesn't even have 10 records in it yet, and while the form _is_ fairly control heavy, it's hardly worthy of the 1 minute load times I've been experiencing. I have other forms in my project that are equally control heavy, operating on similar tables, and they run quite fine, so I'm sure this is a problem just with this form. I know that the issue of slow databases has been dealt with at length on these forums, and consequently I have done several searches, and tried some of the fixes that have been suggested in the past, but to no avail. Does anyone have any new insight into this seemingly common problem? This is driving me absolutely insane

Thanks,
sam

 

[adam_fleck]

I've had this problem too

Access can be weird. I've been developing a database that runs on a Windows 2000 network. It's split into a front end and a back end. The back end is on the server, while for most users, the front end .mde file is local.

Over several weeks, the main form on most front ends gets slower and slower to load. When I'm designing - if I don't do this through the Citrix server - loading the main form can take ages. Saving takes ages too.

A solution that works, at least on the network I'm designing on, is to rebuild the front end database every two weeks or so.

I create a new .mdb file, import all the objects from the old one (remembering to click the Options>> button and tick Menus and Toolbars). Then I reset the references in the code, and create a new .mde file which then gets distributed. The new file is much faster.

It's a pest but, it works, and since it requires on-going maintenance, and I'm a contractor, it doesn't drive me to drink, it just pays for it.

A trick you could try is to keep a slim version of the the back end on your local machine, and design the front end while linked to that. Before you make the real front end, you re-link to the server and then make the mde file.

I designed my own utility for linking to the server or to local c as I found the built-in Access tool for this pretty much unusable. I can give you a copy of this if you think you could use it.

You could also try taking all reports out of your main front end, and set up a separate front end specially for reporting. Reports somehow seem to make an over-the-network database very slow indeed and cause front end hugeness. An .mde with about thirty forms and twenty reports went down from 24mb to 3mb when I took out the reports.

 

[vknierim]

I had the same problem with one of my forms but this form was based on a table with more than a thousand records.... In this case it helped to implement a list with all the records of the table in it. The user selects one record by clicking it in the list. The RecordSource of the form is then modified to only display the selected record (like "SELECT * FROM tblCustomers WHERE CustID = X;")

Another approach that might help is to modify the way the code gets the data from the database. I found that DLookups are quite slow, especially when they are inside a loop and called often. It is much more effective to implement a ADO Recordset in this case and loop through the Recordset to find the required data. The execution time of the code was reduced from about 2s to 0.2s!

Hope this one helped you

Volker

 

[dsmaj]

Thanks for the replies, I'll try some of those things...however for the most part I think my code is optimized as much as possible--I'm not using any D* functions or anything.

Adam--That'd be great if I could get a copy of that linking utility you have...yeah, access's built in utility sucks!

Thanks,
Sam

 

[adam_fleck]

Link Form

Here's a zip with the link form stuff. It should only take a couple of minutes to import the objects into the front end you're designing.

I came across another tip which you might want to try. From the database container, select the menu options - Tools, Options... and, when the Options popup appears, click on the General tab and look for the "Name AutoCorrect" option group. There are three options in this group. Remove all ticks so that none of the options is on.

I found this tip at http://www.tek-tips.com/gviewthread.cfm/pid/702/qid/206410. The folk there think it rocks.

 

[mstorer]

dsmaj, from your post, I couldn't tell if your database was split into FE/BE. If so, and you're using Access tables, you may also try the following:
1. Open your source tables in design view
2. Open the properties of the table
3. Change the "Subdatasheet Name" to read [None]

I read this somewhere on the Microsoft website when I was having the same problem. Although the forms are still not as fast as normal, there was a noticeable increase in speed. I hope this helps.