Link ODBC Table in Access 2007
I have an Access 2002 application that 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 no longer accepts this syntax, stating that ODBCDirect is no longer supported (runtime error 3847) and suggests using ADO instead of DAO. Can someone please tell me how I can modify this code to suit Access 2007?
source to share
I found that I can solve my problem in a very simple way by removing the first two statements and changing the third in 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 will be linked without asking for anything. If I leave the connection string as simple "ODBC", Access instead asks for an odbc connection and other missing parameters, thereby getting the exact same thing I tried with the previous statements.
source to share
Try the following:
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 the text in brackets ( []
) with your information.
source to share