Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2008
    Posts
    6

    Unanswered: Query vs Recordset produces different results

    I'm running code that creates a recordset which produces 1 record. However, when I take the SQL statement which creates the recordset and paste it in a query (in QBE) I get two records which is what I am expecting. Part of the code follows. I have a Debug pause at "Do While Not .EOF", and when I check, the RecordCount=1. I'm really stumped. Any suggestion as to why I would have this issue? I'm running Access 2003 on XP Pro, but the database is actually 2000.

    jsSQL = "SELECT qryMnthlyForeignCallsCheck.[Activity_PK]"
    jsSQL = jsSQL & ", qryMnthlyForeignCallsCheck.[APO_FPO_Address]"
    jsSQL = jsSQL & ", qryMnthlyForeignCallsCheck.[DSNPhone]"
    jsSQL = jsSQL & ", qryMnthlyForeignCallsCheck.[Phone]"
    jsSQL = jsSQL & " FROM qryMnthlyForeignCallsCheck WHERE qryMnthlyForeignCallsCheck.[Country] Is Null"

    Set joRS = New ADODB.Recordset
    With joRS
    .ActiveConnection = CurrentProject.Connection
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    .Open jsSQL

    Do While Not .EOF

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums

    When you set the rs, Access only thinks there is the one record that it is currently on (the first one). To get around this, use the .movenext action. Hope that gets you closer at least.

    See Allen Browne's site for more help if needed.
    Me.Geek = True

  3. #3
    Join Date
    Sep 2008
    Posts
    6
    Thanks, nckdryr. I just didn't show the .MoveNext. The fact is before I even start processing the recordset, I check .RecordCount and it only shows 1 record when I should be getting 2 records. It just seems to me any recordset you create in code should be the same as if you take that same SQL and create a query in QBE. It's probably some esoteric thing with Access (query vs recordset) I just haven't found out yet.

    Thanks,

    G

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    gjglick,

    nckdryr is right. From MSDN (http://msdn.microsoft.com/en-us/library/bb208624.aspx):
    Use the RecordCount property to find out how many records in a Recordset or TableDef object have been accessed. The RecordCount property doesn't indicate how many records are contained in a dynaset–, snapshot–, or forward–only–type Recordset object until all records have been accessed. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast method on the Recordset object. You can also use an SQL Count function to determine the approximate number of records your query will return.
    Have a nice day!

  5. #5
    Join Date
    Sep 2008
    Posts
    6
    Not true when you use .CursorType = adOpenStatic. I have a function I use all the time to get record counts and I don't use MoveLast, I just open the recordset and reference .RecordCount. But I do apologize on my inconsistancies. I actually stepped thru my recordset processing loop and indeed there is only 1 record when I should be getting 2 records.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    In ADO, I just open the recordset as such:

    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyTable"
    rs.open strsql,currentproject.connection,adopenkeyset,adlo ckreadonly
    msgbox "Recordset count = " & rs.recordcount
    rs.close
    set rs = nothing

    Note:
    rs.open strsql,currentproject.connection,adopenkeyset,adlo ckreadonly - > to open it non-updatable and quickly for things such as recordcount
    rs.open strsql,currentproject.connection,adopendynamic,adl ockoptimistic - > to open it updatable.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Sep 2008
    Posts
    6
    pkstormy, you appear to have a lot of experience. Forgetting about code structure, etc, have you ever come across a situation where you're expecting, say, 2 records in a recordset but only 1 shows up. So you do the logical thing and grab the SQL statement that builds the recordset and paste it in a query in Access's QBE to check that the right data is being retrieved. And your query in QBE window shows the 2 records you're expecting. That is my situation and I am stumped as to why it is occurring. I assume it has to do with the saved query I'm using (qryMnthlyForeignCallsCheck). I think people are getting sidetracked from my real problem.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Are you sure your .recordcount in your recordset is only returning 1 record?

    If opening an ADO recordset as adopenstatic and adlockoptimistic, you typically need an rs.movelast to then get the correct .recordcount. (typically). I usually don't do this though and instead use the adopenkeyset and adlockreadonly which I know returns the correct recordcount.

    If you get -1 for a recordcount in ADO, then you're not opening the recordset correctly to get a recordcount. I noticed your opening the recordset as adopenstatic and adopendynamic. Try opening it as adopenkeyset as your cursortype and adlockreadonly as your locktype parameters. (note: the recordset won't be updatable but you'll at least see if the recordcount is correct using a msgbox "My Recordcount = " & joRS.recordcount)

    Otherwise, if you're using the adopenstatic and adopendynamic,

    Try adding a message box in your looping routine:
    ex:
    ....
    Set joRS = New ADODB.Recordset
    With joRS
    .ActiveConnection = CurrentProject.Connection
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    .Open jsSQL

    joRS.movefirst
    Do While Not .EOF
    msgbox "Record here: " & joRS!MyPrimaryKeyField.
    joRS.movenext

    loop
    .....

    or (which may or may not work with the way you're currently opening the recordset)...

    joRS.movefirst
    joRS.movelast
    msgbox "Record count: " & joRS.recordcount

    If the later doesn't give you a true recordcount, again, try opening the recordset as suggested.

    Note also: you're using jsSQL = jsSQL & " FROM qryMnthlyForeignCallsCheck WHERE qryMnthlyForeignCallsCheck.[Country] Is Null"

    Sometimes an Is Null test can produce varying results where I've seen that a field isn't actually null but instead, has a blank value (which SHOULD be the same but sometimes doesn't recognize a Null value versus a blank value. These are rare but you can also utilize the .[Country] = "" to test as well. You can also test by len(.[Country])> 0 as another test.

    I rarely use adopenstatic and adlockOptimistic together. Again, these are the 2 typical recordsets I use:
    rs.open strsql,currentproject.connection,adopenkeyset,adlo ckreadonly - > to open it non-updatable and quickly for things such as recordcount
    rs.open strsql,currentproject.connection,adopendynamic,adl ockoptimistic - > to open it updatable (but no recordcount)
    Last edited by pkstormy; 02-05-10 at 11:05.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Sep 2008
    Posts
    6
    I've tried every combination. Please ignore .RecordCount, it's not significant. My code logic is similiar to yours (see below). My debug statement only prints one key (record), yet if I take the contents of jsSQL and paste it in the QBE window and run it, I get both records I'm expecting.


    jsSQL = "SELECT qryMnthlyForeignCallsCheck.[Activity_PK]"
    jsSQL = jsSQL & ", qryMnthlyForeignCallsCheck.[APO_FPO_Address]"
    jsSQL = jsSQL & ", qryMnthlyForeignCallsCheck.[DSNPhone]"
    jsSQL = jsSQL & ", qryMnthlyForeignCallsCheck.[Phone]"
    jsSQL = jsSQL & " FROM qryMnthlyForeignCallsCheck WHERE qryMnthlyForeignCallsCheck.[Country] Is Null"

    Set joRS = New ADODB.Recordset
    With joRS
    .ActiveConnection = CurrentProject.Connection
    .CursorType = adOpenKeyset
    .LockType = adLockReadOnly
    .Open jsSQL

    Do While Not .EOF
    jiRead = jiRead + 1
    Debug.Print .Fields(0) & ": " & jsMsg

    '-- Some processing...

    .MoveNext
    Loop

    .Close
    End With


    What follows is the the design of the query (qryMnthlyForeignCallsCheck) I'm using. I suspect it's something to do with my WHERE clause that causes it to behave differently in ADO than in the QBE.


    SELECT qryMnthlyCallsRpt.Call_PK, tblCallerID.LName, tblCallerID.FName, tblActivity.Activity_PK, tblActivity.ActName, tblActivity.APO_FPO_Address, tblActivity.Address1, tblActivity.Address2, tblActivity.Address3, tblActivity.City, tblActivity.State, tblActivity.Zipcode, tblActivity.Country, tblCallerID.DSNPhone, tblCallerID.Phone
    FROM tblActivity INNER JOIN (tblCallerID INNER JOIN qryMnthlyCallsRpt ON tblCallerID.Caller_PK=qryMnthlyCallsRpt.Caller_FK) ON tblActivity.Activity_PK=qryMnthlyCallsRpt.Activity _FK
    WHERE (((tblActivity.Address1) Like "FPO*" Or (tblActivity.Address1) Like "APO*")) Or (((tblActivity.Address2) Like "FPO*")) Or (((tblActivity.Address2) Like "APO*")) Or (((tblActivity.Address3) Like "FPO*")) Or (((tblActivity.Address3) Like "APO*")) Or (((tblActivity.City) Like "FPO*")) Or (((tblActivity.City) Like "APO*")) Or (((tblActivity.Zipcode) Like "FPO*")) Or (((tblActivity.Zipcode) Like "APO*")) Or (((Left(tblCallerID.DSNPhone & " ",3)) In ('313','314','315','316','318'))) Or (((tblCallerID.Phone) Like '01*')) Or (((tblActivity.Country)<>'US' And (tblActivity.Country) Is Not Null));

  10. #10
    Join Date
    Sep 2008
    Posts
    6
    Found the problem. I consider it a bug in MS-Access, Microsoft would consider it an enhancement. My underlying query, qryMnthlyForeignCallsCheck, uses wildcard * (asterisk) for some filtering. I know from experience when building and executing SQL in code, I have to use % as the wildcard character. BUT, one would expect a canned query to execute the same either way. Go figure.

    Thanks for the feedback from all.

    G

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •