设为首页收藏本站Access中国

Office中国论坛/Access中国论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

返回列表 发新帖
查看: 11305|回复: 9

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

[复制链接]

点击这里给我发消息

发表于 2012-5-6 10:10:01 | 显示全部楼层 |阅读模式
微软 KB:如何将 Microsoft Access 窗体绑定到 ADO 记录集

本文介绍什么所需创建一个可更新的窗体,它绑定到 ActiveX 数据对象 (ADO) 记录集 对象。

若要将 Microsoft Access 窗体绑定到记录集,您必须是有效的数据访问对象 (DAO) 或 ADO 记录集 对象来设置窗体的 Recordset 属性。

若要将 Microsoft Access 窗体绑定到记录集,您必须是有效的数据访问对象 (DAO) 或 ADO 记录集 对象来设置窗体的 Recordset 属性。

Recordset 属性在 Microsoft Access 2000 中, 引入的它使您可以将窗体绑定到 DAO 或 ADO 记录集 对象。但是,在 Access 2000 中的窗体支持更新,仅当 ADO 连接通过使用 MSDataShape 和 SQL Server OLEDB 提供程序打开。

在 Microsoft Access 2002 或更高版本中,您可以创建一个可更新的窗体绑定到 ADO 记录集使用其他 OLEDB 提供程序。窗体必须满足几个常规要求时它绑定到 ADO 记录集是可更新窗体。这些常规的要求如下所示: 1.基本的 ADO 记录集必须可更新。
2.记录集必须包含一个或多个唯一索引的如表的主键字段。
在其他要求的更新不同的提供程序之间变化。本文介绍当您使用 Microsoft SQL Server、 Jet、 ODBC,和 Oracle OLEDB 提供程序是什么其他要求。

建议查看英文原文:http://support.microsoft.com/kb/281998/en-us

本帖被以下淘专辑推荐:

点击这里给我发消息

 楼主| 发表于 2012-5-6 10:11:33 | 显示全部楼层
To bind a Microsoft Access form to a recordset, you must set the Recordset property of the form to a valid Data Access Objects (DAO) or ADO Recordset object.

The Recordset property was introduced in Microsoft Access 2000, and it allows you to bind forms to either DAO or ADO Recordset objects. However, forms in Access 2000 support updateability only if the ADO connection is opened by using the MSDataShape and SQL Server OLEDB providers. For additional information about this limitation in Access 2000, click the article number below to view the article in the Microsoft Knowledge Base:
227053  ACC2000: Forms Based on ADO Recordsets Are Read-Only
In Microsoft Access 2002 or later, you can create an updateable form that is bound to an ADO recordset that uses other OLEDB providers. A form must meet several general requirements for the form to be updateable when it is bound to an ADO recordset. These general requirements are: 1.The underlying ADO recordset must be updateable.
2.The recordset must contain one or more fields that are uniquely indexed, such as a table's primary key.
The other requirements for updateability vary between different providers. This article describes what the other requirements are when you use the Microsoft SQL Server, Jet, ODBC, and Oracle OLEDB providers.


Requirements for Microsoft SQL Server
There are two main requirements for supporting updateability when you bind a form to an ADO recordset that is using Microsoft SQL Server data:
•The ADO recordset's connection must use the Microsoft Access 10.0 OLEDB provider as its service provider.
•The ADO recordset's connection must use the Microsoft SQL Server OLEDB provider as its data provider.
NOTE: The Microsoft Access 10.0 OLEDB provider is an OLEDB service provider that was written specifically for use in Microsoft Access. It was not designed to be used in applications other than Microsoft Access, and it is not supported in other applications.

When you create ADO recordsets within Microsoft Access, you have a choice as to which ADO connection will be used by the recordset. Your ADO code can share the ADO connection that Microsoft Access is using for the SQL Server database currently open in an Access project (ADP) file; or you can programmatically create a new ADO connection to a different SQL Server database.
Sharing the ADO Connection Used by Microsoft Access
If you are using an Access project file (.adp) connected to a Microsoft SQL Server database, it is possible for your ADO code to share the ADO connection that Microsoft Access is using. This connection is exposed by the CurrentProject.AccessConnection property.

