Count number of Records Query always returns -1

[Andibevan]

I am trying to count the number of records in a query but it keeps
returning -1 when there are lots of records returned by the query.

I have tried the following approaches:-
'Debug.Print "Records = " & rst.RecordCount
'Debug.Print "Records = " & rst.GetRows

Recordcount returns -1 and getrows throws an error - "Type Mismatch".

What have I got wrong?

My total code is:-

Private Sub ExportSignoff()
Dim MyVar As Boolean
Dim strQueryName As String: strQueryName = "qry_MyTemp_1"
Dim strTemplateLoc As String: strTemplateLoc = "C:\Documents\Application
Data\Access\WIP\PVCS_CM\SMV - Sign-Off Template.xlt"
Dim rst As ADODB.RecordSet
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command

'Set rst = CurrentDb.OpenRecordset(strQueryName)

cmd.CommandText = "EXECUTE " & strQueryName
cmd.CommandType = adCmdText

' Set cnn = GetNewConnection
'cnn = GetNewConnection
Set cnn = CurrentProject.Connection

cmd.ActiveConnection = cnn
Set rst = cmd.Execute
'Debug.Print "Records = " & rst.RecordCount
'Debug.Print "Records = " & rst.GetRows

' Export
' MyVar = ExportToTemplate(rst, 15, 3, m_xls, strTemplateLoc)
MyVar = ExportToExcelSignOff(rst, 15, 3, m_xls, strTemplateLoc)
' MyVar = ExportToTemplate(rst, 15, 3, m_xls)


' Disconnect recordset and return
rst.Close
cnn.Close
'rst.ActiveConnection = Nothing

Set cnn = Nothing
Set cmd = Nothing
End Sub

 

[Jerry Whittle]

Look what I found in Help under Recordcount:

When you work with linked TableDef objects, the RecordCount property setting
is always –1.

 

[RoyVidar]

I am trying to count the number of records in a query but it keeps
returning -1 when there are lots of records returned by the query.

I have tried the following approaches:-
'Debug.Print "Records = " & rst.RecordCount
'Debug.Print "Records = " & rst.GetRows

Recordcount returns -1 and getrows throws an error - "Type Mismatch".

What have I got wrong?

My total code is:-

Private Sub ExportSignoff()
Dim MyVar As Boolean
Dim strQueryName As String: strQueryName = "qry_MyTemp_1"
Dim strTemplateLoc As String: strTemplateLoc =
"C:\Documents\Application Data\Access\WIP\PVCS_CM\SMV - Sign-Off
Template.xlt" Dim rst As ADODB.RecordSet
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command

'Set rst = CurrentDb.OpenRecordset(strQueryName)

cmd.CommandText = "EXECUTE " & strQueryName
cmd.CommandType = adCmdText

' Set cnn = GetNewConnection
'cnn = GetNewConnection
Set cnn = CurrentProject.Connection

cmd.ActiveConnection = cnn
Set rst = cmd.Execute
'Debug.Print "Records = " & rst.RecordCount
'Debug.Print "Records = " & rst.GetRows

' Export
' MyVar = ExportToTemplate(rst, 15, 3, m_xls, strTemplateLoc)
MyVar = ExportToExcelSignOff(rst, 15, 3, m_xls, strTemplateLoc)
' MyVar = ExportToTemplate(rst, 15, 3, m_xls)


' Disconnect recordset and return
rst.Close
cnn.Close
'rst.ActiveConnection = Nothing

Set cnn = Nothing
Set cmd = Nothing
End Sub

Some say that the ADO recordcount is unreliable (count me in there),
and
if you really, really need a recordcount, use a "select count(*)..." on
the same table/query with the same where condition.

Others say that as long as you use a clientside cursor, it's OK, or use
for instance also a static cursor. Something like this air code?

dim rs as adodb.recordset
set rs = new adodb.recordset
with rs
set .activeconnection = currentproject.connection
.cursorlocation = aduseclient
.cursortype = adopenstatic
.locktype = adlockoptimistic
.open strQueryName,,,,adcmdstoredproc
debug.print .recordcount
end with

But what do you need the recordcount for?

If you just use it as a test for whether the recordset has records, you
can rather use

if ((not rs.bof) and (not rs.eof)) then
' contains records
end if

 

[Andibevan]

Some say that the ADO recordcount is unreliable (count me in there),
and
if you really, really need a recordcount, use a "select count(*)..." on
the same table/query with the same where condition.

