Leave OLEDB connection open

Hey all,
I have an excel document that when opened takes the users ID and password and creates an OLEDB connection to a Netezza database. I have no problem opening this connection but when the workbook_open sub is finished running, the connection is no longer accessible by other subs/functions. I need a way for this connection to be left open until the file is closed (or activity timed out) so that query parameters can be changed within the excel document and then a query run by a separate macro. I have the connection declared as Public in a regular module but still once that opening sequence is complete that connection value goes away.
Thanks

Check this out: http://msdn.microsoft.com/en-us/libr.../ff839237.aspx

Hmm, I'll have to test that when I have a minute. The way I've used it is in the creation of WorkbookConnections, as in use code to create a WorkbookConnection and set .MaintainConnection = True, then use the connection as needed. If you don't want the newly created connections to persist, you could have code delete the connection on workbook close or next open. I'll try to do it your way in a bit, in the middle of something right now.

Which line threw the error?

It doesn't look like the maintain connection property is available when creating connection through ADODB. Since you are using conn.Open to open, I assume you are closing with conn.Close. If that is true, I think you can just delete the conn.Close. Note, though, that in general, it's a very bad idea to maintain an open connection to a database. It sucks resources on both ends and offers way more opportunity for corruption and concurrency issues. I know sometimes you just gotta do what you just gotta do, but, if there is any other alternative, like storing a local copy on a worksheet, processing as needed, then reopening connection when you're actually ready to use it. Not sure what your exact situation is, but you may want to give this some more thought.

And, again, you may find the connection manipulation much easier if you create WorkbookConnections as mentioned above.

Try setting maintain connection before opening the connection.

Absolutely, you're already doing it in your connection string:

User ID=" & NZuser & ";Password=" & NZpass

and:

Persist Security Info=True

(though, I think you need to remove the spaces).

Once set, if you need to change, you can read, change, then rewrite the connection string properties. The code below has everything you need to alter the connections. It's probably way overkill for your needs, but it's all I have handy. It reads the necessary connection and command info from the existing connection, and uses "Replace" to change the necessary connection and command properties, then creates a new connection, merging previous and new info. If the new connection is successful, the old connection is deleted.

Option Explicit