The following example demonstrates how to bind a form to an ADO recordset that is based on SQL Server data that shares an ADO connection with Microsoft Access. 1.Open the sample project NorthwindCS.adp.
2.Open the Customers form in Design view.
3.Clear the RecordSource property of the form to unbind the form.
4.Set the OnOpen property of the form to the following event procedure:
  1. Private Sub Form_Open(Cancel As Integer)
  2.    Dim cn As ADODB.Connection
  3.    Dim rs As ADODB.Recordset
  4.          
  5.    'Use the ADO connection that Access uses
  6.    Set cn = CurrentProject.AccessConnection

  7.    'Create an instance of the ADO Recordset class, and
  8.    'set its properties
  9.    Set rs = New ADODB.Recordset
  10.    With rs
  11.       Set .ActiveConnection = cn
  12.       .Source = "SELECT * FROM Customers"
  13.       .LockType = adLockOptimistic
  14.       .CursorType = adOpenKeyset
  15.       .Open
  16.    End With
  17.    
  18.    'Set the form's Recordset property to the ADO recordset
  19.    Set Me.Recordset = rs

  20.    Set rs = Nothing
  21.    Set cn = Nothing
  22. End Sub
复制代码
5.Save and then close the form.
6.Open the Customers form in Form view.
7.Add, edit, or delete a record in the form.
Note that the form is bound to an updateable recordset that is based on SQL Server data.
Opening a Separate ADO Connection
At some point, you may have to open and manage your own ADO connection to SQL Server. For example, you would have to use this approach if you were writing your code in an Access database (.mdb) or an Access project file (.adp) that was connected to a different SQL Server database than your application. Note that when you use this approach, Microsoft recommends that you close the ADO connection that you opened when it is no longer needed. For example, you may want to close the ADO connection in the UnLoad event of the form.

The following example demonstrates how to open your own ADO connection to a Microsoft SQL Server database and to bind a form to it:1.Open the sample database Northwind.mdb.
2.Open the Customers form in Design view.
3.Clear the RecordSource property of the form to unbind the form.
4.Set the OnOpen property of the form to the following event procedure:
  1. Private Sub Form_Open(Cancel As Integer)
  2.    Dim cn As ADODB.Connection
  3.    Dim rs As ADODB.Recordset
  4.          
  5.    'Create a new ADO Connection object
  6.    Set cn = New ADODB.Connection

  7.    'Use the Access 10 and SQL Server OLEDB providers to
  8.    'open the Connection
  9.    'You will need to replace MySQLServer with the name
  10.    'of a valid SQL Server
  11.    With cn
  12.       .Provider = "Microsoft.Access.OLEDB.10.0"
  13.       .Properties("Data Provider").Value = "SQLOLEDB"
  14.       .Properties("Data Source").Value = "MySQLServer"
  15.       .Properties("User ID").Value = "sa"
  16.       .Properties("Password").Value = ""
  17.       .Properties("Initial Catalog").Value = "NorthwindCS"
  18.       .Open
  19.    End With

  20.    'Create an instance of the ADO Recordset class, and
  21.    'set its properties
  22.    Set rs = New ADODB.Recordset
  23.    With rs
  24.       Set .ActiveConnection = cn
  25.       .Source = "SELECT * FROM Customers"
  26.       .LockType = adLockOptimistic
  27.       .CursorType = adOpenKeyset
  28.       .Open
  29.    End With
  30.    
  31.    'Set the form's Recordset property to the ADO recordset
  32.    Set Me.Recordset = rs
  33.    Set rs = Nothing
  34.    Set cn = Nothing
  35. End Sub
