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
