ADODB RecordCount

[Deleted member 73419]

Hi,
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

    rst.MoveLast
    
    Debug.Print "Recordset recordcount: " & rst.RecordCount
    
    rst.Close
    conn.Close
    Set rst = Nothing
    Set conn = Nothing

End Sub

Can anyone help?

Many thanks

 

[Trevor G]

Look at this thread, there is a working option here

http://www.access-programmers.co.uk/forums/showthread.php?t=26588

Function recordCount1()
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSQL As String
Set rst = New ADODB.Recordset
Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
strSQL = "SELECT * from tblemployee"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
Debug.Print rst.RecordCount
Set cnn = Nothing
Set rst = Nothing
End Function

 

[Deleted member 73419]

Ah ha, it now seems to be working ok!!

Thanks Trevor

 

[LPurvis]

Very quick bit of background...
By opening the recordset using the Execute method of a command or connection object (cmd.Execute) you are acquiring a ForwardOnly recordset cursor type.
It doesn't support the RecordCount property.
Even though you were attempting to request other types, such as Static, you always got ForwardOnly.
The alternative you've moved to (using the Open method) allows for all supported types to be enforced.

Just to mention that your connection code:
conn.Open CurrentProject.Connection
is opening an unnecessary connection. Unless you're wanting something very specific with the connection, then
Set conn = CurrentProject.Connection
should be perfectly adequate and require less overhead.

Cheers.

 

[blah]

The alternative you've moved to (using the Open method) allows for all supported types to be enforced.

well, wdik but using theRecordset.open (blah) and then ?theRecordset.recordcount is useless as well.

but as I said, wdik?

 

[LPurvis]

theRecordset.open (blah)

Blah? It depends greatly on the content of the Blah.
Directly as above (i.e. a Keyset cursor) there should always be a RecordCount available. It depends on "Blah" and what you mean by "useless".

 

[jdraw]

Further to Leigh's note, here's a link to M$oft description

http://msdn.microsoft.com/en-us/library/ms676701(VS.85).aspx

 

[blah]

Blah? It depends greatly on the content of the Blah.
Directly as above (i.e. a Keyset cursor) there should always be a RecordCount available. It depends on "Blah" and what you mean by "useless".

for example, blah = "SELECT * FROM someTable WHERE theMoon = 'blue';"

as previously remarked, wdik? All i know is it seems like theRecordset.recordcount ought to give me something besides -1. It's frustrating when it doesn't. But DAO usually does, and that makes me content.

I fully admit to being somewhat behind the curve on the significance of cursors.

After all, I'm an office user. I'm supposed to be clueless.

@jdraw: thx.