如何通过ODBC将表与VBA代码链接 [英] How to link tables with VBA code over ODBC

问题描述

实际上，我使用ODBC连接将Acces女士连接到PostgreSQL-DB的表.我通过使用外部数据/导入ODBC-Link命令连接它们.效果很好.

Actually I use a ODBC-Connection to connect Ms Acces to tables of a PostgreSQL-DB. I connect them by using the External Data/Import ODBC-Link command. It works fine.

但是如何使用VBA链接表?

But how can I use VBA to link my tables?

推荐答案

使用VBA将表与ODBC链接时，可以添加和APP=参数来指定通常在连接属性中显示的应用程序名称.在您的数据库服务器上.

When using VBA to link a table with ODBC, you can add and APP= argument to specify an application name that will generally show in the properties of the connection on your database server.

例如，这是链接表的示例ODBC连接字符串:

For example, here is a sample ODBC Connection string for a linked table:

ODBC;Driver={SQL Server};Server=MyServer\SQLExpress;Database=MyDatabase;APP=My App Title;Trusted_Connection=Yes;

My App Title是将用作该连接的应用程序名称的字符串.

My App Title is the string that will be your Application Name for that connection.

更新1 以回应OP的进一步评论:

Update 1 In response to further comment by the OP:

以下是在VBA中通过ODBC链接表的示例代码.为了简化此操作，您还应该始终在每次重新连接之前始终删除ODBC链接表，以确保尊重您的选项，并且Microsoft Access更新链接表的架构.此示例显示了SQL Server数据库的连接字符串，因此您只需更改PostgreSQL-DB的连接字符串即可.其余的VBA代码将相同.

Here is sample code to link a table via ODBC in VBA. To facilitate this, you also should always delete the ODBC linked table each time before re-linking it to make sure that your options are respected, and that Microsoft Access updates the schema for the linked table. This example shows a connection string for a SQL Server database, so all you would need to change is the connection string for your PostgreSQL-DB. The remaining VBA code would be the same.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConn As String
Dim ODBCTableName as String
Dim AccessTableName as String

Set db = CurrentDb()
ODBCTableName = "dbo.YourTable"
AccessTableName = "YourTable"
strConn = "ODBC;Driver={SQL Server};Server=YOURSERVER\SQLINSTANCE;Database=MYDATABASE;Trusted_Connection=No;UID=MyUserName;PWD=MyPassword"
db.TableDefs.Refresh
For Each tdf In db.TableDefs
    If tdf.Name = AccessTableName Then
        db.TableDefs.Delete tdf.Name
        Exit For
    End If
Next tdf
Set tdf = db.CreateTableDef(AccessTableName)

'===============================
'If your connection string includes a password
'and you want the password to be saved, include the following 3 lines of code
'to specify the dbAttachSavePWD attribute of the TableDef being created
'If you don't want to save the password, you would omit these 3 lines of code
'===============================
If InStr(strConn, "PWD=") Then
    tdf.Attributes = dbAttachSavePWD
End If

tdf.SourceTableName = ODBCTableName 
tdf.Connect = strConn
db.TableDefs.Append tdf

这篇关于如何通过ODBC将表与VBA代码链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！