Incredibly slow Design view in Access 2007

[Fazered]

Right, i guess before i go on to mention exactly what the issue is, i'd best just explain that i have spend days trying all kinds of different possible solutions to sort this issue and have researched this no end, but not yet found a proper solution.

Ok, so here is the situtation i am in.
I have 2 similair access 2007 databases (1 much bigger than the other), both are split into front and back ends. Both backends are stored on a central server (not SQL yet), then each user on the network has their own copy of each front end (so a local copy of the front end on each users machine). Then there is a development version for each front end, a master copy if you like, that is used to make fixes, develop new features, etc. Then whenever the development version if ready to be released, a few settings are made and copies placed on each users computer.

Everything is fine when the users use the front ends as normal (so form view, etc). However, whenever I do anything in design view the database slows down considerably, And by considerably i mean that i've had to wait up to 15 minutes to click from one object to another.

I have tried everything i could find on the net to fix this problem, but i have had no success. Occassionally, it would be incredibly quick for a few hours and then would just grind to a halt.
Here are the things i've tried:

• Subdatasheet properties for all of the tables.
• Keeping a connection open to the back end database.
• Compiling and 'Option Expliciting' all VBA
• Reducing the number of different databases the front end has to link too.
• I've also looked through just about every form and database property and turned off anything that i dont need.

Because the slowness only seems to happen when switching between objects that are part of a bound form, part of a subform or anything that seems to be related to the data in the backend. Which makes me beleive its timing out or verifying recordsets or something every time i change object.

Any ideas, anything at all?

 

[DCrake]

Simple Software Solutions

Not got round to using 2007 yet but I did come across this problem with 2003. I deduced it was happening when modifying bound forms. What Access attempts to do is to track errors constanlty so lets say you change a field on a bound form access tries to validate the whole form AND the underlying table or query.

I'll bet if you removed the recordsource form the form then saved it and went backl into the design the changes would be almost instant. Give it a try, you can always reattach the recordsource after making amendments.

CodeMaster:

 

[Fazered]

Thank you for the very prompt reply. I have just tried your suggestion and surely enough, it worked, everything sped up and worked as it should do.

So the issue now is whats the best was to get around this issue, as its a little inconvenient to constantly change the recordsource of all of the forms, subforms, etc. Im guessing that there isn't really a way to sort the issue?

 

[DCrake]

Simple Software Solutions

As the developer you can dictate where the linked tables are. I suggest you create a clone of your back end with little or no data in it. Whilst in development mode link your front end to the empty back end. Then prior to deployment relink back to the live data and deploy the new front end. You could actually automate this by checking which pc is opening the mdb. If it is the development pc then relink tables to empty backend otheriwse makje sure the linked tables are pointing to the live data.