复制代码
5.Add the following code to the UnLoad event of the form:
  1. Private Sub Form_Unload(Cancel As Integer)
  2.    'Close the ADO connection we opened
  3.    Dim cn As ADODB.Connection
  4.    Set cn = Me.Recordset.ActiveConnection
  5.    cn.Close
  6.    Set cn = Nothing
  7. End Sub
复制代码
6.Save the form, and then close it.
7.Open the Customers form in Form view.
8.Add, edit, or delete a record in the form.
Note that the form is bound to an updateable recordset that is based on SQL Server data.


点击这里给我发消息

 楼主| 发表于 2012-5-6 10:11:54 | 显示全部楼层
Requirements for Microsoft Jet
Even though it is possible to bind a form to an ADO recordset that is using data from a Jet database, Microsoft recommends that you use DAO instead. DAO is highly optimized for Jet and typically performs faster than ADO when used with a Jet database.

When you bind a form to an ADO recordset using Microsoft Jet data, there are two alternatives: •The recordset's ActiveConnection property must use the Microsoft Access 10.0 OLEDB service provider, as well as the Microsoft Jet 4.0 OLEDB Data provider and the recordset must be a server-side cursor.

-or-
•The recordset's ActiveConnection property must use only Microsoft Jet 4.0 OLEDB Data provider and the recordset must be a client-side cursor.
Similar to the "Microsoft SQL Server" section earlier in this article, you have a choice as to which ADO connection will be used by the recordset when using a Jet database. Your ADO code can share the ADO connection that Microsoft Access is using for the Jet database file (.mdb) currently open, or you can programmatically create a new ADO connection to a separate Jet database file.
Sharing the ADO Connection Used by Microsoft Access
If you are writing the code in the same Microsoft Access database (.mdb) that contains the data that your recordset needs, it is possible for your ADO code to share the ADO connection that Microsoft Access is using. This connection is exposed by the CurrentProject.AccessConnection property. The following example demonstrates how to bind a form to an ADO recordset in a Jet database by sharing the ADO connection that Microsoft Access is currently using: 1.Open the sample database Northwind.mdb.
2.Open the Customers form in Design view.
3.Clear the RecordSource property of the form to unbind the form.
4.Set the OnOpen property of the form to the following event procedure:
  1. Private Sub Form_Open(Cancel As Integer)
  2.    Dim cn As ADODB.Connection
  3.    Dim rs As ADODB.Recordset
  4.                
  5.    Set cn = CurrentProject.AccessConnection

  6.    'Create an instance of the ADO Recordset class, and
  7.    'set its properties
  8.    Set rs = New ADODB.Recordset
  9.    With rs
  10.       Set .ActiveConnection = cn
  11.       .Source = "SELECT * FROM Customers"
  12.       .LockType = adLockOptimistic
  13.       .CursorType = adOpenKeyset
  14.       .CursorLocation = adUseServer
  15.       .Open
  16.    End With
  17.    
  18.    'Set the form's Recordset property to the ADO recordset
  19.    Set Me.Recordset = rs
  20.    Set rs = Nothing
  21.    Set cn = Nothing
  22. End Sub
复制代码
5.Save the form, and then close it.
6.Open the Customers form in Form view.
7.Add, edit, or delete a record in the form.
Note that the form is bound to an updateable recordset that is using Jet data.

Opening a Separate ADO Connection
At some point, you may have to open and manage your own ADO connection to a Jet database. For example, you would have to use this approach if you are writing your code in a database that is separate from the database that contains the data that you need to access. Note that when you use this approach, Microsoft recommends that you close the ADO connection that you opened when it is no longer needed. For example, you may want to close the ADO connection in the UnLoad event of the form.

