Link an ODBC table in Access 2007

Question

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?

Answer 1

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.

Answer 2

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.