ADO query EOF problem

M

MikeV06

The below Sub returns 2 columns and 3 records. The open shows Recs = 3. The
If statement to move through the recordset returns the name in the first
row but does not move through the next 2 rows. Evidently it thinks it is
EOF and quits. I tried Not .EOF, but it also only returned the first row.
What am I doing wrong. I am using Access 2003.

Thank you, Mike.

Public Sub Retrieve_Test_2()
' ADO - ActiveX Data Objects Architecture directly supported.
' Provides a common set of objects across any data engine including
' SQL Server
Dim cnThisConnect As ADODB.Connection
Dim rstCorpNames As ADODB.Recordset
Dim fldEach As ADODB.Field
Dim strSQL As String
Dim CName As String
Dim Cnt, Recs
Set cnThisConnect = CurrentProject.Connection
Cnt = 1
pstrco = "'1000000001'"
Set rstCorpNames = New ADODB.Recordset
strSQL = "SELECT [tblCorp Name].Corp, " & _
"[tblCorp Name].CorpName FROM [tblCorp Name]" & _
"ORDER BY [tblCorp Name].Corp"
rstCorpNames.Open strSQL, _
cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdText
Recs = rstCorpNames.RecordCount
MsgBox ("Record Count " & Recs)
MsgBox ("rstCorpNames(1) " & rstCorpNames.Fields(1).Name)
For Each fldEach In rstCorpNames.Fields
MsgBox fldEach.Name
Next
rstCorpNames.MoveFirst
If Cnt < Recs Then
CName = rstCorpNames!CorpName
MsgBox ("CorpName = " & CName)
rstCorpNames.MoveNext
Cnt = Cnt + 1
MsgBox ("Cnt & Recs = " & Cnt & " " & Recs)
End If
rstCorpNames.Close
Set rstCorpNames = Nothing
End Sub
 
D

Dirk Goldgar

MikeV06 said:
The below Sub returns 2 columns and 3 records. The open shows Recs = 3.
The
If statement to move through the recordset returns the name in the first
row but does not move through the next 2 rows. Evidently it thinks it is
EOF and quits. I tried Not .EOF, but it also only returned the first row.
What am I doing wrong. I am using Access 2003.

Thank you, Mike.

Public Sub Retrieve_Test_2()
' ADO - ActiveX Data Objects Architecture directly supported.
' Provides a common set of objects across any data engine including
' SQL Server
Dim cnThisConnect As ADODB.Connection
Dim rstCorpNames As ADODB.Recordset
Dim fldEach As ADODB.Field
Dim strSQL As String
Dim CName As String
Dim Cnt, Recs
Set cnThisConnect = CurrentProject.Connection
Cnt = 1
pstrco = "'1000000001'"
Set rstCorpNames = New ADODB.Recordset
strSQL = "SELECT [tblCorp Name].Corp, " & _
"[tblCorp Name].CorpName FROM [tblCorp Name]" & _
"ORDER BY [tblCorp Name].Corp"
rstCorpNames.Open strSQL, _
cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdText
Recs = rstCorpNames.RecordCount
MsgBox ("Record Count " & Recs)
MsgBox ("rstCorpNames(1) " & rstCorpNames.Fields(1).Name)
For Each fldEach In rstCorpNames.Fields
MsgBox fldEach.Name
Next
rstCorpNames.MoveFirst
If Cnt < Recs Then
CName = rstCorpNames!CorpName
MsgBox ("CorpName = " & CName)
rstCorpNames.MoveNext
Cnt = Cnt + 1
MsgBox ("Cnt & Recs = " & Cnt & " " & Recs)
End If
rstCorpNames.Close
Set rstCorpNames = Nothing
End Sub
Click to expand...



I believe your problem lies here:
rstCorpNames.Open strSQL, _
cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdText
Recs = rstCorpNames.RecordCount
Click to expand...

When the recordset is first opened, the RecordCount property is not
reliable. Normally, it only shows the number of records that have been
accessed so far, which is usually 0 (for an empty recordset) or 1 (for the
first record). It may also be -1, if ADO is unable to determine the number
of records at this time.

