Opinions regarding DB connections on a LAN needed!

[uol98]

I have two pc's set up 24 hrs a day which are just for order entry. Both pc's are connected to the same Access mdb on a 3rd pc and process 300 records max between them in a day. I am making the connection to the database upon program startup without disconnection. The users have reported occasional errors with the two systems losing their connection to the database - thereby needing a reboot to regain connection. My question is: Am I using a good practice to connect to the db at program startup or should I connect and then disconnect when a user finishes entering a new record?

If it matters, I am using ADO 2.1

Running VB6 SP3, NT4 SP51

Thanks all!

[Surgeon]

I experienced much the same problem myself in the past. I think the problem occurs when the network is too busy with something else than your db application traffic(let's say that somebody is copying something big through the network, from one PC to another, this will decrease the access-time of the others computers to the hub). I have some suggestions :
1)No, I don't think you should close/open the connection each time one of your client app needs access to your server database. This will definetely slow down your run time speed.
2) If your LAN is 10 MBps based, try making the jump to 100 Mbps. Of course, this would mean new network cards, new hub i.e. more money :-(
3) Try increasing your connection timeout. Generally it is set to 60 seconds by default. Increasing it to say 2 minutes will decrease the probability of your connection choking in too much LAN activity.
4) Write code that verifies if your connection is still alive each time one of your client apps try to read/edit/delete something in your database. If the connection is not active, try to revive it from within code, and if this is not possible due to network malfunction or over-activity, pop up a message and close the client app in a civilized manner.

I hope this would help, if only a bit.

Surgeon

[uol98]

Thanks Surgeon!! I'll probably start testing for a connection before I do any writing or updating to the db.