Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Adodb Recordset Recordcount -1

I am a manic of dinamic settings, so I made a dinamic connection based on few varible like:

Dim cnnTmp as new adodb.connection
dim recTmp as new adodb.recordset

set cnntmp = new adodb.connection
cnntmp.connectionstring ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & " est.mdb;Persist Security Info=False" mysqlstring, cnntmp
so far the code is just fine but after this code if I try:
msgbox rectmp.recordcount it returns -1 even that I have let say 3 records on the table.
of course I made a little piece like:

dim i as integer
do until rectmp.eof
i=i+1: rectmp.movenext
but my question is:
How can I pass the do => loop code and simple after I open the recordset to have my real recordcount at the function rectmp.recordcount and not just -1?

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Adodb Recordset Recordcount
hello all,

nothing critical, but i cant understand why when i've coded a recordset through an ADODB connection i cant return the number of records in the recordset?

it returns -1 records in the set?? this is oarticularly bizzare since i can page through, update and retrieve values for the entire recordset...

can anyone shed any light?


"Smoke me a kipper I'll be back for breakfast!"

ADODB.Recordset.RecordCount Giving Problems.
I'm using ADO 2.7 library in VB 6 ..

I have this:

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset

'conn.ConnectionString = "Provider=MSDASQL; Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:pathfilename.mdb;
conn.ConnectionString = "Provider=MSDASQL; Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & dbPath & "coupons.mdb"

Set rst.ActiveConnection = conn

If (IsNew) Then
Dim CountRs As New ADODB.Recordset
Dim dbID As Integer

Set CountRs.ActiveConnection = conn
CountRs.Open ("Select Max(CouponId) as Max from coupon"), , adOpenKeyset

Debug.Print "recordcount" & CountRs.RecordCount

dbID = CountRs("Max") + 1

dbFontSize = IIf(dbFontSize, Null Or 0, 12)
doFontStyle = IIf(dbFontSize, Null, "")

strQuery = "Update Coupon Set FileName = " & dbFileName & "Logo = " & dbLogo & _
", LogoText = " & RTB1.Text & ", CutOffHeight = Nz(" & txtCutOffHeight.Text & _
",0) ,FontStyle = " & dbFontStyle & ", FontSize = Nz(" & dbFontSize & ", 12)"


strQuery = "INSERT INTO Coupon VALUES (" & dbID & ", '" & dbFileName & "', " & _
dbLogo & ", " & txtCutOffHeight & ", " & dbFontStyle & ", " & dbFontSize & _
", '" & dbLogoText & "')"

End If

rst.Open strQuery

I'm getting a "Syntax Error in the INSERT query" - mainly because dbID = 0 .. countRS is returning value of -1 and CountRS("Max") is returning 0. Why is that - even despite the fact that my CursorType is OpenKeySet . It is suppose to work for adOpenKeySet cursors ..

Can Anyone help me figure this out .l..

Thanks much!


Recordcount Through ADODB In VB6
Hon'ble gurus,
I am using VB6 and ADODB. I fail to count the total no of records in the table(Access2000) I am using.
My code is:
Option Explicit
dim con as adodb.connection
dim rs as adodb.recordset
Private Sub form_load()
set con=new adodb.connection
set rs=new adodb.recordset
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Mydata.mdb;Jet OLEDBatabase Password=ABC;") "select * from table1",con,adopendynamic,adlockoptimistic
msgbox rs.recordcount

This shows me always -1. Why? How to count the no of records? Please help.


ADODB.Command And RecordCount
Dear friends.
How I return the recordcount using ADODB.COmmand?

ADODb Connection Does Not Have Recordcount Working Properly
I have the following:

Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SERVER & "disp-1_be.mdb;Persist Security Info=False"
Set rst = New ADODB.Recordset
rst.Open "numbers", cnn, adOpenDynamic, adLockOptimistic
MsgBox rst.RecordCount

But when I run the code I get a -1 for the RecordCount in the messagebox...anybody knows why?

Thanks a bunch and...

recCount = strRecordSet.RecordCount

I need this figure for a
For i = 1 To recCount

Always returns -1, I know there are more records in the recordset, but always returns -1
The syntax must be wrong

Recordset.Recordcount = -1

Im getting data from a MS Access database and need to display it in a Flexgrid in my VB application.

My recordset is returning a recordcount of -1 eventhough there are records in the recordset.

