The VBA Database Solution Tutorial (Copyright 10090845) is the second set of tutorials I have launched, and it is currently in its second edition of revisions. This set of tutorials is positioned at the intermediate level, and it is another special topic after learning the dictionary. Database is a powerful tool for data processing, the tutorial introduces in detail the method and example operation of using ADO to connect ACCD and Excel, and the revision content of the first version of the tutorial is mainly to complete the 32-bit and 64-bit Office system testing of all program files.
This set of tutorials consists of two volumes, 84 lectures, and the revised tutorial content will be introduced to you in the future. Today's content is: VBA Database Solution Lecture 7: How to Use RecordSet Objects to Open the Data Record Set of the Database.
Share the results and rejoice in positive energy] Don't take other people's kindness to you for granted. Just because others don't need you to thank doesn't mean you don't need to be grateful.
Lecture 7 How to use the recordset object to open the data record set of the database.
In our previous lecture, we talked about some of the properties, methods, and events of the RecordSet object. Today, we will continue to explain the relevant knowledge of recordset objects, how to use recordset objects to open the collection of data records in the database.
Before explaining, we also have to add some necessary knowledge points:
1RecordSet object.
1.1 The recordset object can support two types of updates.
Update immediately, as soon as the update method is called, all changes are immediately written to the database.
Batch updates, the provider caches multiple changes and then uses the updatebatch method to propagate those changes to the database.
1.2 In addition, when accessing records, ADO defines 4 different types of cursors (pointers).
Dynamic Cursor : Allows you to see additions, changes, and deletions made by other users.
Keyset Cursor: Similar to a dynamic cursor, except that you can't see additions made by other users, and it prevents you from accessing records that have been deleted by other users. Data changes made by other users are still visible.
Static Cursor: Provides a static copy of a recordset that can be used to find data or generate reports. In addition, additions, changes, and deletions made by other users will be invisible. When you open a client-side recordset object, this is the only type of cursor that is allowed.
Forward Cursor Only: Behaves like a static cursor, except that it is only allowed to scroll forward in the record. This improves performance when a one-way move in a recordset is required.
1.3. Why are these four cursor types designed?
This is the good intention of building a database, these four types are different types of customer access to the database, or different permissions, some are fully owned access permissions, any access can be enjoyed, and some only have partial permissions, which depends on the nature of the user to determine the permissions you enjoy. Of course, this is not very closely related to VBA, but when it comes to databases, I will talk about it with you by the way.
2 The interconnection between the three objects of ADO.
2.1 The command object and the recordset object depend on the connection object
2.The 2Command object can replace the RecordSet object in combination with SQL commands, but it is far less flexible and practical than the RecordSet object
2.3RecordSet object, which can only implement recordset operations in a data table. Recordset: A specific set of records that is returned when a query operation is performed on a table.
3. Open a recordset.
It must be clear that before opening the recordset, you need to connect to the database, please refer to the previous lecture for connection
dim cnado as new adodb.connection
dim strpath as string
strpath = thisworkbook.path & "\mydata.accdb"
cnado.open "provider=microsoft.ace.oledb.12.0;data source=" & strpath
Before opening a recordset, you need to declare a recordset object and create a recordset object.
dim rszys as adodb.recordset
set rszys = new adodb.recordset
rszys.open "Staff Table", cnado, 1, 3
msgbox rszys.recordcount
rszys.close
cnado.close
set rszys = nothing
set cnado = nothing
All**: omitted.
*Screenshot:
*Interpretation: rszysopen "Staff Table", cnado, 1, 3 or above** is to open the recordset,.
msgbox rszys.The RecordCount dialog box pops up to prompt the number of staff members.
rszys.close
cnado.close
Close the connection between the recordset and the database above, note that the recordset should be closed first, and then the connection should be closed, and the order of opening is reversed.
set rszys = nothing
set cnado = nothing
The above ** frees up memory, and the number of records will pop up after running: (the number of records in the database).
The content of this section is that we are beginning to enter the internal operation of the database, everyone should not be in a hurry, but take your time, my explanation of the database is step by step.
Today's content is returned:
1 How many ways can the recordset object be updated?
2 recordset object How do I open a recordset?
This lecture is covered by the program file: VBA and Database Operations (Volume 1).xlsm
My more than 20 years of practical experience in VBA is condensed in the following tutorials: