open adodb connection to excel file in read only mode

Question

Case: opening as read-only an excel file (.xlsx) using adodb.connection inside a VBA script in Excel 2013 on Windows 7 64bit.

Problem: the excel file seems to be opened for editing even setting the Mode parameter as Read in the connection string of adodb in order to open the connection in read-only mode:

szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & SourceFile & ";" & _
                "Mode=Read;" & _
                "Extended Properties=""Excel 12.0;HDR=Yes;"";"

Test: I set a break point after opening the connection as below

Set rsCon = CreateObject("ADODB.Connection")
rsCon.Open szConnect

and to test if the source file is opened in 'Mode=Read;' through the adodb and it is still able to be opened in write/edit mode by other users/connections I tried to open it through file explorer while the script was in break mode but an alert saying the file "is locked for editing" is popped up and I am forced to open in read-only mode.

So what might be wrong?

Here I am pasting the whole code mentioned in the below comments so people might run the code to find the problem, it is still a problem and the reason for wanting to open the file in read-only mode is to both avoid corrupting the source file due any bugs in the code and also let other users open the file in write mode to edit the file:

Public Sub ADODBTEST()

    'the paths to a shared file in local network pcs
    Dim szSourceFile As String
    'you might want to comment/uncomment one of the following szSourceFile paths for testing purposes
    szSourceFile = "\\NetworkPC\READONLYACCESS\SourceFile.xlsx" 'A source file shared in a folder giving read-only access
    'szSourceFile = "\\NetworkPC\WRITEACCESS\SourceFile.xlsx" 'A source file shared in a folder giving write access

   'the connection string that sets the Mode=Read
    Dim szConnect As String
    szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & szSourceFile & ";" & _
                "Mode=""Read"";" & _
                "Extended Properties=""Excel 12.0;HDR=No;"";"

    'creating the rsCon connection object
    Dim rsCon As Object
    Set rsCon = CreateObject("ADODB.Connection")
    'opening a connection to the SourceFile.xlsx through szConnect connection string
'***THE LINE WHERE ALL THE PROBLEMS HAPPEN: might be a good idea to set a breakpoint here
    rsCon.Open szConnect

'THE REST OF THE CODE IS MOSTLY FOR OUTPUT DEMONSTRATION
    'SQL code needed to read data from SourceFile.xlsx
    Dim szSQL As String
    szSQL = "SELECT * FROM A1:A1;"

    'creating rsData object
    Dim rsData As Object
    Set rsData = CreateObject("ADODB.Recordset")

    'opening and reading data according to szSQL and rsCon
    rsData.Open szSQL, rsCon, 0, 1, 1

    'Outputing some data for more clarification
    Dim szData As String
    szData = rsData.Fields(0).Value

    'in case the source file pops-up in foreground and is activated
    'if VBA does not reference ThisWorkbook.Worksheets(1) the read data
    'unwantedly is copied to the source file that is activated
    ThisWorkbook.Worksheets(1).Cells(1, 1).Value = "Connection String (Mode=Read): " & rsCon.ConnectionString
    ThisWorkbook.Worksheets(1).Cells(2, 1).Value = szData

    'cleaning the connection and data
    rsData.Close
    Set rsData = Nothing
    rsCon.Close
    Set rsCon = Nothing

End Sub

I'll try your suggestion tomorrow at work. By the way here is a link to my same but more complete question in another forum in case anyone wants to investigate. vbforums.com/… — mhmdghfr
– mhmdghfr, Commented Jan 24, 2016 at 20:56
okay! keep me updated. By the way, do not put adModeRead in the connections string, but just before openning your connection, add this line: rsConn.Mode = adModeRead — Oscar Anthony
– Oscar Anthony, Commented Jan 24, 2016 at 21:15
I tried your suggestion, however since in VBA we do not have direct access to the ADODB built-in constants, I set rsCon.Mode = 1, as defined in the file adovbs.inc located in the folder "C:\Program Files\Common Files\System\ado", and although I watched the rsCon.Mode value being set to adModeRead while debugging, I still have the same problem and the application tries to access the file in Write/Edit mode. — mhmdghfr
– mhmdghfr, Commented Jan 25, 2016 at 9:50

Nick · Accepted Answer · 2017-04-10 18:22:08Z

1

Not sure if I'm not saying the banal thing, but why to have user possibility to open the file while your code runs? I found this page cause was looking for a solution of a problem that file was blocked after the code runs. The solution was very simple - first close, then set to nothing.

rsCon.Close

set rsCon = Nothing

edited Apr 10, 2017 at 18:22

answered Apr 10, 2017 at 18:12

Nick

937 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

open adodb connection to excel file in read only mode

So what might be wrong?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

So what might be wrong?

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related