Thats throwing up an error of Invalid Row number when the procedure attempts to populate the flexgrid.

Need help ASAP!!

Thanks in advance,


here is the subprocedure i use to connect

Dim sql As String

sql = sql_In

Set cnCompetitor = New Connection
Set comCompetitor = New Command
Set rsCompetitor = New Recordset

cnCompetitor.Provider = "Microsoft.Jet.OLEDB.4.0"
cnCompetitor.ConnectionString = "Sailing.mdb"

comCompetitor.ActiveConnection = cnCompetitor

comCompetitor.CommandText = sql

Set rsCompetitor = comCompetitor.Execute

Why Does My Recordset Have A Recordcount Of -1???
I have a recordset based on a table with 38 records. Therefore, it should have a recordcount of 38.

I basically said: "Select * from mytable", connection


myIntVariable = rs.recordcount

myIntVariable should be 38, but it says it has a value of -1 ?????

Any ideas??? Thanks!!

VB Recordset.recordcount
I have found some times that my recordset.recordcount=-1! but recordset.eof=false.
when dose it happen.
here i give u some sample code:

dim rstemp as new adodb.recordset "select * from emp",cnnmain,adOpenDynamic, adLockBatchOptimistic

if not rstemp.eof then
for i=0 to rstemp.recordcount
     doing something


end if

here in the above code the retemp.recourdcount result is -1. how can it be while i have come through the if not rstemp.eof then condition.

now if i do that following way like: "select count(*) as ccount from " _
& "emp",cnnmain,adOpenDynamic,adLockBatchOptimistic

here the result is rstemp!ccount=5

I cant find any reson for that.

Please let me know that.

with thanx

I have the following code:-

Dim cnn As New Connection
Dim rst As New ADODB.Recordset

Private Sub Command1_Click()
cnn.Open "bmpa", "sa", ""
rst.Open "select * from member", cnn, adOpenDynamic, adLockOptimistic, adCmdText
MsgBox rst.RecordCount
End Sub

the table has 17 records.
But the messageBox shows -1.
What could be the error?
pls help

Lloyd Dsouza

Ado Recordset RecordCount
I need value of ReCordCount of my ADODB.Recordset for set number of the grid rows. But I would like to open my recordset as “select…”,con, adOpenDynamic, adLockOptimistic
and with such CursorType and LockType Tb.RecordCount each time=-1. I don’t want MoveFirst and then Loop my recorset till Not TB.EOF for calculate number of records, it will take time.
MoveLast that sometimes we used in DAO recordset doesn't helps in ADO, gives you same -1

ADO Recordset - Why Is My Recordcount = -1
I am populating a recordset via and I know that the recordset is being populated because I can cycle through the records. However, for some reason, the rs.recordcount value is -1. Does anyone know why this is happening?


RecordSet.recordcount Question

I am looping through the recordset object and loading my list view happily.

I wanted to know how many records there are hence using the following

recordset.recordcount returns -1 and not the total no of record or the position of the actual cursor as it is looping.

Anyone know why ?

There may be a better way to return the total number of records, as oppose to using a counter (i=1+1). If anyone knows please let me know.



Recordset.RecordCount Problem
Disclaimer: Before I start, I'll point out that I have visited MSDN and tried to implement their solution (found here) to this common problem (namely, setting the recordset's CursorType property to adOpenStatic and/or setting the recordset's CursorLocation property to adUseClient).

Anyhow, I'm trying to loop through a recordset and create a new control array index for each record in the recordset. Everything works except that I can't get the Recordset.RecordCount property to return anything other than -1 -- regardless of the CursorType or CursorLocation values I set.

Here's the code:

Private Sub Form_Load()
Dim sConn As String
Dim oConn As New ADODB.Connection
Dim oRs As New ADODB.Recordset

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=main.mdb;" & _
"User Id=admin;" & _

oConn.Open sConn

[b] With oRs
.CursorLocation = adUseServer
.CursorType = adOpenStatic
End With[/b]
Set oRs = oConn.Execute("EXEC myStoredProc " & iParamValue)

If Not oRs.EOF Then
Dim i As Integer
For i = 0 To [b]oRs.RecordCount[/b] - 1
.... Do some stuff ...
End If

... Do cleanup ...
End Sub
Please note that the Recordset object does contain data. If I hard-code a value into my For...Next loop (in place of the oRs.RecordCount value), the whole thing works as intended.

