ADODB connection troubles

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 391,145 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,143 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser:

Thread Tools Display Modes

Views: 1933 | Replies: 0

•

Join Date: Apr 2007

Posts: 1

Reputation: dmckenna is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

dmckenna

Offline

Newbie Poster

ADODB connection troubles

Apr 26th, 2007

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:

Help with Code Tags

(Toggle Plain Text)

    
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:

Help with Code Tags

(Toggle Plain Text)

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