Hi,
I used to have to deal with the similar situation of various Oracle clients
and Excel versions. You can trap and handle the error raised if the relevant
OLE DB provider is not present. For example when MSDAORA.1 is not present,
error code -2147467259 is raised with the following description...
"Oracle client and networking components were not found. These components
are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3
or later client software installation.
Provider is unable to function until these components are installed."
When OraOLEDB.Oracle.1 is not present, error code 3706 is raised with the
description: "Provider cannot be found. It may not be properly installed."
The following code may not be super-elegant, but worked just fine for me
over many years...
Sub OpenMyOracleConnection()
Dim cnn As New ADODB.Connection
Dim strCnn As String
Dim iTry As Integer
strCnn = "Provider=MSDAORA.1;Password=<pwd>;User ID=<userID>;Data
Source=<dbName>;Persist Security Info=True"
' Enter your own specific user and environment info between the angle
brackets
iTry = 1
On Error GoTo Err_ADOError
' Open the Connection
cnn.Open strCnn
On Error GoTo 0
Application.StatusBar = "ADODB connection successfully opened..."
' <Insert your code here>
Application.StatusBar = False
Exit Sub
Err_ADOError:
Select Case Err.Number
Case -2147467259, 3706 ' OLE DB provider is probably missing (MSDAORA.1
or OraOLEDB.Oracle.1 respectively)
strCnn = "Provider=OraOLEDB.Oracle.1;Password=<pwd>;User
ID=<userID>;Data Source=<dbName>;Persist Security Info=True"
' Enter your own specific user and environment info between the angle
brackets as above
If iTry < 2 Then
iTry = iTry + 1
Resume
Else
Exit Sub
End If
Case Else
MsgBox Err.Number & ": " & Err.Description & vbCrLf & vbCrLf &
"Application will now terminate."
Application.StatusBar = False
End Select
End Sub
Trust this helps and let me know how you get on.
Good Luck, Sean.