Sub swap_connections()

    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    
    
    Dim wcOLD As WorkbookConnection
    Dim wcNEW As WorkbookConnection
    Dim strName As String

    Dim strOLD(0 To 2) As String
    strOLD(0) = "F:\Live_Databases\DATA FILES.mdb"
    strOLD(1) = "F:\Live_Databases\DATA FILES Annex.mdb"
    
    Dim strNEW(0 To 2) As String
    strNEW(0) = "P:\Test_Databases\DATA FILES.mdb"
    strNEW(1) = "P:\Test_Databases\DATA FILES Annex.mdb"
    
    
    Dim i As Long

    Dim ws As Worksheet
    Set ws = wb.Worksheets.Add()
    ws.Names.Add Name:="nmConnection", RefersTo:=ws.Range("$a$1")
    ws.Names.Add Name:="nmCommandText", RefersTo:=ws.Range("$a$2")
    
    Dim odbcConn As ODBCConnection
    Dim oledbConn As OLEDBConnection
    
    Dim skip As Boolean
    skip = False
    
    Dim defdirOLD As String
    Dim defdirNEW As String
    
    Dim strFromOLD As String
    Dim strFromNEW As String
    
    Dim GetOut As Boolean
    GetOut = False
    
    Dim strDescription As String
    Dim strDB As String
    Dim tp As String
    
    For Each wcOLD In wb.Connections
        
        strDB = vbNullString
        
        ' Get current connection string
        If wcOLD.Type = xlConnectionTypeODBC Then
            skip = False
            Set odbcConn = wcOLD.ODBCConnection
            ws.Range("nmConnection").Value = odbcConn.Connection
            ws.Range("nmCommandText").Value = odbcConn.CommandText
        ElseIf wcOLD.Type = xlConnectionTypeOLEDB Then
            skip = False
            Set oledbConn = wcOLD.OLEDBConnection
            ws.Range("nmConnection").Value = oledbConn.Connection
            ws.Range("nmCommandText").Value = oledbConn.CommandText
        Else
            skip = True
            ' This version doesn't include handling for text, web, or other sources
        End If
        
        
        ' Find and replace appropriate connection strings (and/or CommandText)
        For i = LBound(strOLD) To UBound(strOLD)
            GetOut = False
            defdirOLD = "DefaultDir=" & Left(Replace(strOLD(i), Split(strOLD(i), "\")(UBound(Split(strOLD(i), "\"))), ""), Len(Replace(strOLD(i), Split(strOLD(i), "\")(UBound(Split(strOLD(i), "\"))), "")) - 1)
            defdirNEW = "DefaultDir=" & Left(Replace(strNEW(i), Split(strNEW(i), "\")(UBound(Split(strNEW(i), "\"))), ""), Len(Replace(strNEW(i), Split(strNEW(i), "\")(UBound(Split(strNEW(i), "\"))), "")) - 1)
            strFromOLD = Replace(strOLD(i), ".mdb", "")
            strFromNEW = Replace(strNEW(i), ".mdb", "")
            strDB = Replace(Split(strOLD(i), "\")(UBound(Split(strOLD(i), "\"))), ".mdb", "")
            If InStr(ws.Range("nmConnection").Value, strOLD(i)) > 0 Then
                ws.Range("nmConnection").Value = Replace(Replace(ws.Range("nmConnection").Value, strOLD(i), strNEW(i)), defdirOLD, defdirNEW)
                GetOut = True
            End If
            If InStr(ws.Range("nmCommandText").Value, strFromOLD) > 0 Then
                ws.Range("nmCommandText").Value = Replace(ws.Range("nmCommandText").Value, strFromOLD, strFromNEW)
                GetOut = True
            End If
            If GetOut = True Then
                Exit For
            End If
        Next i
       
       
        
        If Left(strNEW(0), 3) = "P:\" Then
            strDescription = "TEST"
        ElseIf Left(strNEW(0), 3) = "F:\" Or InStr(ws.Range("nmConnection").Value, "ServerName=192.168.1.208.1583") > 0 Then
            strDescription = "LIVE"
        End If
        strDescription = strDescription & " connection to " & strDB
        strName = wcOLD.Name
        If Not skip = True Then
            On Error Resume Next
            Set wcNEW = wb.Connections.Add(Name:=strName & "_NEW", Description:=strDescription, ConnectionString:=ws.Range("nmConnection").Value, CommandText:=ws.Range("nmCommandText").Value, lCmdtype:=xlCmdSql)
            If wcNEW.Type = xlConnectionTypeODBC Then
                tp = "ODBC"
                With wcNEW.ODBCConnection
                    .BackgroundQuery = False
                    .RefreshOnFileOpen = False
                    .SavePassword = True
                    .SourceConnectionFile = ""
                    .ServerCredentialsMethod = xlCredentialsMethodIntegrated
                    .AlwaysUseConnectionFile = False
                    
                End With
            ElseIf wcNEW.Type = xlConnectionTypeOLEDB Then
                tp = "OLEDB"
                With wcNEW.OLEDBConnection
                    .BackgroundQuery = False
                    .RefreshOnFileOpen = False
                    .SavePassword = True
                    .SourceConnectionFile = ""
                    .ServerCredentialsMethod = xlCredentialsMethodIntegrated
                    .AlwaysUseConnectionFile = False
                    .MaintainConnection = True
                End With
            End If
            
            If Err.Number > 0 Then
               MsgBox ("Fail on: " & strName & vbCrLf & "Type = " & tp & vbCrLf & "Error Description = " & Err.Description)
               Stop
            Else
                ' If new connection successfully added, delete old and rename new
                wcOLD.Delete
                ' Remove "_NEW" from Name
                wcNEW.Name = strName
            End If
            On Error GoTo 0
        Else
            MsgBox ("Skip = True" & vbCrLf & "strName = " & strName)
        End If
            
    Next wcOLD
    
    ws.Delete
    
    Set wb = Nothing
    Set wcOLD = Nothing
    Set wcNEW = Nothing
    

End Sub

Note: in the above code, I copy the connection and command text into a new worksheet, rather than just loading them into string variables for the Replace function. I found that some of my really long connection and command strings were being truncated (to somewhat random lengths) when I used variables. A worksheet cell has no virtually no limit to the amount of text it can hold, so, I add a sheet, name two ranges, "nmConnection" and "nmCommandText", paste the old values, edit, create the new connection, then delete the worksheet that contained the named ranges.

Hmm, I'm not entirely sure I'm following your last post. If you create a "WorkbookConnection" object in your code, that connection will persist until it is removed, either by deleting it or by closing the file without saving. If you create an ADODB connection, like in your original post, it definitely will not persist; other than, perhaps, if you open the connection in one sub and close it later, in another sub, after you are done with it.