adodb.connection timeout problem

[zakman]

Hello everyone,

Suppose you have a program that establishes a database connection
upon program startup and closed the connection when the user exits.

Joe User starts the application (which opens the db connection),
then goes to town for 3 hours... when he returns, the connection
has apparently timed out (gee, wonder why??)...

Is there a way to check to see if the connection has timed out?

I have used the .state property to see if a connection is open or
closed, but didn't know if I could use that same .state to determine
if the connection actually timed out or it was lost...

What type of activity has occured on the connection? User authentication
with the database, then it sits idle (until the user specifies data to
be accessed).

Any ideas?

 

[CassandraR]

Hi Zakman:

I follow a simple procedure to avoid the hassles that you mention. When the program starts, it establishes a connection to the database, but does not open it. When something needs to be done with the database, then the program opens the database, does it thing, and then closes the database again. When the program exits, it releases the connection to the database.

For example:

At program start:

    m_cnnMyDB.ConnectionString = ". . ."

During the program:

    On Error Goto MyErrorHandler
    strSQL = "SELECT * FROM . . . "
    m_cnnMyDB.Open
    Set rsData = m_cnnMyDB.Execute(strSQL)
    ' Process the data
    Set rsData = Nothing
    If m_cnnMyDB.State <> adStateClosed Then m_cnnMyDB.Close
    On Error Goto 0

On program unload:

    Set rsData = Nothing
    If m_cnnMyDB.State <> adStateClosed Then m_cnnMyDB.Close

Just a suggestion.

Cassie

 

[CassandraR]

Hi:

My previous comment left out the major point: keep the database open for as short a time as reasonable.

If I need the data for longer than a quick change, then I copy the data to other controls or variables. My programs do not hold onto recordsets or open database connections for longer than necessary. Doing this may provide a little more safety on the actual database. Also, the "left hand right hand" problem doesn't occur since one block of code releases its connection before another block of code tries to access the database.

Cassie