The RecordCount property only becomes reliable when you have traversed the
entire recordset. So if you need to know *before looping* how many records
there are, you would do this:

With rstCorpNames

.Open strSQL, _
cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdText

If Not .EOF Then
.MoveLast
Recs = .RecordCount
.MoveFirst
End If

End With

More often, though, you don't need to know in advance how many records there
are, and you can just loop until .EOF:

With rstCorpNames

.Open strSQL, _
cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdText

Do Until .EOF Then
Cnt = Cnt + 1
CName = !CorpName
MsgBox ("CorpName = " & CName)
.MoveNext
Loop

.Close

End With
 
M

MikeV06

MikeV06 said:
The below Sub returns 2 columns and 3 records. The open shows Recs = 3.
The
If statement to move through the recordset returns the name in the first
row but does not move through the next 2 rows. Evidently it thinks it is
EOF and quits. I tried Not .EOF, but it also only returned the first row.
What am I doing wrong. I am using Access 2003.

Thank you, Mike.

Public Sub Retrieve_Test_2()
' ADO - ActiveX Data Objects Architecture directly supported.
' Provides a common set of objects across any data engine including
' SQL Server
Dim cnThisConnect As ADODB.Connection
Dim rstCorpNames As ADODB.Recordset
Dim fldEach As ADODB.Field
Dim strSQL As String
Dim CName As String
Dim Cnt, Recs
Set cnThisConnect = CurrentProject.Connection
Cnt = 1
pstrco = "'1000000001'"
Set rstCorpNames = New ADODB.Recordset
strSQL = "SELECT [tblCorp Name].Corp, " & _
"[tblCorp Name].CorpName FROM [tblCorp Name]" & _
"ORDER BY [tblCorp Name].Corp"
rstCorpNames.Open strSQL, _
cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdText
Recs = rstCorpNames.RecordCount
MsgBox ("Record Count " & Recs)
MsgBox ("rstCorpNames(1) " & rstCorpNames.Fields(1).Name)
For Each fldEach In rstCorpNames.Fields
MsgBox fldEach.Name
Next
rstCorpNames.MoveFirst
If Cnt < Recs Then
CName = rstCorpNames!CorpName
MsgBox ("CorpName = " & CName)
rstCorpNames.MoveNext
Cnt = Cnt + 1
MsgBox ("Cnt & Recs = " & Cnt & " " & Recs)
End If
rstCorpNames.Close
Set rstCorpNames = Nothing
End Sub
Click to expand...

I believe your problem lies here:
rstCorpNames.Open strSQL, _
cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdText
Recs = rstCorpNames.RecordCount
Click to expand...

When the recordset is first opened, the RecordCount property is not
reliable. Normally, it only shows the number of records that have been
accessed so far, which is usually 0 (for an empty recordset) or 1 (for the
first record). It may also be -1, if ADO is unable to determine the number
of records at this time.

The RecordCount property only becomes reliable when you have traversed the
entire recordset. So if you need to know *before looping* how many records
there are, you would do this:

With rstCorpNames

.Open strSQL, _
cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdText

If Not .EOF Then
.MoveLast
Recs = .RecordCount
.MoveFirst
End If

End With

More often, though, you don't need to know in advance how many records there
are, and you can just loop until .EOF:

With rstCorpNames

.Open strSQL, _
cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdText

Do Until .EOF Then
Cnt = Cnt + 1
CName = !CorpName
MsgBox ("CorpName = " & CName)
.MoveNext
Loop

.Close

End With
Click to expand...

Most embarrassing. I have been mentally thinking the IF statement was a DO
UNTIL -- which obviously it is not. Arg...

As you said it would, this works just fine

rstCorpNames.Open strSQL, _
cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdText
Recs = rstCorpNames.RecordCount
MsgBox ("Record Count " & Recs)
With rstCorpNames
Do Until .EOF
Cnt = Cnt + 1
CName = !CorpName
MsgBox ("CorpName = " & CName)
MsgBox ("Cnt & Recs = " & Cnt & " " & Recs)
.MoveNext
Loop
.Close
End With

Thank you again, you have been most helpful.

Mike
 

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

Similar Threads

Select statement to run Access Query from VBA 2

Top