Excel Data Connection Locks Access DB, Prevents Second Connection's Refresh

Question

I have two data connections to different queries in the same Access DB. The second one always fails (regardless of which I run first).

When I look at the database, I notice that it has a lock file, which I think is causing the problem. It stays locked until I close the Excel file. Can anyone help me to unlock the db as soon as my import is complete?

---

Additional info:

I'm using Excel and Access 2010.

The error:

"The text file specification 'MyQuery Link Specification' does not exist. You cannot import, export, or link using the specification."

Connection String (note: I'm using Command type: Table):

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin
;Data Source=A:\folder\folder\My Database.accdb
;Mode=Share Deny None
;Extended Properties=""
;Jet OLEDB:System database=""
;Jet OLEDB:Registry Path=""
;Jet OLEDB:Engine Type=6
;Jet OLEDB:Database Locking Mode=0
;Jet OLEDB:Global Partial Bulk Ops=2
;Jet OLEDB:Global Bulk Transactions=1
;Jet OLEDB:New Database Password=""
;Jet OLEDB:Create System Database=False
;Jet OLEDB:Encrypt Database=False
;Jet OLEDB:Don't Copy Locale on Compact=False
;Jet OLEDB:Compact Without Replica Repair=False
;Jet OLEDB:SFP=False
;Jet OLEDB:Support Complex Data=False
;Jet OLEDB:Bypass UserInfo Validation=False

Lastly, based on this post, I've tried changing my Mode from "Share Deny None" to "Read", but it didn't help. Nor do I understand why it would have, but I tried.

Edit: I've continued to research this issue, but cannot find a solution. I've since tried adding in an additional statement into my connection string, ReadOnly = True but no luck.

Ns

Fruther research turned up an Excel Hot Fix and an Access Update for the errors I was recieving. Had both of these installed, but it did not solve the issue. – Ns

Answer 1

I was faced with another issue regarding data connections, and the solution for it actually ended up fixing this long-standing issue as well!

My guess is that the secret lies in "MaintainConnection = False":

Dim i As Integer
Dim awc As WorkbookConnection
Dim c As OLEDBConnection

For i = 0 to ActiveWorkbook.Connections.Count
    Set awc = ActiveWorkbook.Connections.Item(i)
    Set c = awc.OLEDBConnection
    c.EnableRefresh = True
    c.BackgroundQuery = False
    c.Reconnect
    c.Refresh
    c.MaintainConnection = False
Next i

Ns

I wonder if this can be done with settings instead of in code? – Zaratite

Definitely let me know if you find a way to do so! – Ns

Answer 2

Your answer really helped me. I had the same problem, but with Excel files: an Excel file accessing another Excel (when opening) with Microsoft.ACE.OLEDB.12.0 and this datasource file getting locked (in use).

So, I removed the "refresh data when opening the file", and I replace this with your VBA code in Workbook_Open event. But I improved a little your code, because I was getting errors, since I have another ODBC connection (not OLEBD) in my workbook, I had to add this IF. Now everything works well.

Private Sub Workbook_Open()
    Dim i As Integer
    Dim awc As WorkbookConnection

    For i = 1 To ActiveWorkbook.Connections.Count
        Set awc = ActiveWorkbook.Connections.Item(i)
        If awc.Type = xlConnectionTypeOLEDB Then
            With awc.OLEDBConnection
                .EnableRefresh = True
                .BackgroundQuery = False
                .Reconnect
                .Refresh
                .MaintainConnection = False
            End With
        ElseIf awc.Type = xlConnectionTypeODBC Then
            With awc.ODBCConnection
                .EnableRefresh = True
                .BackgroundQuery = False
                .Refresh
            End With
        End If
    Next i
End Sub

Geibel

Awesome! I'm glad this helped get you there, and thanks for adding your updated code for posterity :) – Ns

Answer 3

What you can try is changing the Mode in your connection string to

Mode=Read

instead of

Mode=Share Deny None

Beyrouth

I've tried that in my related post, but to no avail. However, this issue was solved a few years back with the solution above. Thanks :) – Ns

Answer 4

Mode=Share Deny None/Read didn't work

I had to copy the Workbook_Open Sub

Kristankriste