Make sure you close the connection at the end of your routine:
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.