The following example demonstrates how to open your own ADO connection to a Microsoft Jet database and to bind a form to it:1.Create a new blank database.
2.Import the Customers form from the sample database Northwind.mdb.
3.Open the Customers form in Design view.
4.Clear the RecordSource property of the form to unbind form.
5.Set the OnOpen property of the form to the following event procedure:
  1. Private Sub Form_Open(Cancel As Integer)
  2.    Dim cn As ADODB.Connection
  3.    Dim rs As ADODB.Recordset
  4.          
  5.    'Create a new ADO Connection object
  6.    Set cn = New ADODB.Connection

  7.    With cn
  8.       .Provider = "Microsoft.Access.OLEDB.10.0"
  9.       .Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
  10.       .Properties("Data Source").Value = _
  11.           "C:\Program Files\Microsoft Office\Office10" & _
  12.           "\Samples\Northwind.mdb"
  13.       .Open
  14.    End With

  15.    'Create an instance of the ADO Recordset class, and
  16.    'set its properties
  17.    Set rs = New ADODB.Recordset
  18.    With rs
  19.       Set .ActiveConnection = cn
  20.       .Source = "SELECT * FROM Customers"
  21.       .LockType = adLockOptimistic
  22.       .CursorType = adOpenKeyset
  23.       .Open
  24.    End With
  25.    
  26.    'Set the form's Recordset property to the ADO recordset
  27.    Set Me.Recordset = rs
  28.    Set rs = Nothing
  29.    Set cn = Nothing
  30. End Sub
复制代码
6.Add the following code to the UnLoad event of the form:
  1. Private Sub Form_Unload(Cancel As Integer)
  2.    'Close the ADO connection we opened
  3.    Dim cn As ADODB.Connection
  4.    Set cn = Me.Recordset.ActiveConnection
  5.    cn.Close
  6.    Set cn = Nothing
  7. End Sub
复制代码
7.Save the form, and then close it.
8.Open the Customers form in Form view.
9.Add, edit, or delete a record in the form.
Note that the form is bound to an updateable recordset that is using Jet data.


Requirements for ODBC
When you bind a form to an ADO recordset that is using data from an ODBC database, there are two main requirements: •The ADO connection that is used by the recordset must use the Microsoft OLEDB provider for ODBC.
•The ADO recordset must be a client-side cursor.
The following example demonstrates how to open an ADO connection to an ODBC database and to bind a form to it.

NOTE: These steps assume that the ODBC database contains a table named CUSTOMERS that is identical in structure to the Customers table in the sample database Northwind.mdb. It also assumes you have created an ODBC DSN named MyDSN that uses the ODBC driver that you need to connect to the back-end database. 1.Open the sample database Northwind.mdb.
2.Open the Customers form in Design view.
3.Clear the RecordSource property of the form to unbind form.
4.Set the OnOpen property of the form to the following event procedure:
  1. Private Sub Form_Open(Cancel As Integer)
  2.    Dim cn As ADODB.Connection
  3.    Dim rs As ADODB.Recordset
  4.    Dim strConnection As String

  5.    strConnection = "ODBC;DSN=MyDSN;UID=sa;PWD=;DATABASE=Northwind"
  6.    'Create a new ADO Connection object
  7.    Set cn = New ADODB.Connection

  8.    With cn
  9.       .Provider = "MSDASQL"
  10.       .Properties("Data Source").Value = strConnection
  11.       .Open
  12.    End With

  13.    'Create an instance of the ADO Recordset class, and
  14.    'set its properties
  15.    Set rs = New ADODB.Recordset
  16.    With rs
  17.       Set .ActiveConnection = cn
  18.       .Source = "SELECT * FROM Customers"
  19.       .LockType = adLockOptimistic
  20.       .CursorType = adOpenKeyset
  21.       .CursorLocation = adUseClient
  22.       .Open
  23.    End With
  24.    
  25.    'Set the form's Recordset property to the ADO recordset
  26.    Set Me.Recordset = rs
  27.    Set rs = Nothing
  28.    Set cn = Nothing
  29. End Sub
