Slow response after Splitting a dbs 1

[illini]

This is the first time I've attempted to split a database. Everything went fairly well. The backend was placed on a network server.

Afterwards, though, I've noticed that loading forms and accessing data is extremely slow. I expected some lag, but it can take up to 20 seconds for a form to load (versus 3-5 sec before splitting the dbs).

Is this typical? Are there ways to speed things up? -illini

 

[ssecca]

Where does one begin

Questions
What was included as the backend versus front end?
How many users are accessing the information?
How much traffic is on the network?
How much RAM is available on the server?
What kind of server are you using?
What is your network speed?
Is the server located locally or is it on the other end of a T1 or such?

All of these and many more could be effecting your performance. Start at the top and address one potential problem at a time and eventually you will find the bottle neck. That will only leave you to find the next bottle neck and the next and the next.

Having horrified you I still beleive splitting the database is a good first step towards a well designed and built application.

Oh yes, are you using bound forms?
Are you using linked tables instead of an ADO connection?
And the list goes on...

Good Luck
ssecca

 

[vbajock]

You might have a bloated mdb somewhere.
Did you put the front end on the user hard drive?
I have noticed that the backend (and the frontend if you have left any tables in it all) will bloat out really big and then cause the problem you described if you don't have some method provided to compact the databases. On the frontend (if it is on the users hard drive) , you can turn on compact-on close from the start-up options. On the backend or if the frontend is on a server, which I hardly ever do anymore, I turned on compact-on close, then I put a macro in that just calls the Quit command. I then add the backend with the macro call on the command line, to my job scheduler on the server. At the appointed time late at night when the users are dreaming of their bonuses, the scheduler opens the MDB, calls mcrQuit , forcing an immediate close, which then forces the compaction. If that doesn't work, get started on ssecca's list!

 

[illini]

Thanks for your responses.

First, I tried compacting the database (front & back ends). This reduced the size of the file dramatically, but didn't improve performance.

As for ssecca's list of questions:
1. The backend includes several tables. The front end includes one table, and several forms.
2. For the moment, the dbs is only being accessed by myself. Eventually, it'll be accessed by 4-5 folks.
3. The network likely has quite a bit of traffic. The office uses a LAN; however, my division is a building over. We tap into the network via a T1 which also carries our phone traffic.
4. Unfortunately, I don't know much about the server(RAM available, type, or network speed).
5. My forms are bound for the most part. There are a couple of fields which are populated using vba.

-illini

 

[nerdcore]

YES I HAVE THE ANSWER!!!

I just split a database last week and this drove me CRAZY. So you will be as happy as I was when I figured it out. The reason this happens is that every time you open a form/report/query, access will open a connection to the backend database. Then when you close that form/report/query access will close the connection. So EVERY time you do something involving the tables it will open and close the connection and this is a HUGE time waster. The solution is to open a connection to the backend database when you first open the front end and keep that connection open until you close the front end. Example: Create a dummy table in the backend with one field and no data. On startup of the front end open a recordset connection to this table in VB, or do it through a hidden form, and keep it open until you close the database. Below is a link to the actual code to do this and a better explanation than I can give. Hope this solves your problem! There are some general Tips at the top of this page about speeding up the split databases but scoll down a bit and you will find the code.

http://www.granite.ab.ca/access/a2kslow.htm

Good Luck!

 

[RobPotts]

Cheers Nerdcore,

Just what I was looking for, seems to be working better at the minute, I'll keep my fingers crossed.

Thanks again Rob! [Bigcheeks]
Process and Procedure are the last hiding place of people without the wit
and wisdom to do their job properly.

 

[illini]

I understand the principle, but can't quite get it to work. As instructed, I created a dummy table one the back end. Then I created an empty form on the front end. Then I copied the code (from the above link) into the form's vba.

When I try launching the form, I get a Runtime Error '13' (Type Mismatch). -illini

 

[RobPotts]

Hi,

I'm not a VBA Expert, but I had runtime error 13 and this was because I was using DAO in access 2K. If your using Access 2k you'll need to make sure that your references, in tools references have DAO library selected. Then I think you'll need to have

Public rsAlwaysOpen As DAO.Recordset

as the first line.

Hope this helps, let me know Rob! [Bigcheeks]
Process and Procedure are the last hiding place of people without the wit
and wisdom to do their job properly.