I'm creating a conversion routine to create a backend SQL Server database from some legacy imported files. I first wrote and tested the conversion routines using Access tables with a small input set of data.
Once the routines were debugged I linked to the SQL tables instead of Access tables.
Since the conversion runs so long I used the recordcount property of the Access table to show a status on the screen as the conversion progressed.
My conversion loops all look similar to this:
Dim rsOut As ADODB.Recordset
Set rsOut = New ADODB.Recordset
rsOut.Open outputTable, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Dim rsIn2 As ADODB.Recordset
Set rsIn2 = New ADODB.Recordset
rsIn2.Open inputTable, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rsIn2.EOF Then
' If rsIn2.RecordCount > 0 Then
wkTotRecs = rsIn2.RecordCount
While Not rsIn2.EOF
wkRecCnt = wkRecCnt + 1
' rsOut![ID] = Nz(rsIn2![ID], 0) ' let this one assign it's own ID to link to the detail records
' End If 'recordcount > 1
Set rsIn2 = Nothing
Set rsOut = Nothing
The same loops that worked against the Access tables didn't work against the SQL tables. As I deguugged I found that the recordcount property always seems to be set to -1, regardless of how many records are in the recordset.
I had to change my loops not to be based on the recordcount being greater than 0. That was easy enough.
My question is:
Since the recordcount property doesn't seem to be supported: How to get the number of returned records from a SQL recordset?