oracle 在 MS Access 中以编程方式创建 ODBC 连接和链接表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3096236/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Programatically create ODBC connection and link tables in MS Access
提问by FrustratedWithFormsDesigner
We are using MS Access as a reporting front-end for Oracle. The data is accessed via an ODBC connection, and there are two linked tables in Access to Oracle that are the raw data tables. I'd like to be able to set up the connection and link the tables from within the Access file. That way users don't have to worry about setting up an DSN for the ODBC connection (most of them are not very technical users, and will require hand-holding for something like this), and we can eventually have the Access file point to different test environments and generate reports.
我们使用 MS Access 作为 Oracle 的报告前端。数据是通过 ODBC 连接访问的，Access to Oracle 中有两个链接表，它们是原始数据表。我希望能够从 Access 文件中建立连接并链接表。这样用户就不必担心为 ODBC 连接设置 DSN（他们中的大多数不是技术含量很高的用户，并且需要手动进行此类操作），并且我们最终可以将 Access 文件指向不同的测试环境并生成报告。
Is it possible to have the database connection created dynamically when the file is opened, and can I dynamically change where my Linked Tables link to?
是否可以在打开文件时动态创建数据库连接，我是否可以动态更改链接表链接到的位置？
回答by Tahbaza
You want a DSN-less linked table connection from Access. It is possible and I've done it but I don't have the code with me. I think it was something like the below (this uses a SQL Server source but Oracle would just have a slightly different connection string). To have the table(s) created on startup you'll need to check for the existence of each tabledef prior to attempting to create them again and call a subroutine like the below upon Access database open.
您需要来自 Access 的无 DSN 链接表连接。这是可能的，我已经做到了，但我没有代码。我认为它类似于下面的内容（这使用了 SQL Server 源，但 Oracle 的连接字符串略有不同）。要在启动时创建表，您需要在尝试再次创建它们之前检查每个 tabledef 是否存在，并在 Access 数据库打开时调用如下子例程。
Function LinkTables()
Dim DB As Database, tDef As TableDef
Set DB = CurrentDb
Set tDef = DB.CreateTableDef("YourAccessLinkedTableNameHere")
tDef.Connect = "ODBC;Driver={SQL Server};Server=srvname;Database=dbname;UID=sqluserid;PWD=sqlpwd"
tDef.SourceTableName = "dbo.YourSourceTableNameHere"
DB.TableDefs.Append tDef
End Function
回答by David-W-Fenton
I do my programming on a workstation with a DSN defined, and then before distributing for production use, run a variant of Doug Steele's code to convert all the DSN-based connect strings to be DSN-less.
我在定义了 DSN 的工作站上进行编程，然后在分发用于生产用途之前，运行Doug Steele 代码的变体，将所有基于 DSN 的连接字符串转换为无 DSN。
