Access 97 Linked Table Preformance

[Mike]

I split of tables from my access db to a backend db on our server
(which is fairly robust). It immediatly had a dramatic preformance loss
(difference between an immediate return of data as opposed to 2 or 3
seconds). I have been digging through the groups trying to glean some
info. Here is what I have tried:
-Short file names
-Placed backend as close to root dir as possible
-Placed both front and back in various locations.
-Setup persistant recordset connection to backend.
-Checked and rechecked proper indexing.

A combination of all best practicices yielded some improvemant but
still nothing compared to the two db's being combined.

The best configuration was having both front and back on my local
pc...this resulted in minimal preformance loss, but the db will have to
reside on the server.

Am I missing something else? This is not a large db by any stretch,
back-end is right at 1mb. Is there any apps out there to help me
determine best config for thruput?

 

[Jackie L]

Under Tools - Options - General, make sure Track name autocorrect is not
checked.

Hope this helps.

 

[Rick Brandt]

Under Tools - Options - General, make sure Track name autocorrect is
not checked.

Access 97 doesn't have Name AutoCorrect.

 

[Albert D.Kallal]

usually, there is not that much of a change. Perhaps a bit of slowdown, but
not usually noticeable here...

especially if you have a persistent connection.
(are you sure you got that working, or setup correctly?).

 

[Mike]

declared 'Public rsAlwaysOpen As Recordset' in module
created form to open from AutoExec macro with the following code:
Private Sub Form_Close()
rsAlwaysOpen.Close
Set rsAlwaysOpen = Nothing
End Sub
Private Sub Form_Open(Cancel As Integer)
Set rsAlwaysOpen = CurrentDb.OpenRecordset("tblDummy")
End Sub

Could this be something on the server side of things limiting
throughput. I'm pretty certian we are on an nt/2000 server. Is there a
Network Admin out there who may be able to chime in. I am not sure how
a network manages calls to retrieve data vs security authentication on
each call. Unfortunatly our admins don't want to touch Access issues
with a 10' pole.
The db does runs fine on the server if tables are contained within the
db, but if I seperate to another db and link, this is where things go
south.

 

[Albert D.Kallal]

As long as that form stays open during the operation of the application,
then you got it right

Also, I do believe that tblDummy has to have at least one record.

So, check the above....

Could this be something on the server side of things limiting
throughput. I'm pretty certian we are on an nt/2000 server. Is there a
Network Admin out there who may be able to chime in. I am not sure how
a network manages calls to retrieve data vs security authentication on
each call. Unfortunatly our admins don't want to touch Access issues
with a 10' pole.
The db does runs fine on the server if tables are contained within the
db, but if I seperate to another db and link, this is where things go
south.

Since there is no change in the server settings, then not likely much you
can do. The slowdown is usually due to the fact that the locking file on the
back end is NOT at first created, and this can take a very long time indeed.
So, by forcing a connection to remain open, then all of that extra overhead
should go away (as it only occurs once).