Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2011
    Location
    Houston, TX
    Posts
    32

    Question Unanswered: VBA - Recordset.Open not retrieving records

    this is probably a very simple problem, and I'm probably a doofus for not being able to fix it.

    Okay. I have this saved query and when I run it manually, it brings up like 12 records.

    But when I do this:

    Code:
    Sub test()
        Dim rst As ADODB.Recordset
        Dim conn As ADODB.Connection
        
        Set conn = CurrentProject.Connection
        Set rst = New ADODB.Recordset
        
        
        rst.Open "SELECT * FROM Query6", conn, adOpenKeyset, adLockBatchOptimistic, adCmdText
        Debug.Print rst.RecordCount
        
    End Sub
    I get NOTHING. It pulls records just dandily from my other two queries. I can't figure out why this one in particular isn't working when it's written almost exactly the same way.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    IIRC recordcount isn't populated until you get to the last row in the recordset
    you can test if rows have been returned by checking to see if the BOF and EOF propertied are true
    BOF, EOF Properties (ADO)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2011
    Location
    Houston, TX
    Posts
    32

    ...

    Code:
    IIRC recordcount isn't populated until you get to the last row in the recordset
    you can test if rows have been returned by checking to see if the BOF and EOF propertied are true
    This doesn't answer the question at all. It doesn't even approach answering the question. Either you didn't read my question, or you're just trying to bloat your post count.

    I know how to check BOF and EOF

    My question is that I have two other queries that are written identically the same manner that return the correct number of records to these recordsets, while this one in particular does not, and I can't imagine a reason why.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK I'll phrase it another way
    The Recordcount property isn't set unless you do a movefirst / movelast
    RecordCount Property*[Access 2007 Developer Reference]

    however as that page states
    Using the MoveLast method to populate a newly opened Recordset negatively impacts performance. Unless it is necessary to have an accurate RecordCount as soon as you open a Recordset, it's better to wait until you populate the Recordset with other portions of code before checking the RecordCount property.
    that page suggest you use the SQLcount property to give an approximation orf the number of rows in a recordset

    so your debug.print is worthless

    the reason I suggested testing the BOF and EOF is to see if there are rows in your recordset. its a relatively painless way of finding out if there are rows in your recordset, something which your original post said you were interested in.

    And for the record I actually don't give a flying whatever about the number of posts here there or anywhere. its irrelevant to me. but it helps you to think so feel free

    I suppose you have checked that if you run Query6 outside of VB code and see if it returns rows
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2011
    Location
    Houston, TX
    Posts
    32

    um yeah

    I hate to disagree with you but I have done rst.Open to load up a recordset and output the record count innumerable times without first doing a MoveFirst / MoveLast. I'm actually not sure where you're getting that from...

    Is there any difference between the behavior of recordsets in VBA vs just straight VB6? Because I don't know why there would be, but I'm using VBA.

    Also, yes the query does return rows when I run it from the DB interface.

    It's really weird, this problem.

    Also, I'm not sure what you meant by "IIRC recordcount"
    Last edited by asherman86; 04-05-13 at 16:40. Reason: one other question

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the reference (although for A2007) is where that info comes form. it has been my understanding that this has always been the case in Access, AND I think VB6 and earlier

    IIRC is If I Remember Correctly
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2011
    Location
    Houston, TX
    Posts
    32
    I guess that's just why I was perplexed, because I have never ever ever ever EVER had to MoveFirst/MoveLast to get the recordcount and I've been programming in VBA since like... 2008. I'm gonna have a look at that documentation though.

    LOL Microsoft documentation. Not always the most helpful is it?! Nope, not always.


    But ANYWAY in all my efforts to solve teh problem, I eventually just went about it another way - an EASY WAY. I couldn't believe how ridiculous I was being. I literally just took a machete to that code and reduced about 100 lines of code to like... 5. Literally. > 100 lines of code reduced to five.

    I both love and despise Microsoft Access.

Posting Permissions

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