Access DB via a LAN is VERY SLOW

[RobCrombie]

I am working on a pgm that will be used on a local LAN (approx 10 users).
It is VB6, and it uses an Access DB (97). (DB will be on the server.)
Copies of my pgm will be on each pc.
Currently the design is such that as any Form or grid loads, it connects, returns a read only recordset and disconnects.
If user changes data, and presses Save, then I reconnect, opening an updateable rs, and if TimeStamp matches, I do the update, and disconnect.
When testing on a single pc (it had the DB on that pc, during first tests), the pgm was fast enough.
Now that the DB is on the LAN, it is much slower.
Here are my thoughts and questions -

All my previous pgms have been single user on single pc.
I have always noted that the very first run of those (after starting the pc), was very slow.
But then subsequent connections were fast.
I concluded that Windows had a trick of keeping some 'stuff' in memory to facilitate future connections to that db.
Now that the DB is on a separate Server, does that mean Windows can no longer use that trick ?

Is there a setting on servers, that allow files or folders to be always 'loaded' ?

We are using Windows XP. I'm using VB6 Pro SP5. The DB is Access 97.
I'm using ADO pure code (no binding, no data controls).
Woukd I be better off(speed wise) maintaining permanent connections ?
What are the increased chances of DB corruption, if I do that ?
The person who installed the Server is a newbie, so LAN hiccups are likely.

It is only recently that the users have been testing with the DB on the Server.
They mentioned, even more 'slowness' when they had focus off in other pgms for a while. (I will call that 'comatose')
Should I build in a 'chirp connection', so that the pgm briefly connects say every 5 minutes, to avoid the comatose ?

Any advice, tips, suggestions would be appreciated.

[randem]

RobCrombie,

Look here for some info.

[shragel]

First try upgrading to access 2003.

second DAO will be much faster.

The best way is to upsize the database to MSDE. And it is supposed to continue to work because u r using pure ADO. Just change the first connection string.

[dee-u]

What?????

second DAO will be much faster.

How come?????

[pnish]

I've never experienced database corruption through having open connections (that's not to say that it won't happen though). Opening & closing connections across a network constantly will certainly drag performance down.

You need to analyse how your program is used. If users are constantly editing, deleting or adding records to the database, it just doesn't make sense to close & re-open the connection after each transaction.

One thing I've done here is to use an 'inactivity timer' which basically keeps track of how long a program has been idle. If the program has been inactive for a predetermined amount of time, all connections are closed (and in some cases the programs themselves are shutdown). You just need to be careful in cases where the user is halfway through editing a record and decides to disappear for a smoke for 10 minutes.

EDIT: I just had a look at the link randem posted, and I reckon that's pretty good advice. (And I liked the library analogy )

[shragel]

DAO is faster Because its access the jet engine directly.
But only use it if you are sure u r staying with access.

[randem]

pnish,

Thanks.