How to use VBA to get the correct number of records in a ADO recordset object.Use VBA to get the correct number of records in a Recordset object.IC285554.jpg' alt='Microsoft Access Vba Recordset Record Countdown' title='Microsoft Access Vba Recordset Record Countdown' />Does your Recordset return 1 for its Record.Count property If yes, this article will be able to help you and explain.If you have used Microsoft ADO.VBA project, you must have used Recordset object.Recordset is just a temporary table in memory that contains.You can navigate through the recordset and do whatever processing you are legit to do.Recordset object has a Record.Microsoft Access Vba Recordset Record Count Sql' title='Microsoft Access Vba Recordset Record Count Sql' />Count property.Recordset. When I first used ADO in 1.Record. Count always returned 1.After many tests and digs into the Help document, I.I used for the record set.By default, ADO implicitly creates a forward only cursor for a recordset and returns 1 in the Record.Count property. when one of the following methods is used.Microsoft Access Vba Recordset Record Count In SqlMicrosoft Access Query Tips and Techniques SQL and VBA by Luke Chung, President of FMS, Inc.This paper is featured on Overview.Microsoft Access is the most.When opening the recordset object by using the Open method of the Recordset object if no cursor.When obtaining the Recordset object by using the Execute method of the Connection object.This method. does not give us the option to specify cursor type.The first two code examples below demonstrate how to create recordset with forward only cursor.Then two more. examples are given to show how to create recordsets that return the valid record count.To get the code on this page to work, you need to add a reference to Microsoft Active.X Data Objects Library.Northwind database.In VBA editor, click Tools References.How to create Recordset that returns 1 on the Record.Count property. The following two code snippets implicitly create forward only cursor and will return 1 on the Record.Count property. To see how the code below works, in Access Northwind database, create a form with two buttons and name them as cmd.Cnn. Execute and cmd.Rst. Open. Copy the.On Click event procedure.Use Execute method of the Connection object Record.Count returns 1. The Execute method returns a recordset with forward only cursor type.Private. Sub cmd.Cnn. ExecuteClick.On. Error. Goto Catch.Dim str. Sql As. String.Dim obj. Cnn As ADODB.Connection. Dim obj.Rst As ADODB. Recordset.Get all categories.Sql select Category.ID, Category. Name from Categories.Set obj. Cnn Current.Project. Connection.Set obj. Rst New ADODB.Recordset. This implicitly creates a recordset object with forward only cursor by default.Set obj. Rst obj.Cnn. Executestr. Sql.Display the number of records in Immediate window.Returns 1. Debug.Print obj. Rst ad. Back Office Software For Ruby Verifone Help on this page. Open. Forward. Only record count obj.Rst. Record. Count.Clean up. Set obj.Cnn Nothing. obj.Rst. Close. Set obj.Rst Nothing. Exit.Sub. Msg. Box cmd.Record. CountClick vb.Cr. Lf vb. Cr. Lf.Error Err. Number vb.Cr. Lf vb. Cr. Lf Err.Description. 2 Use Open method of the Recordset object with default cursor type Record.Count returns 1. In this example, the Open method creates a recordset of a forward only cursor type.This is because the cursor.Open. Forward. Only is used.Private. Sub cmd.Rst. OpenClick. On.Error. Goto Catch.Dim str. Sql As. String.Dim obj. Rst As ADODB.Recordset. Get all categories.Sql select Category.ID, Category. Name from Categories.Set obj. Rst New ADODB.Recordset. This creates a recordset object with forward only cursor by default because we didnt specify the cursor type enum.Rst. Open str. Sql, Current.Project. Connection.Display the number of records in Immediate window.Returns 1. Debug.Print obj. Rst ad.Open. Forward. Only record count obj.Rst. Record. Count.Clean up. obj. Rst.Close. Set obj. Rst Nothing.Exit. Sub. Msg. Box cmd.Record. CountClick vb.Cr. Lf vb. Cr. Lf.Error Err. Number vb.Cr. Lf vb. Cr. Lf Err.Description. End.Sub. The ways to obtain a valid count.Now were going to look at how to get the correct count by using Record.Count property. 1 Client side cursor.When a recordsets cursor location is specified as client side, it always returns the valid count for Record.Count property. Client side cursor is specified in VBA as below.Rst. Cursor. Location ad.Use. Client. When a recordset is client side, after its retrieved from the database server eg.Oracle, My. SQL database server, etc.PC for MS Access application, or web server for an internet application and.Any changes in the database after the retrieval.Please note that, for local Access database, the server and client are on the same machine because server is your.Access Jet engine and client is your VBA engine.Server side Static or Keyset cursor.When a recordset is opened, by default its opened as a server side, forward only cursor.If we change its.Record. Count property.The following code opens a static cursor.Rst. Cursor. Type ad.Open. Static. Or specify ad.Open. Static in the Open method directly.Rst. Open str. Sql, Current.Project. Connection, ad.Open. Static. The following code opens a keyset cursor.Rst. Cursor. Type ad.Open. Keyset. Or specify ad.Open. Keyset in the Open method directly.Rst. Open str. Sql, Current.Project. Connection, ad.Open. Keyset. To understand more about the pros and cons for client side and server side cursor, refer to Microsoft article.Client Side Cursors Versus Server Side Cursors.Example of client side cursor that returns actual count.Code below shows how a client side cursor returns the actual record count.In Northwind database, on the form you are.Record. Count. Client.Side, then copy the code below in its On Click event.Private. Sub cmd.Record. Count. Client.SideClick. On. Error.Goto Catch. Dim str.Sql As. String. Dim obj.Rst As ADODB. Recordset.Get all categories from local Northwind database.Sql select Category.ID, Category. Name from Categories.Set obj. Rst New ADODB.Recordset. Client side cursor.Rst. Cursor. Location ad.Use. Client. obj.Rst. Open str. Sql, Current.Project. Connection.Returns the actual count 8 recordsDebug.Print Client side obj.Rst record count obj.Rst. Record. Count.Clean up. obj. Rst.Close. Set obj. Rst Nothing.Exit. Sub. Msg. Box cmd.Record. CountClick vb.Cr. Lf vb. Cr. Lf.Error Err. Number vb.Cr. Lf vb. Cr. Lf Err.Description. Immediate Window shows the actual count for client side cursor.Example of all four server side cursor types.Code below shows four recordsets with four different cursor types.Static and keyset cursor return the actual count.Forward only. and dynamic cursor return 1.Because we didnt specify cursor location, it defaults to server side.On the other hand.Access Northwind database, the client side and server side are both on the.In Northwind database, on the form you are using for testing, create a new button named.Record. Count, then copy the code below in its On Click event.Private. Sub cmd.Record. CountClick.On. Error. Goto Catch.Dim str. Sql As. String.Dim obj. Rst. 1 As ADODB.Recordset. Dim obj.Rst. 2 As ADODB. Recordset.Dim obj. Rst. 3 As ADODB.Recordset. Dim obj.Rst. 4 As ADODB. Recordset.Get all categories from local Northwind database.Sql select Category.ID, Category. Name from Categories.In code below, because we didnt specify cursor location, its server side by default.Returns 1. Set obj.Rst. 1 New ADODB.Recordset. obj. Rst.Open str. Sql, Current.Project. Connection, ad.Open. Forward. Only.Returns 1. Set obj.Rst. 2 New ADODB.Recordset. obj. Rst.Open str. Sql, Current.Project. Connection, ad.Open. Dynamic. Returns valid count.Set obj. Rst. 3 New ADODB.Recordset. obj. Rst.Open str. Sql, Current.Project. Connection, ad.Open. Keyset. Returns valid count.Set obj. Rst. 4 New ADODB.Recordset. obj. Rst.Open str. Sql, Current.Project. Connection, ad.Open. Static. Display the number of records in Immediate window.Debug. Print obj.Rst. 1 ad. Open. Forward.Only record count obj.Rst. 1. Record. Count.Debug. Print obj.Rst. 2 ad. Open. Dynamic record count obj.Rst. 2. Record. Count.Debug. Print obj.Rst. 3 ad. Open. Keyset record count obj.Rst. 3. Record. Count.Debug. Print obj.Rst. 4 ad. Open. Static record count obj.Rst. 4. Record. Count.Clean up. obj. Rst.Close. obj. Rst. 2.Close. obj. Rst. 3.Close. obj. Rst. 4.Close. Set obj. Rst.Nothing. Set obj.Rst. 2 Nothing. Set obj.Rst. 3 Nothing. Set obj.Rst. 4 Nothing. VBA Recordset objects.As an Access developer or even as a power user you wont get far if you cant work with the Recordset object.But its easy to go astray, and there are a number of mistakes that even experts make.Sometimes, were just careless.Often, were unaware of a subtle nuance between the two object libraries, Data Access Objects DAO and Active.X Data Objects ADO.No matter how experienced you are, its difficult to commit every little behavior and requirement to memory.Luckily, you can avoid some of the most common mistakes if you just plan for them.Note This information is also available as a PDF download.Eliminate ambiguous objects.ADO is the default object library for Access, but DAO is still available.If you use a DAO Recordset, you must explicitly reference it.Referencing both libraries is likely to generate an ambiguous object error.To avoid this type of error, give priority to the library that your code references the most.In the Visual Basic Editor, choose References from the Tools menu, highlight the main library, and click the up button to position it above the other object library.ADO is the main object library is this project.When both libraries are referenced and both libraries support an object, VBA will assign the library with the highest priority.Usurp implicit library references.Giving the main library priority is good 1.Explicitly referencing each objects library when you declare the object is better Dim rst As DAO.Recordset. Dim rst As ADODB.Recordset. Combine 1 with 2 for the best results.Avoid moving violations.Moving through the records in a Recordset is a common task, but doing so comes with a few hazards.Using any move method will generate an error if the Recordset object is empty.The most reliable check uses a simple If statement before executing a move method If Notrst.BOF And rst. EOF Then.The BOF property returns True when the current position is before the first record.Similarly, EOF returns True when the current position is just beyond the last record.If both properties are True simultaneously, the Recordset is empty.Tip Including the Not operator is a matter of efficiency.If you expect the Recordset to contain records most of the time, its more efficient to include Not in the check.Expose an erroneous record count.The previous technique uses BOF and EOF to expose an empty Recordset before code can generate an error.Another way to avoid this type of error is to use the Record.Count property to count the records as follows If rst.Record. Count lt 0 Then.This statement is a bit unpredictable with ADO because some ADO Recordset objects return 1 as the count.Use a static or keyset cursor to return a true record count in an ADO Recordset.When using DAO, you must remember to populate fully the Recordset before counting, as follows rst.Move. Firstrst. Move.Last. If rst. Record.Count lt 0 Then. DAO populates with records as they are needed, and consequently, its Record.Count property considers only the records already accessed.ADO, on the other hand, defines the Record.Count property as the total number of records, period.Thats why this second check for an empty Recordset is less flexible and can be troublesome.Whether using Record.Count to check for the existence of records or to count records, you must remember the differences between DAO and ADO When using DAO, populate fully the Recordset before checking for an empty Recordset or a total count.When using ADO, use a static or keyset cursor to return the actual record count.Tip Dont use Move.Last with a large Recordset to check for an empty Recordset.Instead, use Move.Next. If Record. Count returns 1, you know the Recordset isnt empty.You cant move beyond the first or last record.In tip 3, I told you to use the BOF and EOF properties to test for an empty Recordset.Youll also use them individually when moving through records.Moving beyond the first or last record returns an error.For instance, the following code returns an error when the Move.Next method moves beyond the last record If rstfieldname value Then.Move. Next. If youre moving forward, use EOF to inhibit the error that occurs when you move beyond the last record, as follows Do While Not rst.EOF. If rstfieldname value Then.End Ifrst. Move. Next.Loop. As long as EOF equals False, meaning the current position is a valid record, this loop executes the If statement.Once the EOF property is True, the check eliminates the error that would otherwise occur by bypassing the Move.Next method in the loop.Avoid an endless loop.In the previous tip, a Do loop moves through records.If you omit the Move.Next method, the loop becomes stuck in an endless loop.The condition for ending the loop is never met and the position within the loop never moves.Always make sure you include a Move.Next method when you mean to cycle through a set of records.Failing to move in any type of looping structure can have the same result this isnt just a Do loop consideration.Tip If this happens to you and it happens to everybody, press Ctrl Break to manually stop the code.Accommodate nested Recordset objects.Access 2. 00. 7 introduces the multivalue field.That means despite what weve all been taught, you can now store multiple values in a single field.Whether you decide to use this enhancement is up to you.In fact, DAO and ADO use the Recordset object to represent a multivalue field.However, the possibility means that any Recordset that includes such a field must be able to handle the multiple values.In other words, a Recordset field could actually be another Recordset object.When encountering a multivalue field, you must add a loop to cycle through the values.Avoid errors when theres no match to a search.You can apply criteria to find specific records using a Find or Seek methods.However, when the method fails to find a matching record, it returns an error.You must ensure that a match exists before trying to move to the matching record, using the No.Match property as follows rst.Find searchcriteria.If Not rst. No. Match Then.End IfDAO uses the Find.First, Find. Next, and Find.Last methods. If theres no matching record, the code skips the If statement and continues, without generating an error.Force the newest record to be the current record.When you add a record to a Recordset, you might expect that record to become the current record.If thats your expectation, your code will fail to return the expected results because that is not what happens.If you want to work with the new record, you must force the newly added record to become the current record by setting a bookmark immediately after the Update method as follows rst.Update. rst. Bookmark rst.Last. Modified1. Avoid IS incompatibility in ADOADOs Find method doesnt support the IS operator.That means when youre searching or excluding NULL, you shouldnt use IS as follows rst.Find Last. Name NullIts the exact opposite in DAO, which requires IS rst.Find Last. Name IS NullThis difference is bound to cause trouble if you dont know about it.Because its so subtle, it could take a long time to find.ADO doesnt recognize the And operator.DAO lets you search for records using a complex criteria string.For instance, the following statement works just fine against a DAO Recordset rst.Find. Next Supplier.ID 1. 0 And Category.ID 4For better or worse, the statement run against an ADO Recordset using Find instead of Find.Next will return an error because ADO doesnt support the And operator in this way.To apply multiple criteria to a search task in ADO, use the Filter property instead rst.Filter Supplier. ID 1.AND Category. ID 4DAO also has a Filter property, but it works differently.DAO works against subsequent Recordsets, while ADO works on the current Recordset.Tip Use the Record.Count property to count the number of records in a filtered Recordset.Close Recordset objects.When the codes finished with a Recordset object, close it as follows rst.Close. An open Recordset, especially in older versions of Access, can hang things up.In short, you might not be able to close the application.Caution Closing a Recordset releases its resources.If you have multiple references to the same Recordset, one Close method will close them all.Postscript DAO vs.ADOWithin the Visual Basic world, there are two Recordset objects Recordset and Recordset.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |