在 Access 2007 中链接 ODBC 表

发布于 2024-07-09 14:22:13 字数 447 浏览 9 评论 0原文

我有一个 Access 2002 应用程序，它通过 ODBC 使用以下代码链接 Oracle 表：

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

不幸的是，Access 2007 不再接受此语法，表示不再支持 ODBCDirect（运行时错误 3847）并建议使用 ADO 而不是 DAO。有人可以告诉我如何修改此代码以满足 Access 2007 的要求吗？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

花心好男孩 2024-07-16 14:22:13

我发现我可以用一种非常简单的方式解决我的问题，通过删除前两个语句并以这种方式修改第三个语句：

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=Microsoft ODBC for Oracle;SERVER=myserver;UID=myuser;PWD=mypassword", acTable, "SCHEMA.TABLE", "TABLE", False, True

这样表将被链接而不会提示任何内容。如果我将连接字符串保留为简单的“ODBC”，Access 将要求指定 odbc 连接和其他缺少的参数，从而获得我尝试使用前面的语句执行的相同操作。

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.

回复收藏 0 原文

倾城花音 2024-07-16 14:22:13

尝试以下操作：

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

将括号 ([]) 中的文本替换为您的信息。

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.

回复收藏 0 原文

~没有更多了~