Why am I getting a recordcount of -1

  • Thread starter the.flash.flood.editor
  • Start date
T

the.flash.flood.editor

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
 
U

urkec

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top