如何将 Microsoft Access 窗体绑定到 ADO 记录集

时　间:2016-12-14 23:30:19
作　者:gaoyunpeng   ID：49516  城市：大连
摘　要:将ADO记录集直接写入窗体中，完成查询功能。
正　文:

打开单独的 ADO 连接

1. 打开示例数据库 Northwind.mdb。
2. 在设计视图中打开客户窗体。
3. 清除要取消绑定窗体的窗体的记录源属性。
4. 将OnOpen属性的窗体设置为以下事件过程︰

   Private Sub Form_Open(Cancel As Integer)
      Dim cn As ADODB.Connection
      Dim rs As ADODB.Recordset
            
      'Create a new ADO Connection object
      Set cn = New ADODB.Connection
   
      'Use the Access 10 and SQL Server OLEDB providers to
      'open the Connection
      'You will need to replace MySQLServer with the name
      'of a valid SQL Server
      With cn
         .Provider = "Microsoft.Access.OLEDB.10.0"
         .Properties("Data Provider").Value = "SQLOLEDB"
         .Properties("Data Source").Value = "MySQLServer"
         .Properties("User ID").Value = "sa"
         .Properties("Password").Value = ""
         .Properties("Initial Catalog").Value = "NorthwindCS"
         .Open
      End With
   
      'Create an instance of the ADO Recordset class, and
      'set its properties
      Set rs = New ADODB.Recordset
      With rs
         Set .ActiveConnection = cn
         .Source = "Select * FROM Customers"
         .LockType = adLockOptimistic
         .CursorType = adOpenKeyset
         .Open 
      End With
      
      'Set the form's Recordset property to the ADO recordset
      Set Me.Recordset = rs
      Set rs = Nothing
      Set cn = Nothing
   End Sub

5. 将以下代码添加到窗体的 UnLoad 事件︰

   Private Sub Form_Unload(Cancel As Integer)
      'Close the ADO connection we opened
      Dim cn As ADODB.Connection
      Set cn = Me.Recordset.ActiveConnection
      cn.Close
      Set cn = Nothing
   End Sub

6. 保存该窗体，然后关闭它。
7. 在窗体视图中打开客户窗体。
8. 添加、 编辑或删除窗体中的记录。

请注意，窗体绑定到可更新记录集基于 SQL Server 数据。

资料来源：

https://support.microsoft.com/zh-cn/kb/281998