如何为 Access 中的链接表创建到 SQL Server 的无 DSN 连接

Question

如题所述

Answer 1

可以使用 DSN 在 Microsoft Access 中创建链接的 SQL Server 表。但是，在将数据库移动到另一台计算机时，必须在该计算机上重新创建 DSN。当您必须在多台计算机上执行此过程时，此过程可能会有问题。如果未正确执行此过程，则链接表可能无法找到 DSN。因此，链接表可能无法连接到 SQL Server。如果想要创建指向 SQL Server 表的链接，但是不想在“数据源”对话框中对 DSN 进行硬编码，请使用下列方法之一来创建到 SQL Server 的无 DSN 连接。方法1：使用 CreateTableDef 方法通过使用 CreateTableDef 方法，可以创建链接表。要使用此方法，请创建新模块，然后将以下 AttachDSNLessTable 函数添加到新模块中。'//Name : AttachDSNLessTable '//Purpose : Create a linked table to SQL Server without using a DSN '//Parameters '// stLocalTableName: Name of the table that you are creating in the current database '// stRemoteTableName: Name of the table that you are linking to on the SQL Server database '// stServer: Name of the SQL Server that you are linking to '// stDatabase: Name of the SQL Server database that you are linking to '// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection '// stPassword: SQL Server user password Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) On Error GoTo AttachDSNLessTable_Err Dim td As TableDef Dim stConnect As String For Each td In CurrentDb.TableDefs If td.Name = stLocalTableName Then CurrentDb.TableDefs.Delete stLocalTableName End If Next If Len(stUsername) = 0 Then '//Use trusted authentication if stUsername is not supplied. stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes" Else '//WARNING: This will save the username and the password with the linked table information. stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword End If Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect) CurrentDb.TableDefs.Append td AttachDSNLessTable = True Exit Function AttachDSNLessTable_Err: AttachDSNLessTable = False MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description End Function 要调用 AttachDSNLessTable 函数，请在 AutoExec 宏或在启动窗体 Form_Open 事件中添加类似于以下代码示例之一的代码： 在使用 AutoExec 宏时，调用 AttachDSNLessTable 函数，然后从 RunCode 操作传递类似于以下内容的参数。AttachDSNLessTable ("authors", "authors", "(local)", "pubs", "", "") 使用启动窗体时，将类似于以下内容的代码添加到 Form_Open 事件。Private Sub Form_Open(Cancel As Integer) If AttachDSNLessTable("authors", "authors", "(local)", "pubs", "", "") Then '// All is okay. Else '// Not okay. End If End Sub注意：在将多个链接表添加到 Access 数据库时必须调整编程逻辑。方法2：使用 DAO.RegisterDatabase 方法通过使用 DAO.RegisterDatabase 方法，可以在 AutoExec 宏或在启动窗体中创建 DSN 连接。虽然此方法不会删除 DSN 连接的要求，但是它可以通过创建代码形式的 DSN 连接来帮助您解决问题。要使用此方法，请创建新模块，然后将以下 CreateDSNConnection 函数添加到新模块中。'//Name : CreateDSNConnection '//Purpose : Create a DSN to link tables to SQL Server '//Parameters '// stServer: Name of SQL Server that you are linking to '// stDatabase: Name of the SQL Server database that you are linking to '// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection '// stPassword: SQL Server user password Function CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean On Error GoTo CreateDSNConnection_Err Dim stConnect As String If Len(stUsername) = 0 Then '//Use trusted authentication if stUsername is not supplied. stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "Trusted_Connection=Yes" Else stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr End If DBEngine.RegisterDatabase "myDSN", "SQL Server", True, stConnect '// Add error checking. CreateDSNConnection = True Exit Function CreateDSNConnection_Err: CreateDSNConnection = False MsgBox "CreateDSNConnection encountered an unexpected error: " & Err.Description End Function 注意：如果再次调用 RegisterDatabase 方法，则会更新 DSN。要调用 CreateDSNConnection 函数，请在 AutoExec 宏或在启动窗体 Form_Open 事件中添加类似于以下代码示例之一的代码： 在使用 AutoExec 宏时，调用 CreateDSNConnection 函数，然后从 RunCode 操作传递类似于以下内容的参数。CreateDSNConnection ("(local)", "pubs", "", "") 使用启动窗体时，将类似于以下内容的代码添加到 Form_Open 事件。Private Sub Form_Open(Cancel As Integer) If CreateDSNConnection("(local)", "pubs", "", "") Then '// All is okay. Else '// Not okay. End If End Sub注意：此方法假定您通过将“myDSN”用作 DSN 名称已在 Access 数据库中创建了 SQL Server 链接表。回到顶端 | 提供反馈