Data connection taking minutes to close

[krausr79]

I'm trying to update an access database from excel with an SQL statement. I've tried ADODB command execution, ADODB connection.execute, and DAO database.execute. The data update is successful, but any time the connection gets closed, excel/vba become unresponsive for 5-10 minutes. We are using Office 2010

This person seems to have had the same problem, but I've tried waiting after executing for a couple minutes to close the connection and it hasn't helped.

VBA ADODB Connection close taking too long - Excel 2010 - Stack Overflow

    Dim CN As ADODB.Connection
    Dim CM As ADODB.Command
    Set CN = New ADODB.Connection
    CN.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=S:\Mydbaddress\db.accdb"
    Set CM = New ADODB.Command
    CM.ActiveConnection = CN
    CM.CommandText = sql
    CM.Execute
    Do Until CN.State = 1
        Debug.Print CN.State
        DoEvents
    Loop
    Set CM = Nothing
    CN.Close 'Hangs up here
    Set CN = Nothing

or

    Dim DB As DAO.Database
    Set DB = Workspaces(0).OpenDatabase("S:\OSK-Share\DEPT\ILS\IPSQCBE1.accdb")
    DB.Execute sql, dbFailOnError
    DB.Close 'Hangs up here
    Set DB = Nothing

 

[krausr79]

We never really figured out what was wrong, but we have a workaround. This was a large (over 1 gig) backend on a company shared drive. We split the backend into two backends and had just the stuff relevant to the excel part in it. Then it worked faster again.