Strange results when linking based on provider info

[Corey-g via AccessMonster.com]

Hi All,

I am linking Oracle tables to my A2K3 FE, which seemed to be working fine.
But now that I'm trying to implement some fancy code to check if the tables
exist, relink, etc... I have found something strange I was hoping somebody
else could explain.

It seems that I can create an ADO connection to the Oracle BE using "OraOLEDB.
Oracle" as the provider string, and I can execute queries and see results.
But when I'm trying to link the tables using DAO I get an ODBC Call Failed
error (3146). If I change the provider string to "{Oralce in XE}" then it
works fine.

So I'm guessing that I can use the driver name in ADO, but I need to use the
DSN name for DAO. Only issue is that I'm trying to do this DSN-Less.

Hope that's clear and someone knows what I'm talking about,

Corey

 

[Corey-g via AccessMonster.com]

Thought I could post some code to help demonstarte...

ADO Working Example:

Public Function TestConnection(DS As String, UID As String, PWD As String) As
Boolean
On Error GoTo ErrorHandler
Dim testCon As ADODB.Connection
Dim testRS As ADODB.Recordset
Set testCon = New ADODB.Connection
With testCon
.Provider = "OraOLEDB.Oracle"
.Properties("Data Source").Value = DS
.Properties("User ID").Value = UID
.Properties("Password").Value = PWD
.CursorLocation = adUseClient
.Open
'.Close   ' commented out to test
End With
Set testRS = New ADODB.Recordset
Set rs = testCon.Execute("Select count(*) from user_tables")
MsgBox rs(0).Value
Set testRS = Nothing
testCon.Close
TestConnection = True
FunctionExit:
Set testCon = Nothing
Exit Function
ErrorHandler:
TestConnection = False
Resume FunctionExit
End Function

DAO Append Table Def not working example:

Public Function Link_ODBC_Tables(Source As String) As Boolean
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim tblDef As DAO.TableDef
Dim rs As DAO.Recordset
Dim strProvider As String

' Temporary settings until testing is complete
Dim strUSER_NAME As String
Dim strPWD As String

strUSER_NAME = "UID"
strPWD = "PWD"
strProvider = "{Oracle in XE}"
'strProvider = "OraOLEDB.Oracle"
'strProvider = "{Oracle in OraHome92}"

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("Select * from tbl_ODBC_Tables")

Do While Not rs.EOF

Set tblDef = db.CreateTableDef(rs("LocalTableName").Value)
With tblDef
.SourceTableName = rs("OracleTableName").Value
.Connect = "ODBC;DRIVER=" & strProvider & ";DBQ=" & Source &
";UId=" & strUSER_NAME & ";Pwd=" & strPWD & ";"
End With
db.TableDefs.Append tblDef    ' ---> This is where I get the Error
rs.MoveNext
Loop
RefreshDatabaseWindow
Link_ODBC_Tables = True
FunctionExit:
Set db = Nothing
rs.Close

Exit Function
ErrorHandler:
Link_ODBC_Tables = False
Dim tThisError As typErrStr
Dim result As Boolean
With tThisError
.lngErrNum = Err.Number
.strErrDesc = Err.Description
.strErrSource = Err.Source
.strErrDetail = " "
.bErrReviewed = True
.bErrNotificationSent = False
End With
result = LogError(tThisError)
If result Then
Resume FunctionExit
End If

End Function

 

[Douglas J. Steele]

I've never seen that particular provider used in conjunction with Oracle and
ODBC.

The only ones with which I'm familiar are the ones Carl Prothman has at
http://www.carlprothman.net/Technology/ConnectionStrings/ODBCDSNLess/tabid/90/Default.aspx

 

[Corey-g via AccessMonster.com]

Thanks Doug,

It was from Carls site that I found the OLEDB.Oralce string so that I could
use ADO with OLEDB. I had already tried to use the other strings that are on
the site, but I tried again, and they don't work. So what I did was look in
the "ODBC Data Source Administrator" under the "Drivers" tab, and using the
Driver Name as the "driver' in the connection string. And sure enough it
worked.

The problem is that the driver name on my development system is different
than the driver name on my test system - so I am also afraid that the driver
name on the prod Citrix server will also be different.

Have you seen this before? Is there a way around this? I don't recall
setting the name during the install, but that is what I'm trying next.

Thanks again,

Corey