ADODB connection troubles

[dmckenna]

Hello, hopefully you can please help me.

I've been tasked to upgrade an old system and there's many different versions of VB code that uses MDAC to talk to MSSql.

Version 1 of the program is:

Public cnxEventLog As New ADODB.Connection (defined globally)
 
<code in a procedure follows>

If (cnxEventLog.State <> adStateOpen) Then
        'Set cnxEventLog = New ADODB.Connection
        cnxEventLog.Open strConnection
    End If

Version 2 of the program is:

Public cnxEventLog As ADODB.Connection (defined globally)
 
<code in a procedure follows>

     'If (cnxEventLog.State <> adStateOpen) Then
        Set cnxEventLog = New ADODB.Connection
        cnxEventLog.Open strConnection
    'End If

I appreciate that version 1 checks the connection state before trying to reconnect but it doesn't seem to do all of the connection logic, whereas version 2 always reconnects.

Version 1 is currently running on a machine with VBA. The system runs fine all day calling the connection procedure as shown and then doing an insert, update or query.

Approximately once a day it tries to do 2 database calls (queries, inserts, etc.) and each db call runs the connection procedure first. Once a day, I get the error "ADOB.Command Operation is not allowed on a object referencing a closed or invalid connection". Unfortunately I cannot determine 100% whether that is the only error since my error display logic overwrites with the most recent error, there may or may not be a connection error thrown which is then overwritten with the ADOB.command.

I personally observed this problem and after trying to do inserts, etc. to the database they all failed. I left the system to walk away to get my laptop and when I returned everything worked fine. No changes or actions were taken on the system while I was away.

There are many other times a day (approximately 50) when they do these 2 database calls at the same time and everything works with no problems.

I would really appreciate any help you can provide,

Thanks,

Dave