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;Use r
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.

"cesw" wrote:


I am working in Excel 2000 VB 6.0 with an Oracle 9.2 client
Some of the users have Excel 2002 or 2003, VB 6.3, and/or Oracle 8i or
9i clients.
So far, everyone has been able to use either MSDAORA.1 or
oraOLEDB.Oracle for the connection string provider.

Rather than hard-coding the provider, and maintaining multiple versions
of the code, and lists of who has what, which could change with an
upgrade, is there a way to check which dll the user has, and then set
the provider from that?

Thanks!


--
cesw
------------------------------------------------------------------------
cesw's Profile: http://www.excelforum.com/member.php...o&userid=27117
View this thread: http://www.excelforum.com/showthread...hreadid=473094