Connect to Oracle via ODBC



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

Set tdf = Nothing
Set db = Nothing

End Function



Douglas J. Steele

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

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


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.


Douglas J. Steele

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

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)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question