Multiple Queries with ADO Recordsets

[bradyboyy88]

So I am trying to fine tune my ado functions and subroutines but keep running into a certain problem. I want to be able to keep the connection open then run queries and updates then close. However if I run SQLQueryDatabaseRecordset and then SQLWriteDatabase I get error '3705' operation is not allowed when the object is open. I am assuming this is related to the fact that you cannot run two queries when both are assigned to the same recordset variable?

Option Explicit
'Database connection variables
Public oConn As Object
Public oRs As Object
Public sConn As String


'Return a query as a recordset
Public Function SQLQueryDatabaseRecordset(SQLQuery As String) As Variant
       
    'Open Record Set by executing SQL
    oRs.Open SQLQuery, oConn
    
    'Disconnect the recordset
    Set oRs.ActiveConnection = Nothing
        
    'Return recordset
    Set SQLQueryDatabaseRecordset = oRs
    
End Function


Public Sub SQLWriteDatabase(SQLQuery As String)
    
    'Open Record Set by executing SQL
    oRs.Open SQLQuery, oConn


End Sub


Public Sub SQLOpenDatabaseConnection(StrDBPath As String, EngineType As Integer)
   
    'Define Connection String by inputting StrDBPath into a larger string (Works for Excel DB)
    'Define Connection String by inputting StrDBPath into a larger string
    'Access Support for engine type
    If EngineType = 0 Then
    
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                 "Data Source=" & StrDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;Mode=Share Exclusive;"
    
    'Excel Support for engine type
    ElseIf EngineType = 1 Then
        
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & StrDBPath & "';" & _
             "Extended Properties=""Excel 12.0;HDR=YES;ReadOnly=0;"";"
    
    End If
                                 
    'Create Connection
    Set oConn = CreateObject("ADODB.Connection")
    oConn.Mode = 3
    oConn.CursorLocation = adUseClient
   
    'Create RecordSet
    Set oRs = CreateObject("ADODB.Recordset")
    oRs.LockType = adLockPessimistic


RetryConnection:
    DoEvents
    On Error GoTo ErrorHandler
        'Connect to the database
20       oConn.Open sConn
    On Error GoTo 0


Exit Sub


ErrorHandler:
'triggered by connection error. Most likely locking type
'MsgBox "looks like we had a connection error. The error number is " & Err.Number & " and the description is " & Err.Description & " on line " & Erl


Err.Clear
Resume RetryConnection


End Sub




Public Sub SQLCloseDatabaseConnection()


    'Close Connection
    oConn.Close
    
    'Clear Memory
    Set oConn = Nothing
    Set oRs = Nothing


End Sub

 

[bradyboyy88]

I guess my problem is i need to close my recordset before opening it to run the next query. The only problem is I cant do oRs.close in my one function since it passes a disconnected recordset to a variable. If i close it and then do anything to that variable then it always returns it cant do operation when object is closed. If there is a way to pass the recordset to the variable and close the old recordset without it closing this new variable recordset then id be golden!