How prevent open Excel file automatically when open connection to an Excel file

[tttin20]

Dear friends,

I am using Excel 2007 and writing a tool to get data from another Excel (use as database) by ADODB connection like this:

GetConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & strDBName & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=Yes"";"

The problem occurs when two users use the tool and connect to database at the same time, the second connection is failed and open Excel database automatically.

Could someone give me an instruction or advice to prevent open Excel database automatically. Thanks!

[Jan Karel Pieterse]

You haven't posted the code which actually opens the connection. Also: Are you just reading data? Or are youalso writing?

[tttin20]

Dear Jan Karel Pieterse,

The connection use to read and write. Here are my code to read database:

Dim rsData As New ADODB.recordset
Dim rsCon As New ADODB.Connection
Dim szConnect As String
    
    szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & SourceFile & ";" & _
                "Extended Properties=""Excel 12.0;HDR=Yes"";"
    
    rsCon.Open szConnect
    rsData.Open sqlString, rsCon, 0, 1, 1

The problem is at the line:

rsCon.Open szConnect

Excel application of second user opens the Excel database file in readonly mode after executing line above.

Regards!

[Jan Karel Pieterse]

Make sure you close the connection at the end of your routine:

rsCon.Close

In the Open statement, add a loop and check for success of opening the connection. If fails, retry opening a number of times:

Sub foobar()
    Dim rsData As New ADODB.Recordset
    Dim rsCon As New ADODB.Connection
    Dim szConnect As String
    Dim SourceFile As String
    Dim lTry As Long

    SourceFile = "C:\Users\***\Documents\AdoTest.xlsm"

    szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & SourceFile & ";" & _
                "Extended Properties=""Excel 12.0;HDR=Yes"";"
    On Error Resume Next
    Err.Clear
    Do
        lTry = lTry + 1
        rsCon.Open szConnect
        DoEvents
    Loop Until Err.Number = 0 Or lTry > 10
    If Err.Number = 0 Then
        rsData.Open "Select * from Database", rsCon, adOpenStatic, adLockReadOnly, 1
    Else
        MsgBox "Failed to open connection to database!"
    End If
    rsCon.Close
End Sub

Though I must say that for me the rsCon.Open does not fail.

[tttin20]

Dear Jan Karel Pieterse,

Thanks for your reply! But my scenario is that two computers connect to Excel database file at the same time.

Please download the attached file and save to two computers (and change the Excel database file path). If you click the button of two computers at the same time, one of your computers will open Excel database file regardless of catching Error or not.

Regards!

[Jan Karel Pieterse]

I tried from the same computer, with two separate instances of Excel and it works as advertised.