Connect to Oracle via ODBC

[Kim]

I am using the following code to connect to Oracle tables DNSless. The
problem is that I receiving a "runtime error 3146" when the code reaches
tdf.Connect = strConnect. Any hep would be appeciated.

Function ConnectODBC()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String

strConnect = "ODBC;DRIVER={Microsoft ODBC for Oracle}" _
& ";DATABASE=" & strDatabase _
& ";UID=" & strUID _
& ";PWD=" & strPWD & ";"

Set db = CurrentDb()
Set tdf = db.CreateTableDef("tblName")
tdf.SourceTableName = "OracletblName"

tdf.Connect = strConnect

db.TableDefs.Append tdf
db.TableDefs.Refresh

Set tdf = Nothing
Set db = Nothing

End Function


TIA,

Kim

 

[Douglas J. Steele]

Where do you assign values to strDatabase, strUID and strPWD?

As well, you don't need the terminating semi-colon.

 

[Kim]

I have the values assigned in a table that I will be looping through. For
testing purposes I have been plugging in the appropriate values. I removed
the trailing semicolon, but I am still receiving the error.

Kim

 

[Douglas J. Steele]

Take a look at what strConnect contains. Make sure there are no extraneous
spaces or special characters.

 

[Ralph]

Your CreateTableDef looks amiss, try the following link:

http://support.microsoft.com/kb/892490

 

[Douglas J. Steele]

I think the CreateTableDef should be okay.

I've always set the Connect property before defining the SourceTableName
property: you could try that change. (I'm assuming "OracletblName" is the
actual name of a table in the database to which you're trying to link)