Persistent Connection to Speed Performance

[Katherine R]

My forms are taking forever to open in my database. It is taking 30 – 40
seconds to open a form from the database switchboard. I found a previous
posting suggesting establishing a persistent connection to the back-end from
each of the front-end workstations to speed performance. After following the
suggestion, it is still taking 30 – 40 seconds to open a form on the first
attempt. However, if I close the form and then reopen it, it is taking only
a second or two the second, third, fourth, and etc., attempt. This is a huge
improvement, but I expected the form to open more quickly on the first
attempt. Could I have possibly left a step out? This is what I did:

1) I created a dummy table in the back-end file and put one record in it; 2)
I created a form (frmKeepOpen) based on the dummy table in the front-end; 3)
I added the following line of code the switchboards OnOpen event -
DoCmd.OpenForm "frmKeepOpen", acNormal, , , , acHidden

What I would like is for the forms to open just as quickly on the first
attempt immediately after opening the db instead of on just the second and
subsequent attempts. (Thirty seconds seems like an awful long time.)

By the way, I am working in Access 2003.

 

[Albert D.Kallal]

The persistent connection trick will fix performance problems due to a split
database.

However, the persist connection trick will NOT fix a poor design.

However, before we try to blame the design, you might want to check out the
following list of things:

(track auto name correct is a big one, and so is the persistent connection).


Check the following list...go through each one:

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

I added the following line of code the switchboards OnOpen event -
DoCmd.OpenForm "frmKeepOpen", acNormal, , , , acHidden

That looks good. However, if you remove this code, and open one form...and
close it...I bet your application sill opens faster
(in other words, I not sure the persist trick is helping you!!).

Anther good suggestion would be to try a mde for each front end you put on
each pc...(that tend to eliminate the track auto name correct issue).

 

[Katherine R]

First of all Albert, thank you for your reply. I really don't have the
background for building databases and would never have been able to do what
(little) I have without this Discussion Group. (I've searched and read
postings until my eyes hurt. Although most of what I read is beyond my
comprehension, sometimes I get lucky.) So, it is quite possible that my
design is lacking. I think I have my tables set up and related properly,
but...

I have a copy of the FE from before I appied the "persistent connection" and
the forms open slowly each time. After applying the persistent connection
fix the forms open slowly the first time, but instantly each time after. I
can close the application and reopen and it is the same thing - forms open
slow the first time and quickly each time after. This is an improvement.
The application itself opens quickly either way, it is just the forms I am
having problems with. I am wondering if this is the way the persistent
connection fix is supposed to work - slow on opening the first form and
quickly thereafter?

I am familar with the link you provided and have applied a number of the
tips to my database some months ago. I believe the application itself was
opening slowly before applying these tips. (I set the sub datasheet Name
property on each table in the back-end database to [NONE], I set Track name
AutoCorrect info to off, I shortened the name of the backend database, I
deleted and recreated table links, and I moved the backend as close to the
network share as our IT people would allow - The path is something like
this: \\Rev001-cc\shared\ATD\BE Data
I think I even did a decompile - maybe I should try this again?)

I don't quite understand the mde thing but I'll search the group for more on
this.

 

[Albert D.Kallal]

I am wondering if this is the way the persistent

connection fix is supposed to work - slow on opening the first form and
quickly thereafter?

No..only that first form that is attached to the back end should experience
the nasty delay.

After the above form has been opened (and remains open), then each
additional form should open quite quickly, with little delay, and certainly
very close to a un-split database (in other words...you sill might see a bit
of delay..but it is usually so small).

I am not sure why you have a "slow first" time of a form load..land then
each additional time is fast, but this sounds very much like the forms code
libraries are being loaded..and thus I would look at the track-auto name
correct. (or, at least test a mde).