ADO connection issues

[David]

When developing an application that uses ADO, do I just
have to open the database connection once and close it
once?
Can the connection ever be lost?
I am used to developing ASP applications where timeouts
are an issue.

This is a multi-user application and many users will be
using it simultaneously.

Also, how do I get around the problem of cosing the path
to the database in my application, I dont want to have
this hard-coded there and I dont want to use an INI file.
I never had to do this in DAO.

 

[dbracey]

The most prevailant problem here is not how to open and close an AD
connection with your db. It is which db should you be using.

Ask yourself this quesion:
Will your users be modifying data?

If the answer to this quesion is yes, you should not use Access. Th
Jet engine, while good for small databases, is not the correct databas
engine to use if multiple users will be looking at and modifying dat
simultaneosly.

My suggesion is to use SQL to set up your db and then write a GUI i
VB. This will give you much more flexability, room for growth, and SQ
is designed for multi threading (multiple users accessing and modifyin
data simultaneously.) Furthermore, VB has great support for ADO, DAO
and other connection types.

IF you still plan to move forward with your current plan. Writing a V
function to open your ADO connection is the best way, and I would ope
and close the conection as data is requested. Leaving a connection ope
is not a good idea, it will incease the chance of data corruption. No
to mention opening and closing it as needed is much more condusive o
error reporting and prevention.

Hope this is helpful