Related Topics


MS Access 2007: ADO Recordset Filter returns 0 records
I have spent several hours on a problem with ADO recordset trying to filter records. I always get 0 records.

Sample project: [LINK]

To run in Immediate Window:


rS1.RecordCount = 4
rS2.RecordCount = 13

rS2.Filter = (ParPol like '555?????') rs2.RecordCount = 0
rS2.Filter = (ParPol like '5556????') rs2.RecordCount = 0
rS2.Filter = (ParPol like '55221???') rs2.RecordCount = 0
rS2.Filter = (ParPol like '555611??') rs2.RecordCount = 0

rs2 must be read/write. I'd like to stay with late binding.

Cursors in MS Access and ADO
In multi-user, multi-tier distributed application scenarios where one has to provide the most current data possible, keeping the network traffic low can be challenging. The idea of using cursors becomes very appealing by offering different ways of showing the data. This tutorial, the third in a series covering MS Access and ADO, looks at cursors. TOC: Cursors in MS Access and ADO; ADO Cursors; The Recordset's RecordCount, AbsolutePosition, BOF and EOF properties; CursorType changed to adOpenForwardOnly.

RecordSet RecordCount returning -1
I am using the following to create a connection to an Access DB.


Unfortunately, when I try to use the RecordCount property it always returns -1. I have tried to Google this and everywhere keeps telling me why it happens but offers no suggestion to fix it. For example from [LINK]:

Note: This property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and -1 or the actual count for a dynamic cursor.

Note: The Recordset object must be open when calling this property. If this property is not supported it will return -1.
rs.Supports(adBookmark) is returning True and I am still getting -1. It returns all the records without a problem, but any reference to RecordCount at any point gives me this -1 value. As I understand it from googling, DAO recordsets do this if you don't go to EOF first. but ADO recordsets shouldn't do this normally.

Testing Recordset for Records
I have long used .recordcount to test a recordset for records before doing some sort of processing.

For example:

If rst.recordcount = 0 then
exit sub
Do some process
End If

This usually does the trick but I am there another method I might try to see if a recordset contains records

Testing Recordset for Records
I have long used .recordcount to test a recordset for records before doing some sort of processing.

For example:

If rst.recordcount = 0 then
exit sub
Do some process
End If

is there another method I might try to see if a recordset contains records?

DAO.Recordset Vs ADODB.Recordset Vs Recordset
So I know that there are two types of libraries for retrieving recordsets, either DAO or ADO.
I read somewhere that if you are using DAO, you should use DAO.recordset instead of just "recordset" to make sure it run properly.
Dim rs As DAO.Recordset
instead of
Dim rs As Recordset

but if the default is DAO anyways (I think), would it really cause any problems. (I'm using Access 2003)

So now, which is better DAO or ADO if I'm only using a recordset to update a single field of a record from a table that may contain hundreds of records.

Which is better in general? What exceptions are there? What do other people use

How to: Bind a Form to an ADO Recordset
Describes how to set a form's Recordset property to an open ADO Recordset object.

ADO Recordset Literature
Can anyone provide a link or two to some good ADO Recordset information? I have a feeling I am going to be using them more and I am an amateur at best

Using the Recordset with MS Access and ADO
A Connection object may give you access to the database, but that's only half the equation; the Recordset object gives you access to the data. Keep reading to learn more about the Recordset. TOC: Using the Recordset with MS Access and ADO; The Recordset Object, Properties, Methods and events; Write code to Open ADODB connection, Recordset and close open objects; Review recordset properties using code.

SQL works in query but not VBA
For over a year now I have used the following sql statement to open an ADO recordset in Access 2007 VBA for a monthly process we have. This month the recordset showed no recordsto be processed. I copied the statement into a new query and the query returned over 30 records.

I found that if I try to re-create the query completely using code and opening the recordset directly from a table, the recordset would give the correct number of records.

I've also checked the recent critical updates installed and can't find one that may have changed vba's ability to open a recordset from a query. I have hundreds of ADO recordsets opened on queries throughout my database and don't want to have to re-write all the SQL statements so any help is greatly appreciated.

sql = "SELECT * FROM << myQuery >> WHERE File_Name = '" & [File] & "' ORDER BY Acct1;"
rsTemp.Open sql, cnMe, adOpenStatic, adLockOptimistic
If rsTemp.RecordCount > 0 Then
<< process data >&gt

simple recordset loop?
here? I have this simple code-

Dim db As DAO.Database Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblReturnList", dbOpenDynaset)
j = rs.RecordCount
For I = 1 To j
'##collect data from table or query##
strGetData = rs!fldFirstName
MsgBox strGetData
Next I

it only ever returns the value of 1. If I add rs.MoveLast above the rs.RecordCount, I get the record count but then the loop fails on the msgbox as it is at the end of the recordset.

All im trying to do is loop through the records in the recordset and display a messagebox with the name. Hardly a complicated thing and ive done it many times but feel I might be a little rusty under the arches lol.

Any quick help available

Open ADO Recordset from Memory
rs.Open strSQL, {Active Connection}, adOpenStatic, , adCmdText

Is it possible to designate an {Active Connection} such that it allows the recordset to be derived from another ADO recordset already in memory?

Or must I hit the original database again

I would like to move to record number x in a recordset using

me.recordset.move x, 1

However, I keep getting a "not a valid bookmark" error.

What am I missing? Does the form's recordset object take different methods to an ADO recordset?

Of course, I can achieve the same effect using:

me.recordset.move x

but that doesn't help me to understand why I can't get .move to work with two parameters

Sorting data in ADO Recordset
I am trying to calculate time between failures on a large dataset of repairs.

I am attempting to pull in the data from a query into an ADO recordset, sort the recordset and then step through the recordset, processing as I go. However every time I process the data the sort does not work.

My VBA for calling the data an sorting the data is below, can anyone tell me what I am doing wrong

Dim rstAllRepairs As Recordset Set rstAllRepairs = CurrentDb.OpenRecordset"qry_RepairsOrderedForMTBFCalculations")
rstAllRepairs.Sort = "rpr_JobNumber DESC, rpr_Model DESC,rpr_SerialNumber DESC

