check for OLE DB provider?

[cesw]

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!

[Sean Connolly]

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.

"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
>
>

[cesw]

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.

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>