Does anyone see something that I might be missing that's causing this problem?

Advice is appreciated.

Using Recordset's .RecordCount Functionality

Before this, I always use cursortype adOpenKeySet when setting my recordset (ado) to a connection string.

However, when I use parameterized query, I would use adoCommand and set the recordset like this : set rstDetails = adoCmd.Execute.

I notice that when I set my recordset like that, I won't be able to get a correct .RecordCount value. I think it's because the cursortype isn't adOpenKeyset. And so, is there a way where I can set the cursortype as adOpenKeyset to my recordset when I set my recordset to adoCmd.Execute? The only reason I set my recordset like that is because I need to set the parameterized query.

Pls. advice and thanks in advance.

Recordset Recordcount Problem..
Hi Guys,

Im filling a ComboBox with names from a recordset taken from an access database..

Its not quite right and I cant see whats up and hoped someone with a fresh eye could help?

My combobox loads with the names as wanted but the recordset says there are no returned records? this causes a problems for me....

Here is the code I have and as I say my list gets filled but the recordcount is wrong??? I dont understand how this can be because if the recordcount is -1 as the msgbox says then how does my list fill?

Any thoughts or support is appreciated..


Private Sub PopulateNamesCombo()
Dim rs As New ADODB.Recordset
Dim sSQL As String
Dim myconn As ADODB.Connection

Set myconn = New ADODB.Connection

myconn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & App.Path & "
ecords.mdb;" & _
"Uid=;" & _


lblpropstat.Caption = "Clearing Combo List"


sSQL = "SELECT * FROM names Order By name asc"
rs.Open sSQL, myconn

MsgBox rs.RecordCount ' << displays as -1

If rs.RecordCount <> 0 Then

Combo22.Text = rs.Fields("name")
With rs
Do While Not .EOF

Combo22.AddItem .Fields("name") ' << Fills as expected ??

End With

End If

Set rs = Nothing
End Sub

RecordSet.RecordCount Always Reports -1
I can move first and there will be data when I debug, but RecordCount is always -1. If I change my connection string to a older SQL server it works OK so I dont know whats going on.

conSQL.ConnectionString = "Provider=SQLOLEDB.1;Password=user_rw;Persist Security Info=True;User ID=user_rw;Initial Catalog=PC;Data Source=KARG"

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
strSQL = "SELECT * FROM ADSModels WHERE [FileName] = '" & FileList(x) & "'"

rs.Open strSQL, conSQL, adOpenForwardOnly, adLockOptimistic
If rs.RecordCount > 0 Then
If rs.RecordCount > 1 Then
End If
End If

Recordset Recordcount Returned -1
I'm query and store procedure.

After execute the query and retrieve data by using vb6.

I want to display the total rows returned from query.

I get the -1 value from recordset.recordcount.

Can anyone help me? thanks a million......

Nr = BN_tabel.Recordset.RecordCount
When I run this code I want nr to get the value of the recordcount, but it doesn't work.
I'm I doing something wrong??

nr = BN_tabel.Recordset.RecordCount

nr value is always "2"


Recordset.RecordCount Problem

I'm currently acessing a MS Access DB via DAO.

I have three records in a table. Why I try to count those records i.e
msgbox RS.RecordCount it only reads 1

The idea was to use it as a for loop count, so in this instance it only ever reads the first record.

If however I simply put it in a while not EOF loop then it does indeed go through the three records.

So the question is this, does the RecordCount simply not function correctly? Or is there something I may doing incorrectly?

---------While Works---------------

SQL = "Select * FROM [" & RecordSet & "] WHERE [customers] = '" & comboCust.Text & "'"
Set wreRS = db.OpenRecordset(SQL, dbOpenDynaset)

While Not wreRS.EOF
x = x + 1
Grid.AddItem ""
Grid.Row = x + 1
For y = 0 To wreRS.Fields.Count - 1
Grid.Col = y
Grid.Text = wreRS.Fields(y)

-----Fails due to RecordCount reading 1 (Noted via debug)----

SQL = "Select * FROM [" & RecordSet & "] WHERE [customers] = '" & comboCust.Text & "'"
Set wreRS = db.OpenRecordset(SQL, dbOpenDynaset)

for x=0 to wreRS.RecordCount - 1

