My concern is that there is a read-write lock in the file I want to connect to because it is being used by an application as shown:
Locked MS Access File
When I try to refresh the data connection in MS Excel, these errors/notifications occur because it is unable to make a connection to the MS Access file that is being used:
I am wondering if there is an option in MS Access that will
deny write only but
allow read or something that will allow me to establish a
read-only data connection to my MS Excel file even though it is locked.
I have found
record-level locking options in the MS Access file, but i’m concerned it may cause problems to the users of the main application that is writing data to the MS Access file. I tried disabling it but it’s still locked. The options are shown:
Record-level locking options
The connection string of my Excel data connection to a Access file is shown below:
Provider=Microsoft.ACE.OLEDB.12.0; User ID=Admin; Data Source=C:UsersACERDesktopTest.MDB; Mode=Share Deny Write; Extended Properties=""; Jet OLEDB:System database=""; Jet OLEDB:Registry Path=""; Jet OLEDB:Engine Type=5; 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
You may use this VBA to connect the Access database in Read Only mode:
Sub ReadFromAccess() Dim cn As Object, rs As Object Dim intColIndex As Integer Dim DBFullName As String Dim TargetRange As Range DBFullName = "C:UsersUsernameDesktopSample.mdb" Application.ScreenUpdating = False Set TargetRange = Sheets("Sheet1").Range("A1") Set cn = CreateObject("ADODB.Connection") cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFullName & ";" & "Persist Security Info=False;Mode=Read;" Set rs = CreateObject("ADODB.Recordset") rs.Open "SELECT * FROM Table Name", cn, , , adCmdText For intColIndex = 0 To rs.Fields.Count - 1 TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name Next TargetRange.Offset(1, 0).CopyFromRecordset rs Application.ScreenUpdating = True On Error Resume Next rs.Close Set rs = Nothing cn.Close Set cn = Nothing On Error GoTo 0 Exit Sub End Sub
- File path C:UsersUsernameDesktopSample.mdb and Sheets(“Sheet1”).Range(“A1”) are editable.
- Instead of DBFullName variable you could use File path
C:UsersUsernameDesktopSample.mdb, as I’ve shown in comments.