PDA

View Full Version : How prevent open Excel file automatically when open connection to an Excel file



tttin20
10-19-2018, 01:45 AM
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
10-22-2018, 01:30 AM
You haven't posted the code which actually opens the connection. Also: Are you just reading data? Or are youalso writing?

tttin20
10-22-2018, 07:18 PM
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
10-23-2018, 02:29 AM
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
10-23-2018, 03:08 AM
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
10-23-2018, 08:22 AM
I tried from the same computer, with two separate instances of Excel and it works as advertised.