复制代码
5.Add the following code to the UnLoad event of the form:
  1. Private Sub Form_Unload(Cancel As Integer)
  2.    'Close the ADO connection we opened
  3.    Dim cn As ADODB.Connection
  4.    Set cn = Me.Recordset.ActiveConnection
  5.    cn.Close
  6.    Set cn = Nothing
  7. End Sub
复制代码
6.Save the form, and then close it.
7.Open the Customers form in Form view.
8.Add, edit, or delete a record in the form.
Note that the form is bound to an updateable recordset that is based on ODBC data.


Requirements for Oracle
When you bind a form to an ADO recordset that is using data from an Oracle database, there are two main requirements:•The ADO connection that is used by the recordset must use the Microsoft OLEDB provider for Oracle.
•The ADO Recordset must be a client-side cursor.
The following example demonstrates how to open an ADO connection to an Oracle database and to bind a form to it.

NOTE: These steps assume that the Oracle database contains a table named CUSTOMERS that is identical in structure to the Customers table in the sample database Northwind.mdb. 1.Open the sample database Northwind.mdb.
2.Open the Customers form in Design view.
3.Clear the RecordSource property of the form to unbind the form.
4.Set the OnOpen property of the form to the following event procedure:
  1. Private Sub Form_Open(Cancel As Integer)
  2.    Dim cn As ADODB.Connection
  3.    Dim rs As ADODB.Recordset
  4.          
  5.    'Create a new ADO Connection object
  6.    Set cn = New ADODB.Connection

  7.    With cn
  8.       .Provider = "MSDAORA"
  9.       .Properties("Data Source").Value = "MyOracleServer"
  10.       .Properties("User ID").Value = "username"
  11.       .Properties("Password").Value = "password"         
  12.       .Open
  13.    End With

  14.    'Create an instance of the ADO Recordset class, and
  15.    'set its properties
  16.    Set rs = New ADODB.Recordset
  17.    With rs
  18.       Set .ActiveConnection = cn
  19.       .Source = "SELECT * FROM Customers"
  20.       .LockType = adLockOptimistic
  21.       .CursorType = adOpenKeyset
  22.       .CursorLocation = adUseClient

  23.       .Open
  24.    End With
  25.   
  26.    'Set the form's Recordset property to the ADO recordset
  27.    Set Me.Recordset = rs
  28.    Set rs = Nothing
  29.    Set cn = Nothing
  30. End Sub
复制代码
5.Add the following code to the UnLoad event of the form:
  1. Private Sub Form_Unload(Cancel As Integer)
  2.    'Close the ADO connection we opened
  3.    Dim cn As ADODB.Connection
  4.    Set cn = Me.Recordset.ActiveConnection
  5.    cn.Close
  6.    Set cn = Nothing
  7. End Sub
复制代码
6.Save the form, and then close it.
7.Open the Customers form in Form view.
8.Add, edit, or delete a record in the form.
Note that the form is bound to an updateable recordset that is based on Oracle data.

点击这里给我发消息

发表于 2012-5-7 00:11:44 | 显示全部楼层
谢谢分享,内容不错
发表于 2012-6-5 10:09:05 | 显示全部楼层
值得熟悉

点击这里给我发消息

发表于 2012-7-23 09:38:34 | 显示全部楼层
{:soso_e134:}
发表于 2012-8-28 11:23:10 | 显示全部楼层
这么搞安全多了,就是开发起来没那么直观~
发表于 2016-2-29 14:55:41 | 显示全部楼层
xuexi
回复

使用道具 举报

发表于 2016-3-16 20:49:49 | 显示全部楼层
不错,没有想到朱老师的英文这么好, 太崇拜了!

点击这里给我发消息

发表于 2016-9-23 15:06:20 | 显示全部楼层
慢慢学习,英文不好啊。
您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|站长邮箱|小黑屋|手机版|Office中国/Access中国 ( 粤ICP备10043721号-1 )  

GMT+8, 2024-3-28 22:19 , Processed in 0.105875 second(s), 40 queries .

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表