以编程方式在MS Access中创建ODBC连接和链接表 [英] Programatically create ODBC connection and link tables in MS Access

问题描述

我们将MS Access用作Oracle的报告前端.数据通过ODBC连接访问，Access中有两个链接表，即原始数据表.我希望能够建立连接并从Access文件中链接表.这样，用户不必担心为ODBC连接设置DSN(他们中的大多数不是非常技术用户，并且需要进行诸如此类的操作)，我们最终可以将Access文件指向不同的测试环境并生成报告.

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.

打开文件时是否可以动态创建数据库连接，并且可以动态更改链接表链接的位置吗?

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?

推荐答案

您想要Access中的DSN较少的链接表连接.有可能，而且我已经完成了，但是我没有代码.我认为它类似于以下内容(它使用SQL Server源，但是Oracle会有稍微不同的连接字符串).要在启动时创建表，您需要先检查每个tabledef的存在，然后再尝试创建它们，并在Access数据库打开时调用如下所示的子例程.

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.

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

这篇关于以编程方式在MS Access中创建ODBC连接和链接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！