If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
ADO query EOF problem
"MikeV06" wrote in message
... 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 I believe your problem lies he rstCorpNames.Open strSQL, _ cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdText Recs = rstCorpNames.RecordCount 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 -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
Thread Tools | |
Display Modes | |
|
|