check ADO connection

[smk23]

In my code, I check to see if I have a valid ADODB connection with this code:


Dim cnn As ADODB.Connection

If cnn.State <> adStateOpen Then
Set cnn = New ADODB.Connection
cnn.Open fstrADODBConnectionString()
End If

I am getting an error "object variable not set" on the first line of the
"if" statement. The fstrADODBConnectionString is fine. I use it numerous
places. Is it complaining because it doesn't know what cnn is? This line
should be checking to see if the connection is open and open it if not. What
am I missing?

 

[Sylvain Lafontaine]

At this stage, the variable cnn is still an empty reference pointing to
nothing. Recode your snippet in the following way:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection

...

If cnn.State <> adStateOpen Then
cnn.Open fstrADODBConnectionString()
End If


In the general case, it's also possible that the connection is opened but
has other flags set, so you should make sure to test only the adStateOpen
bit:

If ((cnn.State and adStateOpen) = 0) Then
...

 

[Douglas J. Steele]

Declaring cnn only reserves space for the variable: you need to instantiate
it before you can use it.

Try:

Dim cnn As ADODB.Connection

If cnn Is Nothing Then
Set cnn = New ADODB.Connection
cnn.Open fstrADODBConnectionString()
End If

Once you know it's been instantiated, you can check the value of its State
property.