How to make a read-only data connection to a read-write locked MS Access using MS Excel?

Question 1

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

Question 2

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

N.B.

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.

.

Leave a Reply Cancel reply