Grid.AddItem ""
Grid.Row = x + 1
For y = 0 To wreRS.Fields.Count - 1
Grid.Col = y
Grid.Text = wreRS.Fields(y)



ADO Recordset.RecordCount Problem
I am having some ADO troubles. In the code below, the RST.RecordCount is always equal to zero. Please let me know why. I know the recorset is populated. I can iterate through the records but cannot get the recordcount.

Private DB As New ADODB.Connection
Private RST2 As ADODB.Recordset

Private Sub Form_Load()
Set DB = New ADODB.Connection
' The ConnectionString contains the path of the database.
With DB
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Mode = adModeReadWrite
.ConnectionString = "Data Source=C:spectrum mmsppdata2k.mdb"
End With
Set RST2 = New ADODB.Recordset
RST2.Open "SELECT * FROM employees", DB, adOpenDynamic, adLockOptimistic
If RST2.EOF = True Then
For iii = 1 To RST2.RecordCount
MsgBox "hi " + RST2.Fields(0)
Next iii
End If

AdoData.Recordset.RecordCount &gt; 1?
Using an SQL database I just need to find out if the database is empty or not. If the database is empty it says there is 1 record. If the database actually has 1 record it says there is 1 record. How can I tell if there is actually a good record or a phantom record without causing an error in my program?

Thank you.

Recordset Recordcount Always Returns -1
I am wondering why when I submit a query to the database, my return recordcount is always -1 even when there are records that get returned in this query. Everything is functional, but I could REALLY use the recordcount to get some stuff done within the application.

Adodc1.RecordSource = "Select * from LOAD_PLAN where CAT_ID_NO = '" & strEntry1 & "'"
x = Adodc1.Recordset.RecordCount
'(This query should always return one row.)

I also tried placing the Adodc1.Recordset.MoveLast above the RecordCount statement.

This one does the same:
Adodc1.RecordSource = "Select distinct(MECH_ID) from LOAD_PLAN"
x = Adodc1.Recordset.RecordCount
ReturnStatus = Adodc1.Recordset.Status
'This query should always returns multiple rows.

I even threw in the status just to see what the return value was. It comes back ok.

Any suggestions would be much appreciated.

