OpenRecordset fails with thousands of records?

[Banquo_ws]

Greetings,

If I use the CurrentDb.OpenRecordset(sql) and what I'm looking is in the first row it finds it.
But if its its in row 5000 of 22000, it doens't find it.

Why is that?






sSql = "SELECT " & _
"xps.[Partner or XPS Account Name], " & _
"xps.[Account Name], " & _
"xps.[Serial Number], " & _
"xps.[3rd Party Manufacturer Serial Number], " & _
"xps.[Offering], " & _
"xps.[Price Plan], " & _
"xps.[Last Bill Date], " & _
"xps.[Current Read Date] " & _
"FROM xps " & _
"WHERE xps.[Last Bill Date] = 0 Or xps.[Last Bill Date] < " & sDate

Set rs = CurrentDb.OpenRecordset(sSql)

If Not rs.EOF Then


rs.MoveFirst
Dim sql, market As String


sql = "SELECT ALL [Responsibility Name], [Serial Number] FROM mps"
Set rec = CurrentDb.OpenRecordset(sql)

Do Until rs.EOF

market = "[Serial Number] = " & "'" & rs.Fields("[Serial Number]") & "'"
Dim val, val2 As String
With rec
.FindFirst market
If .NoMatch Then
MsgBox ("No Records Found!")
.MoveFirst
Else
val = rec.Fields("[Serial Number]")
val2 = rec.Fields("[Responsibility Name]")
End If
End With




The only thing I suspect, after 'watching' the recordset is the ODBCFetchCount attribute being set to 100 ?????

[jonh]

I don't know, but why not join the tables to find if records match?


Private Sub eg_Click()
GetRecs True
GetRecs False
End Sub

Private Sub GetRecs(nomatch As Boolean)
Dim rs As DAO.Recordset

s = "SELECT " & _
" mps.[Responsibility Name], " & _
" $.[Serial Number]" & _
"FROM xps " & _
"LEFT JOIN mps ON xps.[Serial Number] = mps.[Serial Number] " & _
"WHERE " & _
" xps.[Last Bill Date]=0 Or " & _
" xps.[Last Bill Date]<#%# " & _
" AND " & _
" mps.[Serial Number] Is £ Null "

s = Replace(s, "%", Date)

If nomatch Then
Debug.Print "missing serials"
s = Replace(s, "£", "")
s = Replace(s, "$", "xps")
Else
Debug.Print "matching serials"
s = Replace(s, "£", "not")
s = Replace(s, "$", "mps")
End If

Set rs = CurrentDb.OpenRecordset(s)
Do Until rs.EOF
Debug.Print rs("Serial Number")
rs.MoveNext
Loop
End Sub