Help needed with ADODB.Command

  • Thread starter Thread starter 8-bit
  • Start date Start date
8

8-bit

Hi all,

I'm fairly new to this so if I don't make sense I apologise. I'm
trying to use the Command object to access a Access DB from Excel - as
per the code below in my PS.

My problem is this: I need the RecordCount property - so I need to
change the CursorType - to adOpenStatic? Do I somehow tell Command or
RecSet?

Everything else works - but the .RecordCount returns -1 which my book
says is the default. I need to know if there are any records in the
recordset.

Any help would be appreciated.

Regards,
8-bit

PS. This is my code...

Set oCmd = New ADODB.Command
oCmd.ActiveConnection = oConn

sSQL = "SELECT * FROM table; "

oCmd.CommandType = adCmdText
oCmd.CommandText = sSQL

Set RecSet = oCmd.Execute

PPS. Please reply to group as the email I've used is bogus.
 
Hi 8-bit,

A couple of options for you:

1) use a client-side cursor, which will enable you to use the RecordCount
property:

oConn.CursorLocation=adUseClient

2) if you want to use a server-side cursor, you'll have to jump to the end
of the Recordset (which will in itself require a dynamic-type Recordset)
using MoveLast, then get the count via RecordCount, then MoveFirst again


Option 1 is much simpler.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
...
use a client-side cursor, which will enable you to
use the RecordCount property
Click to expand...

Using a client-side cursor enables use the RecordCount property
because the resulting recordset 'inherits' the cursor's CursorLocation
property and automatically gets a static cursor type. So it is the
recordset's cursor type, not cursor location, that determines whether
RecordCount is available.
if you want to use a server-side cursor, you'll have
to jump to the end of the Recordset...
Click to expand...

I think you are mistaken. The ADO 2.7 help states:

"If the Recordset object supports approximate positioning or
bookmarks... this value will be the exact number of records in the
Recordset, regardless of whether it has been fully populated."

I assume you are thinking of the bad old days of DAO e.g. from the DAO
3.6 help:

"The RecordCount property doesn't indicate how many records are
contained in a dynaset-, snapshot-, or forward-only–type Recordset
object until all records have been accessed. Once the last record has
been accessed, the RecordCount property indicates the total number of
undeleted records in the Recordset or TableDef object."
... (which will in itself require a dynamic-type
Recordset)
Click to expand...

The RecordCount is always available with Static and Keyset cursors.
According to the help, whether a dynamic cursor supports RecordCount
is determined by the provider. With Jet (MS Access, Excel, etc) using
a server-side cursor, the RecordCount property is indeed available and
Jet doesn't even support dynamic cursors!

Jamie.

--
 
Hi 8-bit,
Click to expand...

Hi Jake,
A couple of options for you:

1) use a client-side cursor, which will enable you to use the RecordCount
property:

oConn.CursorLocation=adUseClient
Click to expand...
Many thanks, that worked well, I am now able to get the recordcount.

I'm still a little confused over the whole issue but, I guess I will
get there in the end.

Regards,
8-bit
 
Back
Top