3065-Cannot execute select query--why not?

[John Sanders]

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

 

[Dirk Goldgar]

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

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]

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]

Answered in a different newsgroup, to which you posted this question
independently.

No, I was wrong. Please disregard this unwarranted message.

 

[John Sanders]

OK, I'm probably missing something basic here.

[snip]
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

[snip]


Thanks. I thought it was something basic