Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Houston, TX

    Question Unanswered: cmd.Execute not filling recordset

    OKAY. This is probably really really simple:

    Set conn = CurrentProject.Connection
        Set cmd = New ADODB.Command
        Set cmd.ActiveConnection = conn
        With cmd
            .Properties("Jet OLEDB:Stored Query") = True
            .CommandText = "qryDATAMAP-ALL"
        End With
        Set rst = New ADODB.RecordSet
        Set rst = cmd.Execute()
        Debug.Print rst.RecordCount
    I don't know why but this thing is returning -1 for record count.

    I use almost this exact same code in a function I wrote for an Excel workbook, and it works FLAWLESSLY when I'm connecting to the database externally. But, when I do it internally, I can't seem to pull any records from this query.

    That query name is RIGHT by golly. Please, someone slap me upside the head with a fish and jog the probably obvious answer loose from my brain.


  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 18
    There is a known problem with ADO recordsets returning -1 for the recordcount. I used to have a link to a website that explained it, and (I think) gave a work-around for it, but that's on my old PC. I can have a look over the weekend if you like?
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  3. #3
    Join Date
    Nov 2011
    Houston, TX

    Thumbs up Workaround

    Nah, there's no need for that now; I just re set the recordset with the exact queries I needed.

    But, I guess it would be good for posterity.

    What I did:
    'I don't have the code with me so let's see if I can remember. 
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Set db = CurrentDb
    Set rs = db.Openrecordset("Select * FROM QueryWhatever WHERE ColumnA LIKE 'somethingorother*'")
    Debug.Print rs.RecordCount
    I'm pretty sure it was something like that. I scrapped the ADO objects and used DAO instead. For some reason using DAO when you're working within Access just makes more sense to me....being Data Access Objects and all.

    Like I said, the ADO objects work fine from Excel....when you're inside Access, using DAO just seems simpler.
    Last edited by asherman86; 03-08-13 at 21:27.

Posting Permissions

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