RecordSet.RecordCount Returning -1
Hi All,
I have a VB Program, wherein I am calling a Stored Procedure using VB6.0. And am assinging the Stored Procedure's ADODB.RecordSet;
Dim rs as New Adodb.RecordSet
Dim cmd as New ADODB.Command
set rs = cmd.Execute()
if rs.RecordCount > 1 ''''----- Returning -1 even if the SP is returning one or more Rows...
end if
It was working fine. But don't know all of a sudden it started returning -1 even if the procedure is returning some recordset.
Can someone help me please?

I Have Problem On Recordset Recordcount
i have a text box its name is txtRMUcontrolNum.text i want put the number of records in this box but i have a problem when i cleck new shoul txtRMUcontrolNum.text = number of records plus one you can say as counter put the probelem insted of increse the number it give me decreasing number
see the code
private sub cmdNew_Click()
dim str as string
txtRMUcontrolNum.text = datAccessData.Recordset.RecordCount
so if the number of record is 234 it giv me 233 and compltee with the next record when i cleck new it give me number of record 232.
please help me

ADO Recordset Returning -1 Recordcount
Hello Folks,

This seems to be a common problem, but I can't find a thread which provides an answer for my situation.

I have a program which uses a single procedure to open a recordset:

rs.Open sSql, cnDb, adOpenStatic, adLockOptimistic
(sSQL is a sql variable).

The database may be a Jet 3.5.1, Jet 4.0 or SQL Server. For the first two db types, the recordcount is returned as expected, but for the SQL Server version, it's always -1, even though records may successfully be returned.

I've tried every combination of parameters - replacing adOpenStatic with adOpenKeySet and adLockOptimistic with adlockpessimistic, but it's still returning a recordcount of -1.

Hopefully I'm missing something simple, so help here would be appreciated!

ADO, RecordSet And RecordCount = -1????? &lt;Resolved&gt;
Hi All,
Thanks for reading my posting. I'm using VB6 with SP5 and have an ADO 2.0 reference to my project. I'm using a Access '97 DB.
When ever I read the RecordSet.RecordCount property I get a negative number returned. I know the record exists, but the count is a negative number.

Private Sub cmdExecute_Click()
Dim myConnection As ADODB.Connection
Dim myRecordset As ADODB.Recordset

Set myConnection = New ADODB.Connection

myConnection.ConnectionString = "Provider=Microsoft.jet.oledb.3.51;" _
                      & "Data Source=C:My DocumentsVB ProjectsFootprintsFootprints.mdb"

'Payments is a Table in my DB and CheckNumber is a Column in the Payments Table
Set myRecordset = myConnection.Execute("SELECT * From Payments WHERE CheckNumber = 110")

'Just a simple check to make sure the RecordSet is working properly
        'PaidTo is a Column in the Payments table
MsgBox myRecordset("PaidTo") & " The record count is " & myRecordset.RecordCount

'Record Count is a -1 ?????? Why???
'This Check Number is in my DB


End Sub

****This is Question 2 not completely related, but a mystery to me:********
           How do I insert a field with a SQL statement and ADO that is auto incremented in Access '97?

Thanks for your responce in advance.

Edited by - Default Programmer on 7/17/2003 9:31:18 PM

Error In Recordset.RecordCount - Need Help.
Hi all,

    My recordset.recordcount returns -1 even when 2 records exists.

I declared recordset in module as public like this

Public rstMTab As New ADODB.Recordset

and in two different modules iam using this recordset.

any one pl. help here.



When you are laughing, you are in a state of meditation.
b'caz laughing and thinking cannot be done together.

Edited by - bkvikas on 7/22/2005 4:26:06 AM

Data.Recordset.RecordCount Problem
i have a database and a data control. for some reason, when the form is first loaded, and i try to print the database, it only prints the first 35 records. so then i made a button that returns the data.Recordset.RecordCount to a textbox. when the form loads, i click the button, and my textbox says 35. but there are really 99 records. if i scroll down past 35 and click a record, and then click my button, it returns 99 to my textbox. does anyone know why this would occur?


ADO: Recordset.RecordCount Not Working In SQL Server
I am using VBScript with ADO (Active Data Objects), to connect to SQl Server 2000. For some reason the "RecordCount" property of "RecordSet" is not working with SQL Server. It returns -1 or something. It works perfectly fine with the same table in Microsoft Access.

I had to do a work around by actually looping through all records and counting (i.e. do until Recordset.EOF).

But am I missing something important here?
Do I need to do something to the table in SQL Server to get the "recordcount" property to work? I.e. build some kind of index or something?

Just want to make sure I am using the database in the most efficient way, thanks.


lentrycount =0

Do Until rst2.EOF
lEntryCount = lEntryCount + 1

Access Recordset Always Returns RecordCount Of 0
Right, hold on tight,

I'm using Access 97, running on Windows 2k. Data is stored on a server - well an XP Pro workstation that we are using as a server.

I have a routine that copies details of a job to a new job (basically a list of items required for that job). This data is added to the table using an append query. No problem with that.

I have a routine which updates the item numbers so that they are all consecutive (1,2,3 etc), this works on most PC's except for 1. This is running Windows 2k.

Whenever I open the recordset the recordcount always returns 0, even though if I go and look in the table there are 50+ records. I have tried adding DBEngine.Idle (with and without dbRefreshCache) at various points in the code, I have added loop counters to effectively pause the code, I've stepped through the code - but no matter what I do it there are never any records. I've even closed and reopened the recordset. All to no avail.

As you can imagine this is becoming increasingly frustrating and annoying, so if there is anyone out there who can possibly give me some pointers then all suggestions will be most welcome.



Edited by - PE Sys on 2/20/2004 1:44:08 AM

Recordcount On A Table Type Recordset
Has anyone experienced the phenomenon of a table type recordset reporting an incorrect record count? I have 376 records in a table. When a dynaset type recordset object is used the correct # of 376 records is reported for its "recordcount" value. Although when I use the table type recordset the amount of 302 is reported for its "recordset" value. Can anyone help solve this mystery?

Vb Application Error When I Run Recordset.recordcount Property.
I am trying to fill a MSFlexGrid with entries using recordset. To find the no of columns for the MSFlexGrid, I use the recordset.recordcount property. I get an vb application error:
'The instruction at "0x77f58qb2" referenced memory at"0xfffffff8". The memory could not be "read". Click on OK to terminate the program. When I step into the code, I get the error after compiling the recordcount property. Why do I get this error? I have tried running other programs with recordcount property, it is just this one program which gives this error. Any reason why this occurs?

Difference Between Using Recordset And ADODB.Recordset

When accessing a DB using ADO, whats the difference between using the AODB prefix and not using it, for example setting a variable as "Dim rs as Recordset" and "Dim rs as ADODB.Recordset" ?



Recordset Based On Stored Procedure Not Allowing Recordcount Property
Well I am calling a stored procedure written in Sql Server from VB.I take the result in a recordset. But the recordset doesn't allow Move previous/Recordcount/Movefirst , and find methods. Also the recordcount returned is always -1. Can anybody let me know how to overcome this.

ADODB Recordset .. Why Would You
In a different post, the following code is observed. No matter how long I have used and/or played with VB6, I have always wondered why you would use
VB Code:
Dim rsInformation as ADODB.RecordsetSet rsInformation = New ADODB.Recordset
Why wouldn't you just use
VB Code:
dim rsInformation as new ADODB.Recordset
I use it this way all the time and never have a problem! I don't use the Set ....

Is there something going on in the background I should be concerned with?

I need to create a temporary ADODB recordset in my VB program.
This recordset is in memory only, it is not ties to any external
table. I need to be able to do a keyed read on the contents
and update some of the fields. I cannot find a good example
of the in MSDN. I found some things close to it but not quite.
Is what I am after possible? Can any one point me to a place to
get some examples? thanks

ADODB.Recordset Help
hello, i was wondering if it was possible to set a field to a specific Word Document from VB using an ADODB recordset. it can be done "manually" using just Access, but i am creating an app that needs to do this behind the scenes.

thanks in advance for any help,

Help With ADODB.Recordset
hello, i was wondering if it was possible to set a field to a specific Word Document from VB using an ADODB recordset. it can be done "manually" using just Access, but i am creating an app that needs to do this behind the scenes.

thanks in advance for any help,

0 With ADODB Recordset
I am trying to collect datas from a Sybase DB and to
populate a ADODB Recordset with those datas. I inconter
the following problem : all numbers have theyre zeros at
the right removed. For example, a 1000 in my Sybase DB
will appear in my Recordset as 1.

I precise that I am working with Excel 2002 on Windows XP
and that my spreadsheet worked perfectly with Excel 97
under Windows NT.

Has anyone already incontered this issue ?

Thanks in advance for your help.


I have a program that reads a Database file (*.dbf - dBase IV) to a ADODB.RecordSet Variable and I set the Datasource property of my MSHFlexGrid to this recordset to diplay it.
On this level program works correctly. I know how to open a DB, which connection to set etc.

I have a problem with saving changes. I dont know whow to create a connection and how to perform this save. I want to write a procedure that saves all changes made on the grid to the database that i had opened earlier. I think that there is a simple way to do that but I am struggling with it for over a week. I have been posting about that elsewhere but nooned had helped me.

Here is some of my code
opennig DB:

Private Sub OpenDB(sFile)

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

conn.mode = adModeShareDenyNone
conn.CursorLocation = adUseClient
conn.Open ("Driver={Microsoft dBASE Driver (*.dbf)};" & _
"SourceDB=" & sFile & ";" & _

Dim strSQL As String: strSQL = "SELECT * FROM " & sFile

Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.CursorType = adOpenKeyset
rs.Open cmd

Set MSHFlexGrid.DataSource = rs

End Sub

Please help me!!!
Thanks in advance.

ADODB Recordset
I am belatedly catching up on ADODB recordsets (with A2K) and have hit a speedbump.

I am attempting to open a named query with a field that references a dropdown control (Forms!MyForm!MyCombo) as criteria.  I get an error message advising me to use a Valid SQL statement beginning with SELECT, DELETE, etc.

The query will return a recordset on its own.

If I change the query's control reference to a hardcoded string, my ADODB recordset opens without difficulty.  This obviously lacks flexibility.

I have also run through all the ADODB recordset flavors (except batch) with the same un happy results.

For grins, here is the code snippet

Dim cnn as ADODB.Connection
Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = = adUseServer 'also tried Client side
rs.Open "qrySet", CurrentProject.Connection, adOpenStatic, adLockReadOnly

The code halts on the rs.Open line.  Any suggestions to keep me from retreating to DAO will be appreciated.


hi i like to how i use the two sql statement in ADODB.Recordset..
this my code

Public Function sqlGetGrpEmailInfo(ByVal grpId As String) As ADODB.Recordset
On Error GoTo SQLERR
     Dim sqlString As String
     Dim sqlString1 As String
    sqlString = "SELECT customer_id FROM customer_group WHERE group_id=1"
    sqlString1 = "SELECT contactPerson & '(' & name & ')' as addresee, email FROM Customer WHERE ????"
 i like to use the retrieve customer_id to place in the condition where customer_id = customer_id in the sqlString1
    Dim rs As ADODB.Recordset
    Set rs = adoLib.getRecordSet(sqlString)
    Set sqlGetGrpEmailInfo = rs
    If Err.Number <> 0 Then
        sqlErrorLog sqlString, Err.description
    End If
End Function

Edited by - evangeline.nah on 5/28/2004 2:05:27 AM

How Can I Get Recordset [ADODB]
First of all , sorry for my bad English.

Dim RS as Recordset
Dim RSADODB as ADODB.Recordset

How can i get the recordset of RSADODB to RS.

ADODB Recordset
I have got following doubt with the ADODB recordset

dim recEx1 as new ADODB.recordset
dim recEx2 as new ADODB.recordset

set recEx1 = funGetRecords() 'Function returns the recordset after querying 'the table

recEx1.Filter = "field1 = 'ABC'" 'filter the records where field1 = 'ABC'

set recEx2 = recEx1

recEx1.filter = adFilterNone 'removes the filter
Msgbox recEx1.recordcount
Msgbox recEx2.recordcount

Suppose for the argument sake recEx1 contains 10 record and after filtering it has got 5 records. But I will get both Output (Message box) as 10 records and not 5 for recEx2.recordcount. Why is this happening ?.
When I specify recEx2 as NEW ADODB.recordset why recEx2 points to recEx1 ?.
What is the difference between 'recEx2 as new ADODB.recordset' and recEx2 as ADODB.recordset (without new)' in this contest ?.

Please let me know if possible, how can I make sure that when filter is removed from recEx1, recEx2 should have 5 records and not 10 (as in recEx1 without filter )?. Also If I make changes to recEx2 (assuming 2 different recordset) how to make sure that when I update in recEx2 will be reflected in recEx1...

thanks in advance....

Need Help With ADODB Recordset.
How hard is this to do?

I have a recordset in memory. I need to execute a SQL statement on it and generate a new recordset in the process. Is this possible WITHOUT having to first save the RS to a database.

what I need to do is create a RS with all records that contain duplicate entries within a particular field.

Any help would be greatly appreciated.

Question About ADODB.Recordset
dim rs as new ADODB.Recordset
rs.Open SQL,conn,....

what is temp? dim temp as ____?
lets say the column "Hour" is empty in the database...when I do the line:
msgbox rs("Hour")
I get an run time error: "Invalid use of null"
I tried also the line:
if rs("Hour")=null then msgbox "empty"
and this line didnt get me a run time error but it didnt open the msgbox!
so, what is the value of rs("Hour") when the cell is empty.
p.s. with cells that are String cell in the database even if they are empty the line works.

ADODB.Recordset And Requery
i have a program that i make lot of queries
and beacuse of that i tried to do :

If rs.State = adStateOpen Then
rs.Requery update
Else update
End If
but i recive type mismatch when i do requery (the syntax in the update is ok i chekced it withthe db)
any idea what can be the problem?
thnaks in advance

Source: ADODB Recordset
In my application when I click on my save button I get the following message:

Error Code: 3265
Description: Item caanot be found in the collection corresponding to the requested name or ordinal.
Source: ADODB Recordset.

I know this is a long shot, but here is the coding for the save button:

Private Sub cmdSave_Click()

On Error GoTo ErrorHandler
If mblnEdit = True Then
mblnEdit = False
If strEditRec <> txtSection.Text Then
End If
End If
If ValidData Then
If mblnAdd Then rsRecordSet.AddNew
mblnAdd = False
SetButtons True
txtSection.Enabled = True
CmdEdit.Enabled = True
CmdDelete.Enabled = True
End If
Exit Sub

If rsRecordSet.EditMode = adEditAdd Then rsRecordSet.CancelUpdate

End Sub

Thanks in advance

Copyright © 2005-08, All rights reserved