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

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

Sean - Thanks for responding.

We decided to upgrade the users client to 9i, thinking that was why
they needed OraOLEDB.Oracle provider while those already on 9i were
using MSDAORA provider.

Unfortunately, the issue seems to also be related to the version of
Excel.
With 2000, MSDAORA works; it does not with Excel 2003.

Could this be related to different versions of MDAC?
The Excel 2003 users have 2.8 while the Excel 2000 users have 2.7
(I think - is there a way to verify this, or is it irrelevant?)

The OraOLEDB.Oracle provider does not work with 9i (it seems the OLE
and ODBC components are not part of the standard client
installation...)

Any suggestions are greatly appreciated.


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

You can get hold of MDAC 2.8 at

http://www.microsoft.com/downloads/d...displaylang=en

--

HTH

RP
(remove nothere from the email address if mailing direct)


"cesw" wrote in message
...

Sean - Thanks for responding.

We decided to upgrade the users client to 9i, thinking that was why
they needed OraOLEDB.Oracle provider while those already on 9i were
using MSDAORA provider.

Unfortunately, the issue seems to also be related to the version of
Excel.
With 2000, MSDAORA works; it does not with Excel 2003.

Could this be related to different versions of MDAC?
The Excel 2003 users have 2.8 while the Excel 2000 users have 2.7
(I think - is there a way to verify this, or is it irrelevant?)

The OraOLEDB.Oracle provider does not work with 9i (it seems the OLE
and ODBC components are not part of the standard client
installation...)

Any suggestions are greatly appreciated.


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