does recordset.recordcount work on a command?

[SAm]

hi

i am pasting in here my code. my question is can i do a recordcount on a
recordset that is based on a command (query) ? for some reason it doesn't
work. i can do things with the field names i can work with the recordset, but
i can't get the recordcount to work. i tried changing the type of the
recordset but i got an error for that. any ideas?


Dim cmd As ADODB.Command
Dim pa As ADODB.Parameter
Dim rstGetRecordSet As ADODB.Recordset

Set cmd = New ADODB.Command
Set pa = New ADODB.Parameter
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "MyQueryQry"
cmd.CommandType = adCmdTable
cmd.Parameters.Refresh

For Each pa In cmd.Parameters
pa.Value = Eval(pa.Name)
Next pa

Set rstGetRecordSet = New ADODB.Recordset
Set rstGetRecordSet = cmd.Execute

 

[Daniel]

I have had to move to the last record to get a proper record count in some
instances. you might want to try it and see if it works for you

 

[RoyVidar]

hi

i am pasting in here my code. my question is can i do a recordcount on a
recordset that is based on a command (query) ? for some reason it doesn't
work. i can do things with the field names i can work with the recordset, but
i can't get the recordcount to work. i tried changing the type of the
recordset but i got an error for that. any ideas?


Dim cmd As ADODB.Command
Dim pa As ADODB.Parameter
Dim rstGetRecordSet As ADODB.Recordset

Set cmd = New ADODB.Command
Set pa = New ADODB.Parameter
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "MyQueryQry"
cmd.CommandType = adCmdTable
cmd.Parameters.Refresh

For Each pa In cmd.Parameters
pa.Value = Eval(pa.Name)
Next pa

Set rstGetRecordSet = New ADODB.Recordset
Set rstGetRecordSet = cmd.Execute

I think that when you request a recordset like this, you'll get a
readonly, forwardonly recordset with server side cursor. This doesn't
support .RecordCount

If you do something like this in stead

Set rstGetRecordSet = New ADODB.Recordset
With rstGetRecordSet
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.CursorType = adOpenStatic
.Open cmd
End With

You should probably get an accurate recordcount.

Now - some thoughts/issues...

1 - I don't really trust ADO recordcount. If I really, really need
a recordcount, I'll do a "SELECT Count(*) FROM theTable" with
the same WHERE clause
2 - you seem to be opening a query, but specify adCmdTable,
shouldn't it be adCmdStoredProc?
3 - in these groups you'll mostly get the advice of using DAO over
ADO as long as you use Jet (and also for ODBC linked tables)
4 - if the above resolves parameters from forms dynamicly, then
it probably wont work in Access 2000, see for instance
Listing 6.14 here

http://msdn2.microsoft.com/en-us/library/aa139932(office.10).aspx

resolving the parameters explicitly would also work, and execute
faster.

But, do you really, really need a recordcount? If you're just after
knowing whether there are records in the recordset or not, you could
just test for that. Here's one common test

If (Not rstGetRecordSet.BOF) And (Not rstGetRecordSet.EOF) Then
' recordset contains records
End If

 

[SAm]

sorry bro,

but your trick didn't work. i will comment more on RoyVidar, to explain what
i am doing.

thanks,

sam

 

[SAm]

Thanks Roy for your answer. unfortunately, it didn't help. let me explain my
situation.

I am running a query (which is actually step 4 in a sequence of queries)
with parameters from a form. this is why i need to use adCmdTable.

I am realizing that for some reason the recordset is not correct. its not
returning the same recordset as the query. in my query i get maybe 24 records
and i can get 160 records using the command/recordset combination. i used
this before and i don't know what is going on.

so why do i need the recordcount. i need because i paste the recordset into
an excel spreadsheet and i then i format the columns. i can loop through all
the rows to figure out where i have data (e.g. do while row=data) but i don't
want that. i had it working, but it took a long time. i would rather use the
recordcount and then i know how my data is mapped in excel.

note: that if i use intloop = DCount("*", "MyQueryQry") i get an accurate
count. but i think that this is also a waste since i am rerunning the query.
please understand that this query takes a while to run.

the basics about this database. i use tables which have an odbc connection.
i then have this final query that is based on 6 other queries. first two i
pull from two seperate databases (tables in my access, from two different sql
servers, and refer to two different databases) then i have 4 queries that
narrow down the data i need.

i am still working on making this command/recordset work. if you got any
ideas i would really appreciate it.

btw, the with statement you sent, works fine, although i see no difference.
i think i prefer it over the execute statement, so i will use your method.

thanks,

sam

 

[RoyVidar]

I am realizing that for some reason the recordset is not correct. its not
returning the same recordset as the query. in my query i get maybe 24 records
and i can get 160 records using the command/recordset combination. i used
this before and i don't know what is going on.

Before going into more detail, the most common reason for ADO stuff
to return different number of records than what one see within the query
designer, is wildcards.

The Access dialect uses * and ? while ADO uses % and _ (underscore)
respectively.

Could that account for the missing records?

 

[SAm]

there are no wild cards. the problems is that it returns too many records. i
don't understand how. it is not returning all records from level 1 query, but
just in between. so lets say that level4qry has 25 records and level1 has
210, its returning 160 records!

i am still trying to figure this one out.

sam

 

[RoyVidar]

there are no wild cards. the problems is that it returns too many records. i
don't understand how. it is not returning all records from level 1 query, but
just in between. so lets say that level4qry has 25 records and level1 has
210, its returning 160 records!

i am still trying to figure this one out.

sam

I don't know, at least not without the SQL, (and I probably wouldn't
know then either ;-) )

One suggestion, which I have already made, is to resolve the parameters
explicitly. There might be something about your way of resolving the
parameters.

Say for a simple query myquery

SELECT CustomerName, CustomerAddress
FROM myTable
WHERE ID = Forms!frmMyForm!txtMyControl

air code

dim cmd as adodb.command
dim prm1 as adodb.parameter
dim rs as adodb.recordset

set cmd = new adodb.command
with cmd
set .activeconnection = currentproject.connection
.commandtext = "myquery"
.commandtype = adcmdstoredproc

' per each parameter
set prm1 = .createparameter("Forms!frmMyForm!txtMyControl", _
adInteger, adParamImput)
.parameters.append prm1
prm1.value = Forms!frmMyForm!txtMyControl

set rs = .execute
debug.print rs.getstring
end with

this should also speed up execution - and - do also try DAO.

 

[SAm]

Ok,

I just found the problem. at one of the levels i am using the *. i changed
it to % and ado is reading it fine. i must say, i knew that there was a
difference (i read about it) but i never had problems with it. the reason i
use ado is because its considered "new" and i just learned that. i know how
to read dao but i am not fluent. i can tell that many times its better to use
dao, but for the most part ado works for me better.

now i have a problem, cause i would rather use the * which is more universal
to access. so i will need to change to dao.

Thanks a lot.

i changed the to the with statement you sent and the %, now it works fine.

sam