ADODB RecordCount
Can someone please tell me how to get an accurate RecordCount from an ADODB RecordSet?

I've tried using adOpenStatic CursorType and adLockOptimistic LockType. I've also tried moving from the first to the last record; all of these are suggestions from the web but they do not work.

I currently have this:

Sub b()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Set conn = New ADODB.Connection
conn.Open CurrentProject.Connection
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "SELECT DocumentNumber, Revision, FROM PartImport WHERE ([DocumentNumber] = '" & strPartNumber & "');"
Set rst = cmd.Execute
Debug.Print "Recordset recordcount: " & rst.RecordCount
Set rst = Nothing
Set conn = Nothing

Can't add new record to subform bound to ADO recordset
I have Access 2010 (front end) and SQL Server 2008 Express Edition (back end). I'm using ADO recordsets to populate a form and subform that I need to use for updates in the data.

The thing is that I need to be able to add new records to the subforms, but these subforms only have records created before, and I don't get the blank (new line) record.

Is there a way to get this line or is it impossible to get with ADO recordset linked forms?

Here is what I'm doing with the Open Event of the form:

DoCmd.GoToRecord,, acNext impossible to pass the record
I should make a function with a button that clears the fields on the form.
I wrote this, but it gives me 'a mistake at the first DoCmd.GoToRecord,, acNext
saying before the records were written by many users at once, and then hypoxic switch to the specified record.
that can be?

On Error GoTo Err_Next_Record
DoCmd.GoToRecord , , acFirst
Do While (Recordset.AbsolutePosition <= Recordset.RecordCount)
Debug.Print Recordset.AbsolutePosition & " *** " & Recordset.RecordCount
CasellaControllo20.value = 0
CasellaCombinata23.value = Null
quantita.value = Null
status.value = Null
tipo.value = Null
dwgsize.value = Null

DoCmd.GoToRecord , , acNext

Exit Sub

MsgBox Err.Description
Resume Exit_Next_Record

filter a recordset
I am trying to filter a recordset but it doesn't seem to be working.

The forms recordset has 3 rows. One of the fields is a status flag. I want to only show the rows in my recordset where the StatusFlag is empty ("").

I use the filter but when I do a row count I still get 3 rows

Set rs = srcForm.Recordset
rs.Filter = "StatusFlag = """""
MsgBox rs.RecordCount

Sum in Footer on Form with ADO Recordset
Why would Sum(fieldname) not work in a form based on an ADO Recordset drawing from a Stored Procedure?

I had a form with a DAO query from linked tables as its RecordSource. Textboxes in the footer showed the Sum for various fields.

When I changed the form to use an ADODB Recordset based on a Stored Procedure in MS SQL Server 2005 the Sum textboxes return Error. There were no other significant changes to the form.

There are certainly other ways to get the Sum but I wonder why it doesn't work the same as the Recordset coming from the DAO RecordSource

how to return every 5th records?
I have a recordset that has more than 50 records. Now I want to use ADO method to return every 5th records i.e.5;10;15;20. to makeup a new recordset. And I have the following codes:

Private Sub Command4_Click() Dim rst1 As New ADODB.Recordset Dim rst2 As New ADODB.Recordset Dim cnn As ADODB.Connection Set cnn = CurrentProject.Connection rst1.Open "select * from sheet2", cnn, adOpenKeyset, adLockOptimistic rst2.Open "sheet3", cnn, adOpenKeyset, adLockOptimistic For I = 1 To rst1.RecordCount Step 5 rst2.AddNew rst2.Fields(1) = rst1.Fields(0) rst2.Fields(2) = rst1.Fields(1) rst2.Fields(3) = rst1.Fields(2) rst2.Fields(4) = rst1.Fields(3) rst2.MoveNext Next I rst2.Update Set Me.Sheet2_subform.Form.Recordset = rst2 Set rst1 = Nothing Set rst2 = Nothing End Sub

But it failed to work. Could anyone help me out