Geek Answers Handbook

Locking the connection of Excel data to access the database prevents the second connection from being updated

I have two connections to different queries in the same access database. The second one always fails (no matter which one I run on first).

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

Additional Information:

I am using Excel and Access 2010.

Mistake:

"Text file specification" MyQuery link specification "does not exist. You cannot import, export or link using the specification."

Connection string (note: I use 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

Finally, based on this post , I tried to change my mode from "Share Deny None" to "Read", but that didn't help. I also don't understand why this would be, but I tried.

Edit: I continued to research this problem, but cannot find a solution. Since then, I tried to add an additional statement to the connection string ReadOnly = True, but no luck.

+4
source share
3 answers

, !

, "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
+5

,

=

= Share Deny None

+1

. , Excel: Excel, Excel ( ) Microsoft.ACE.OLEDB.12.0, ().

So, I deleted the “update data when opening the file”, and I replaced it with VBA code in the Workbook_Open event. But I improved my code a bit because I was getting errors, since I have another ODBC connection (not OLEBD) in my book, I had to add this IF. Now everything is working 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
+1
source

More articles:


All Articles