3065-Cannot execute select query--why not?

Discussion in 'Microsoft Access VBA Modules' started by John Sanders, May 7, 2004.

  1. John Sanders

    John Sanders Guest

    OK, I'm probably missing something basic here.

    I get a run-time error 3065, cannot execute a select query.

    The code seems to compile fine:


    ....
    Dim testqry As DAO.QueryDef
    Dim TestDB As DAO.Database
    Dim SQLString As String

    Stop
    SQLString = "SELECT * FROM " & Me.Recordset.Name & " WHERE (" &
    Me.Recordset.Name & _
    ".[employee name] = """
    SQLString = SQLString & "Jester"
    ' This will be replaced with a variable when I get it to execute
    SQLString = SQLString & """);"
    Debug.Print SQLString
    Set TestDB = CurrentDb
    Set testqry = TestDB.CreateQueryDef("", SQLString)
    testqry.Execute
    ...


    The generated SQL string is
    SELECT * FROM tbl_Employees WHERE (tbl_Employees.[employee name] =
    "Jester");


    When I save that string as a query outside the form, it will execute
    fine. Inside the form, it crashes on the testqry.execute line with the
    run-time error.

    I have as references:
    Visual Basic For Applications
    Microsoft Access 9.0 Object Library
    OLE Automation
    Microsoft DAO 3.6 Object Library

    I am running Access 2000 on a local machine with a local single-user
    database.


    Thanks for any help--this is getting me frustrated.

    John Sanders
    PersonalIT at Juno dot Com
     
    John Sanders, May 7, 2004
    #1
    1. Advertisements

  2. John Sanders

    Dirk Goldgar Guest

    The error message is actually telling you the whole story: You cannot
    "execute" a SELECT query. Only "action" queries -- append, delete,
    make-table queries -- can be executed. SELECT queries return data, so
    you have to use OpenRecordset to open a recordset on your query:

    Dim TestDB As DAO.Database
    Dim rs As DAO.Recordset ' <===***
    Dim SQLString As String

    SQLString = _
    "SELECT * FROM " & Me.Recordset.Name & _
    " WHERE (" & Me.Recordset.Name & ".[employee name] = """

    SQLString = SQLString & "Jester"
    ' This will be replaced with a variable when I get it to execute

    SQLString = SQLString & """);"

    Set TestDB = CurrentDb

    Set rs = TestDB.OpenRecordset(SQLString) ' <===***

    ' ... do stuff with rs ...

    rs.Close

    Set rs = nothing
    Set TestDB = Nothing
     
    Dirk Goldgar, May 7, 2004
    #2
    1. Advertisements

  3. John Sanders

    Dirk Goldgar Guest

    Answered in a different newsgroup, to which you posted this question
    independently. That's called "multiposting", and it's generally frowned
    on because others don't know what answers have already been given, and
    so they duplicate the effort. Also it's harder for you to keep track of
    the various replies, and it's harder for later readers of the question,
    who may be looking for the same answer, to learn what they need.

    In most cases a single, well-chosen newsgroup will do. If your question
    really is relevant to more than one newsgroup, the approved technique is
    to "crosspost" it instead, by listing multiple newsgroups in the To: or
    Newsgroups: line of a single message. If you do that, the message and
    any replies will appear in all the listed newsgroups automatically,
    which is beneficial to all concerned.
     
    Dirk Goldgar, May 7, 2004
    #3
  4. John Sanders

    Dirk Goldgar Guest

    No, I was wrong. Please disregard this unwarranted message.
     
    Dirk Goldgar, May 7, 2004
    #4
  5. John Sanders

    John Sanders Guest

    [snip]


    Thanks. I thought it was something basic :p
     
    John Sanders, May 7, 2004
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.