方法 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 IfEnd 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 passwordFunction CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean 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 FunctionCreateDSNConnection_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 IfEnd Sub注意 此方法假定通过使用 " myDSN " 作为 DSN 名称， 您已经创建链接 SQLServer 表 Access 数据库中。
请 CreateTableDef 方法, 有关访问下列 Microsoft Developer Network (MSDN) Web 站点： http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/output/F1/D2/S5A289.asp (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/output/F1/D2/S5A289.asp)有关 RegisterDatabase 方法, 请访问以下 MSDNWeb 站点： http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/output/F1/D2/S5A2EA.asp (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/output/F1/D2/S5A2EA.asp)
11
22
33
