trying to speed up access database

[Jon123]

I have a database that is split into a FE / BE with the BE running on a server and users are accessing thru a dial up connection. This is working very slow do to the fact that I have combo boxes that users select data from that are based on different tables and every time you click on a combo box it takes several minutes to open deponding on the number of records. At 1st I thought that maybe converting to SQL would help solve this but the more I read the less I think that will do the trick. Could someone please advise me on the best solution here.

Thank you

Jim

 

[FoFa]

Your problem is the amount of network traffic you incurr.
Everytime it needs to go get some data, Network traffic.
So the question really is, how can I change my application so it does not have to go get data every mouse click. Best way to limit network traffic?
It is a sort of trade off between application ease of use (in some cases) vs speed and network traffic. You know better where you are hitting the BE to many times. And the more data it needs, the longer it takes n a slow line. The other problem is all the work is done in the FE in Access. if you changed it to use SQL, you could have the BE do some of the work and cut down on network traffic with proper design.

 

[Sean O'Halloran]

I have combo boxes that users select data from that are based on different tables/QUOTE]

Are the comboboxes pulling data from tables of records entered by your users? Or are they pulling a standard selection of lookup values from a code table?

Sean

 

[Jon123]

They are pulling lookup values. But they can get updated if configuratins change.

Jim

 

[Sean O'Halloran]

If you haven't already tried this, it may help.

In my databases I have code tables with lookup values like:Male, Female, Married, Single, etc. that users need to add to client records through comboboxes. I keep those code tables in the FE for fast response with comboboxes.

Hope that helps - Sean

 

[rhett7660]

In my databases I have code tables with lookup values like:Male, Female, Married, Single, etc. that users need to add to client records through comboboxes. I keep those code tables in the FE for fast response with comboboxes.

Could you elaborate on this...... Code tables??

Thanks
R~

 

[KeithG]

Basically he is talking about constant data. Data that is never going to change. If you store this info in the FE Access does not cause any network traffic because it is a local table.

 

[workmad3]

pretty much what I thought in response to that Change the lookup tables from being linked tables to actually being stored in the FE. It may mean that updating them (if they ever need updating) will take a bit longer as the FE with the new data will need to be propagted to all your clients, but the local data will improve speeds.

If you think the data is likely to change (but not often enough to class as constant) you could always have an automatic update for it in the FE Basically what you would do is have the 'master' tables in the BE, and when a change is made to them, the BE informs the FE on logging in and the FE updates its local copies with the new values from the BE db.

 

[rhett7660]

Basically he is talking about constant data. Data that is never going to change. If you store this info in the FE Access does not cause any network traffic because it is a local table.

Ok gotcha...one more question...when you split the database...access automatically puts the tables in the back end..how do I tell it to keep table x in the front end?? Or am I missing something?? (i have a feeling I am missing something lol)

Thanks
R~

 

[KeithG]

I am not sure, I have never used the database splitter before. You will probably have to add the table to the FE after you split it.

 

[Sean O'Halloran]

After you split the databases create the code tables in the FE.

Here's another idea that may help ( it is not original with me; I got it from another helpful person here in the Forum ): When I first split my database FE / BE I spent a lot of time copying FE shells down to each user's computer when I made changes / upgrades.

Then I wrote a little .bat file that would automatically copy the FE from the server into a folder on every user's computer every time the worker logged on in the morning (the bat file goes in the Windows Startup folder so it fires when the user logs on. This may be an issue if they are not logged onto the server by the time this action fires). That way they would always get the latest and greatest version of the FE every morning. Sure, most days they got the exact same version they had the day before, but that doesn't matter: It saves me hours of time, makes incremental changes and fixes painless, and keeps everyone's FE shell compact and clean. If a glitch crops up you just fix the copy on the server ( BACKUP ALERT! ) and then tell all the users to log off and log back on. This will download the corrected FE to everyone.

Here's my entire bat file: Copy G:\GShells\Gin2K.mde d:\Personal\Gin2K.mde

"G" is the designated server drive, "d" of course is the users' PC drive. Create a desktop shortcut to the FE shell and you're in business.

I hope that helps, and that I haven't insulted your intelligence - I got all my Access learnin' from books and this Forum...

Good luck, and let me know if there is anything else I might help with.

Smilin' Sean