|
3#
楼主 |
发表于 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:- Private Sub Form_Open(Cancel As Integer)
- Dim cn As ADODB.Connection
- Dim rs As ADODB.Recordset
-
- Set cn = CurrentProject.AccessConnection
- '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
- .CursorLocation = adUseServer
- .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.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:- 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
- With cn
- .Provider = "Microsoft.Access.OLEDB.10.0"
- .Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
- .Properties("Data Source").Value = _
- "C:\Program Files\Microsoft Office\Office10" & _
- "\Samples\Northwind.mdb"
- .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
复制代码 6.Add the following code to the UnLoad event of the form:- 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
复制代码 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:- Private Sub Form_Open(Cancel As Integer)
- Dim cn As ADODB.Connection
- Dim rs As ADODB.Recordset
- Dim strConnection As String
- strConnection = "ODBC;DSN=MyDSN;UID=sa;PWD=;DATABASE=Northwind"
- 'Create a new ADO Connection object
- Set cn = New ADODB.Connection
- With cn
- .Provider = "MSDASQL"
- .Properties("Data Source").Value = strConnection
- .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
- .CursorLocation = adUseClient
- .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.Add the following code to the UnLoad event of the form:- 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.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:- 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
- With cn
- .Provider = "MSDAORA"
- .Properties("Data Source").Value = "MyOracleServer"
- .Properties("User ID").Value = "username"
- .Properties("Password").Value = "password"
- .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
- .CursorLocation = adUseClient
- .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.Add the following code to the UnLoad event of the form:- 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.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. |
|