Reputation: 1637
I have an Access 2002 application which links an Oracle table via ODBC with this code:
Set HRSWsp = CreateWorkspace("CONNODBC", "", "", dbUseODBC)
Set HRSConn = HRSWsp.OpenConnection("HRSCONN", dbDriverPrompt, , "ODBC;")
DoCmd.TransferDatabase acLink, "Database ODBC", HRSConn.Connect, acTable, "SCHEMA.TABLE", "TABLE", False, True
Unfortunately, Access 2007 doesn't accept this syntax anymore, saying that ODBCDirect is no more supported (Runtime error 3847) and suggesting to use ADO instead of DAO. Could someone please tell me how can I modify this code to satisfy Access 2007?
Upvotes: 1
Views: 8786
Reputation: 1637
I found that I could solve my problem in a very simple way, by deleting the first two statements and modifying the third this way:
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=Microsoft ODBC for Oracle;SERVER=myserver;UID=myuser;PWD=mypassword", acTable, "SCHEMA.TABLE", "TABLE", False, True
This way the table would be linked without prompting for anything. If I leave the connect string a simple "ODBC", instead, Access will ask to specify the odbc connection and the other missing parameters, thus obtaining the same thing I tried to perform with the previous statements.
Upvotes: 3
Reputation: 29806
Try this:
Dim tbl As New ADOX.Table
Dim cat As New ADOX.Catalog
cat.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[x:\your_access_db.mdb];Jet OLEDB:Engine Type=4"
tbl.NAME = "[Access_table_name]"
Set tbl.ParentCatalog = cat
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;Driver={Microsoft ODBC For Oracle};Server=OracleServerName;Uid=[user];Pwd=[password];"
tbl.Properties("Jet OLEDB:Cache Link Name/Password") = True
tbl.Properties("Jet OLEDB:Remote Table Name") = "[Oracle_Schema].[Table]"
cat.Tables.Append tbl
cat.ActiveConnection.Close
Replace text in brackets ([]
) with your info.
Upvotes: -2