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

Question

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:\Users\ACER\Desktop\Test.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

Solution

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:\Users\Username\Desktop\Sample.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:\Users\Username\Desktop\Sample.mdb and Sheets("Sheet1").Range("A1") are editable.
• Instead of DBFullName variable you could use File path C:\Users\Username\Desktop\Sample.mdb, as I've shown in comments.

.

Comments

[Rajesh Sinha]

Are you comfortable with VBA to establish the Read only connection from Access to Excel?

[Pherdindy]

Yes I am quite comfortable with VBA. If you have a solution it'll be appreciated

[Rajesh Sinha]

Okay wait let first let me tets the Code !

[Rajesh Sinha]

To set the Mode either set the Connection Mode property or the Connection string. db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.mdb;Persist Security Info=False;Mode=Read;" Check this if clicked for you then I'll post it as answer.

[Rajesh Sinha]

Cont.. other should be you could store the Databasee in a shared folder where the other users have read-only permission. Then they should still be able to view, but not change, the data in the linked table.

[Pherdindy]

@RajeshS I have placed my connection string in the main post I am unsure where to place that should I replace the Provider=Microsoft.ACE.OLEDB.12.0; in the first line of my connection string to db.Open "Provider=Microsoft.Jet.OLEDB.4.0; ?

[Pherdindy]

Awesome. I'll try it out tomorrow :).

[Pherdindy]

I have a syntax error to this one line: cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFullName & ";" Persist Security Info=False;Mode=Read;"

[Pherdindy]

Okay I think I found the error. The line should be: cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFullName & ";" & "Persist Security Info=False;Mode=Read;" just lacked an ampersand and semi-colon in string concatenation

[Pherdindy]

Good code this is a life saver

[Rajesh Sinha]

@Pherdindy, glad to help you,, keep asking ☺

[Rajesh Sinha]

@Pherdindy, Yes the & sign was missing now I've edited the post, thanks. ☺

[Pherdindy]

Hi, i'm liking your code as it's the only solution I have at the moment. My concern is that I get this error: "Method 'CopyFromRecordset' of object 'Range' failed" on other computers while on some it works perfectly. Do you know what causes this error?

[Rajesh Sinha]

@Pherdindy,, may possible the Recordset (data)You retrieve includes Null values. Just Check & confirm it?

[Rajesh Sinha]

@Pherdindy, perhaps Database has very long string in column, the RecordSet retrieves from any MEMO data type may alos generates this error.

[Pherdindy]

Thank you will look at the data provided by the database. It's weird it works on other computers but not on some of them