I've been trying to count the number of records in a query that I'm
pulling back to Excel from Access. The query returns about 13K
records, but my recordcount keeps coming back as -1.

What am I doing wrong here?

Thanks

Public Sub SelectFromAccess()

Dim rsData As ADODB.Recordset
Dim sPath As String
Dim sConnect As String
Dim sSQL As String

' Clear the destination worksheet.
Sheet1.UsedRange.Clear

' Get the database path (same as this workbook).
sPath = ThisWorkbook.Path
If Right$(sPath, 1) < "\" Then sPath = sPath & "\"

' Create the connection string.
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & "TestExcelData.mdb;"

' Build the SQL query.
sSQL = "SELECT [CM Data Store].* FROM [CM Data Store];"

' Retrieve the data using ADO.
Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, _
adOpenForwardOnly, adLockReadOnly, adCmdText

If Not rsData.EOF Then
Sheet1.Range("A2").CopyFromRecordset rsData
Else
MsgBox "No data located.", vbCritical, "Error!"
End If

'Get the record count
MsgBox rsData.RecordCount

rsData.Close
Set rsData = Nothing

End Sub

Try doing a rsdata.movelast before getting the record count.

" wrote:

I've been trying to count the number of records in a query that I'm
pulling back to Excel from Access. The query returns about 13K
records, but my recordcount keeps coming back as -1.

What am I doing wrong here?

Thanks

Public Sub SelectFromAccess()

Dim rsData As ADODB.Recordset
Dim sPath As String
Dim sConnect As String
Dim sSQL As String

' Clear the destination worksheet.
Sheet1.UsedRange.Clear

' Get the database path (same as this workbook).
sPath = ThisWorkbook.Path
If Right$(sPath, 1) < "\" Then sPath = sPath & "\"

' Create the connection string.
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & "TestExcelData.mdb;"

' Build the SQL query.
sSQL = "SELECT [CM Data Store].* FROM [CM Data Store];"

' Retrieve the data using ADO.
Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, _
adOpenForwardOnly, adLockReadOnly, adCmdText

If Not rsData.EOF Then
Sheet1.Range("A2").CopyFromRecordset rsData
Else
MsgBox "No data located.", vbCritical, "Error!"
End If

'Get the record count
MsgBox rsData.RecordCount

rsData.Close
Set rsData = Nothing

End Sub



Use static instead of forward-only cursor:

' Retrieve the data using ADO.
Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, _
adOpenStatic, adLockReadOnly, adCmdText

--
urkec