CODEWITHSUNDEEP
vbaexcelconnectionadodb
user3791508
user3791508

Reputation: 43

Excel VBA - How to force close an ADO connection?

I am trying to connect an Excel sheet to a local Access database through VBA, and I ran into the problem of the Access database being locked out from a previous debug, and either error 3704 or 3709 (see below) are being thrown when trying to debug.

Now I am new to VBA so there is a high chance of me not correctly connecting it to the database. Is there any way of forcing the database to close?

The following are the code for connection:

    Dim objAccess As Object
    Dim strFile, strConnection As String
    strFile = "Address of SampleDB.accdb"

    Set objAccess = CreateObject("Access.Application")
    Call objAccess.OpenCurrentDatabase(strFile)

    'get the connection string
    strConnection = objAccess.CurrentProject.Connection.ConnectionString
    objAccess.Quit

    Set cn = CreateObject("ADODB.Connection")
    cn.ConnectionString = strConnection

So to check if the state is open, I wrote an if block to check, but this is when I get "run-time error 3074: Operation is not allowed when the object is closed" on the cn.CloseConnection line:

    If cn.State = adStateOpen Then
        cn.Close
        Else
            MsgBox "The connection is already open."
    End If

So I commented the above code out and replaced it with the following, but I get "run-time error 3079:The connection cannot be used to perform this operation. It is either closed or invalid in this context." on the Set rs.ActiveConnection = cn line. It goes into the (cn.State And adStateOpen) = adStateOpen is true part first. 

    If (cn.State And adStateOpen) = adStateOpen Then
        MsgBox "cn Connection is already open."
        Else
        cn.Open strConnection
        MsgBox "Connection is now open"
    End If
    Set rs = Nothing
    Set rs = CreateObject("ADODB.Recordset")
    Set rs.ActiveConnection = cn

I do clean up the connection at the end by using cn.Close and Set cn = Nothing. However the code stopped before it reached to that point and is now locking me out of the Access database. As for the query I ran, it was just a basic select statement with a variable:

    Dim iArea As String
    Dim strSQL As String
    Dim dId As Integer
    iArea = "Sales"
    strSQL = "SELECT [deptID] FROM [tblDept] WHERE [deptArea]='" & iArea & "'"

    rs.Open
    Set rs = cn.Execute(strSQL)
    dId = rs.Fields(0)
    MsgBox dId 
    rs.Close
    Set rs = Nothing

The code originally stopped because my syntax for placing the variable in the SQL was wrong. The only way I was able to debug again is by restarting the computer. Any advice would be greatly appreciated.

Upvotes: 4

Views: 5515

Answers (1)

VBlades
VBlades

Reputation: 2251

I think the issue lies less with ADO and more with the lack of error handling in your code. You should definitely read up on it.

Unless you specify otherwise, the default mode of error handling will throw a message box, and after you click End, it will just end the routine at the line it stopped on. Look into On Error Goto, and create block which executes after you handle the error. You can set a block of code that executes every time, error or not, which will close your connections and deallocate your objects. This is how you "force" the connection to close, by making sure the code to close it runs without fail. Basic shell looks like this:

Sub MySub()
On Error Goto MySub_ErrorHandler

    '...Code here...
    '1.  If error happens, goto 2.

ExitMe:
    '4. Clean up and run the code which needs to definitely run here.  Close connections, deallocate your objects, etc.

    '5. Finally, exit the routine with all loose ends tied up. 
    Exit Sub
MySub_ErrorHandler:
    '2. Handle the error here...

    '3. Then go to ExitMe...
    Goto ExitMe

End Sub

There is the danger of putting code in ExitMe which will raise an error; in this case, code execution will go to the MySub_ErrorHandler again and you can get into an infinite loop. So just be careful testing your code in ExitMe.

Upvotes: 1

Related Questions