VB Connect to ADODB Only if Necesary

I have a connect string that I am attempting to use in VB to connect only if I do not currently have a connection active.  This is the code:

    If NotificationsConn.State = 0 Then
        SQLString = "Data Source=DSource;UID=UserID;PWD=password"
        NotificationsCmd.ActiveConnection = SQLString
    End If

The problem with this code is that it creates a connection to the database every time it is run, even if there is already a connection present.  What am I doing wrong?  Thanks very much.
UPRRDevelopersAsked:
Who is Participating?
 
hesConnect With a Mentor Commented:
Try this

With NotificationsConn
        If .State = adStateClosed Then .Open
End With

Open in new window

0
 
UPRRDevelopersAuthor Commented:
Thanks, but I must be doing something wrong, because even if I put your statement directly after mine, when my connect runs and successfully connects, your code still believes "adStateClosed" is true and tries to open the connection.  So this:

    If NotificationsConn.State = 0 Then
        SQLString = "Data Source=DSource;UID=UserID;PWD=password"
        NotificationsCmd.ActiveConnection = SQLString
    End If

With NotificationsConn
        If .State = adStateClosed Then .Open
End With

Always tries to re-open the connection.




0
 
jkaiosConnect With a Mentor IT DirectorCommented:
How does NotificationsConn variable defined?  Is it defined as a "public" variable in a module?

There are some thing to consider here:

1) If the NotificationsConn variable is defined as a "private" variable in a sub-routine, then this variable will be implicitly set to nothing, when the procedure in which it is defined, ends (thus setting the State to adStateClosed).

2) If it is defined as a "public" variable, then that little piece of code will work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.