Solved: Excel ADODB Connection resulting in 'Catastrophic Error - 2147418113'

[Pool Master]

Hi,

I'm developing an Excel based application that dynamically populates a listbox by quering an external Excel file using ADO. During testing i've discovered that Excel throws a 'Catastrophic Error - 2147418113' error message when 2 users simultaneously try to connect to the same Excel database. The error only occurs on the "slower" user and i've trapped it at the 'Connection.Open' method. I've tried to handle this error by using On Error Goto and retrying the connection, but Excel only retries once and then ignores the On Error Goto and displays the error. Can someone please assist me in modifying the code to continously retry the ADODB connection until successful?

[vba]
Sub Open_Excel_Connection()
Retry:
Const ConnectionString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=...DO NOT USE - Alternate Hierarchy Log.xls;Extended Properties=Excel 8.0;"
Set Connection = New ADODB.Connection
Connection.ConnectionString = ConnectionString
On Error GoTo Retry:
If Connection.State = ObjectStateEnum.adStateClosed Then Connection.Open
On Error GoTo 0
End Sub
[/vba]

Thank you,

[Bob Phillips]

See if this works any better

[vba]

Sub Open_Excel_Connection()
On Error GoTo ErrHandler:
Set Connection = New ADODB.Connection
Retry:
Const ConnectionString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=...DO NOT USE - Alternate Hierarchy Log.xls;" & _
"Extended Properties=Excel 8.0;"
Connection.ConnectionString = ConnectionString

If Connection.State = ObjectStateEnum.adStateClosed Then Connection.Open

Exit Sub
ErrHandler:
Resume Retry
End Sub
[/vba]

[Pool Master]

Thank you so much xld. That worked perfectly!!!!