Others say that as long as you use a clientside cursor, it's OK, or use
for instance also a static cursor. Something like this air code?

dim rs as adodb.recordset
set rs = new adodb.recordset
with rs
set .activeconnection = currentproject.connection
.cursorlocation = aduseclient
.cursortype = adopenstatic
.locktype = adlockoptimistic
.open strQueryName,,,,adcmdstoredproc
debug.print .recordcount
end with

But what do you need the recordcount for?

If you just use it as a test for whether the recordset has records, you
can rather use

if ((not rs.bof) and (not rs.eof)) then
' contains records
end if

Thanks for your comments - I need the count of the records in order to know
how many lines to insert into an excel template. I think I will use your
"select count" recommendation.

What is a clientside cursor or server cursor? - I imagine this is a very
basic question but I am a bit of a Newb.

Thanks

Andy

 

[Andibevan]

Sorry - I should have spotted that - thanks

Look what I found in Help under Recordcount:

When you work with linked TableDef objects, the RecordCount property setting
is always -1.

 

[RoyVidar]

Thanks for your comments - I need the count of the records in order
to know how many lines to insert into an excel template. I think I
will use your "select count" recommendation.

What is a clientside cursor or server cursor? - I imagine this is a
very basic question but I am a bit of a Newb.

Thanks

Andy

Now that is something I wouldn't even dare start explaining, but did a
quicky with Google and came up with among others, these two links
(watch
for linebreaks in the links)

http://groups.google.com/group/microsoft.public.vb.database.ado/browse_frm/thread/307204774e20b677/
http://groups.google.com/group/microsoft.public.vb.database.ado/browse_frm/thread/9838e13c9f56913f/

Hopefully they will give some information.

BTW - if you'd opened through ADO, I think the recordcount is a bit
more
reliable, just fully access the recordset before getting it (do for
instance a .movelast and .movefirst on it)

 

[RoyVidar]

RoyVidar wrote in message <[email protected]> :

Oh dear, let me alther the BTW part to:

BTW - if you'd opened through *DAO*, I think the recordcount is a bit
more reliable, just fully access the recordset before getting it (do
for instance a .movelast and .movefirst on it)

 

[SteveS]

Look what I found in Help under Recordcount:

When you work with linked TableDef objects, the RecordCount property setting
is always –1.

A saw that also, but I use split databases (linked tables) and get the
recordcount from recordsets all the time (using DAO).

If I open a recordset and immediatly check the record count property, I get
a 0 (zero) if there a no records and a 1 (not a -1) if there are 1 or more
records returned. This is Access FE to Access BE. ( I'm going to test this on
a XBase file when I get one made.)

I don't know if this will work in ADO, but you might try this:
(air code)

' using BOF and EOF
'***SNIP*******
cmd.ActiveConnection = cnn
Set rst = cmd.Execute
'If Not rst.BOF and Not rst.EOF then
' rst.MoveLast
'End If
'Debug.Print "Records = " & rst.RecordCount
'Debug.Print "Records = " & rst.GetRows
'***SNIP*******

or

'using recordcount <> 0
'***SNIP*******
cmd.ActiveConnection = cnn
Set rst = cmd.Execute
'If rst.RecordCount <> 0 then
' rst.MoveLast
'End If
'Debug.Print "Records = " & rst.RecordCount
'Debug.Print "Records = " & rst.GetRows
'***SNIP*******

 

[SteveS]

Works with a dBase IV linked table also.

 

[Van T. Dinh]

DAO Count will return the correct number of rows in the Recordset (after
fully traversing the Recordset).

ADO RecordCount may not return the actual number of rows in the Recordset
depending on the cursor type. From ADO Help:

"The cursor type of the Recordset object affects whether the number of
records can be determined. The RecordCount property will return -1 for a
forward-only cursor; the actual count for a static or keyset cursor; and
either -1 or the actual count for a dynamic cursor, depending on the data
source."

 

[Van T. Dinh]

To obtain the correct RecordCount with ADO Recordset, I normally specify the
cursor type "adOpenStatic" because the value of the RecordCount depends on
the cursor type. Here a bit of code from one of my databases:

====
Set rsBRMachine = New ADODB.Recordset
rsBRMachine.Open strBRM_SQL, fnGetCnnSQL(), adOpenStatic, adLockReadOnly

With Me
If rsBRMachine.RecordCount > 0 Then
rsBRMachine.MoveFirst
====

BTW, GetRows returns multiple rows with multiple values in each row so you
cannot use it as a String in the String construction.