How to speed up Access after a BE/FE split? 2

[MrDeveloper]

Hi,
I have recently split out my application into a Front-End MDE app / Back-End MDB database. Forms now distributed to users PC's on the network. Each desktop shortcut now points to local copy of Access, network copy of the security (MDW) file and then local copy of the forms (MDE).

System loads ok but now runs terribly slow (upto 9 minutes to bring back a record in some instances!). The delay in record-accessing prior to splitting used to be a matter of seconds when it was an all-in-one MDB accessed entirely from the server by networked users.

I have trawled the web for hints/tips and have come up with the following 5 improvements (below) and for the benefit of other users experiencing similar problems, I will post to this thread whether any of them helped when I try them.

However, before I go in to try and solve the above problem, does anyone have any further suggestions as to things I could do? I am concerned the following may only make minor improvements to the delays.

Any further help/suggestions would be really appreciated.

Thanks in advance,

List so far:
1. Shorten the back-end database (MDB) file-name to 5 characters.
2. Move it near to the root of the server.
3. Move the back-end MDB to a faster server.
4. Turn 'Track Name AutoCorrect info' off
5. Set subdatasheet name property of tables to [none]

Anything else I could add to this list when I try fixing the system?

 

[BaudKarma]

This autocorrect thing seems be be able to fix up just about any problem short of the common cold. Does anyone know of a way to switch it on/off through VBA? It would be way cool if I could have my project turn it off when my users start to run it, then switch it back on when they exit.

I know the docmd.domenuitem lets you choose Access menu items programatically, but I'm not sure it'll let let you navigate to the options box and turn things on or off

I try not to let my ignorance prevent me from offering a strong opinion.

 

[willir]

LittleSmudgeMy sincere appolgies on the spelling Graham. Another Dahh moment. Still waiting to find out what LittleSmudge means -- you told me last year that you would have to "kill me".

 

[LittleSmudge]

BaudKarma & MrDeveloper
Yes the £$%^&ed AutoCorrect is a real pain and most people experience a teriffic improvement in speed when they first turn it off.
Then they get into the habit of turning it off
and hence - I'm afraid - forget to mention it to other people who might not yet know about it !

I feel that there must be a way of turning the thing off in code.
Probobly using the DAO SetProperties which I already use for enabling/disabling ShiftKey Entry, etc.

I don't have access to a Db that uses that code today, but I'll have a look over the weekend.

 

[willir]

MrDeveloper

4. Turn 'Track Name AutoCorrect info' off

When I read your original post, I had assumed Autocorrect had been turned off.

Impressive lesson n'est pas?

Richard

 

[BNPMike]

Wicked.

I never knew this existed. Just tested it and it works. This may well change my life.

Having said that I find it odd why it would make much difference to performance. Anybody got any theories?

 

[MrDeveloper]

Hi,
Turning Autocorrect off was 4th the list of things I was going to try, with the original posting being a confirmation of whether anything else I should try just in case before I tackle the issue.
Made an impressive difference, yes!

BNPMike:
I think the reason why performance is impacted is extra checks are being done over the network to verify names. Not so noticable on a stand-alone PC. I imagine the traffic is as follows:

1. Load form A
2. Form A checks names of objects. (should stop here, but autocorrect goes on with
3. "Is the name of this object correct?"
4. Send message back to Table A to see if object name matches ok.
5. Table A checks that the object refers to a column in the table and ensures both Form and Table names match.
6. Table A sends back confirmation that the names match (or if they dont, a look-up/reference confirms how they should if the table has been modified but the form hasnt).
7. Form A confirms the receipt and only then continues to load the form.

Do all of this over a WAN and you have a headache.

This is all speculative by the way - not an expert in Access but I am sure others on here could correct any misconceptions. Sounds like this sort of thing might be happening though.

MrD

 

[PHV]

Richard, Impressive lesson n'est pas? should be read:
Impressive lesson n'est-ce pas?