Using SQL to open Recordset, keep getting error 3011, could not find object

gaatjaat

New member
Local time
Today, 05:51
Joined
Jan 17, 2013
Messages
2
Please help me, this one line of code has cost me an entire day already.
I am using a SQL query to open a recordset in the following code:

Private Sub CommandInstructors_Click()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim IID As DAO.Recordset
Dim ClassNumber As Long
Dim strSQL As String
Dim CurrentRecordID As Long

Set dbs = CurrentDb

Me.ClassID = Me.ComboClassID.Column(0)
ClassNumber = Me.ClassID
strSQL = "SELECT [qryInstr].[ClassID], * " & _
"FROM qryInstr" & _
"WHERE (((qryInstr.[ClassID])= " & _
ClassNumber & "));"

Set IID = dbs.OpenRecordset(strSQL, dbOpenTable)

Every time I try to run it, it keeps giving me an

error 3011, MS Access could not find object SELECT [qryInstr].[ClassID], * FROM qryInstr WHERE (((qryInstr.[ClassID)=1));

I have tried every combination of using brackets, even simplifying the query so I might narrow down what is causing the error, but no luck so far. qryInstr is a SELECT query. I even created a query with everything from strSQL and it works perfect, so I feel completely lost on this. Any help will be greatly appreciated.

:banghead:
Gaatjaat
 
Try removing the "dbOpenTable" from this line:
Code:
Set IID = dbs.OpenRecordset(strSQL, dbOpenTable)

Just use:

Code:
Set IID = dbs.OpenRecordset(strSQL)
 
I thought I had tried that and got a different error, but I just now removed dbOpenTable and now I have a syntax error in FROM clause. Something I have not seen yet.
 
strSQL = "SELECT [qryInstr].[ClassID], * " & _
"FROM qryInstr" & _
"WHERE (((qryInstr.[ClassID])= " & _
ClassNumber & "));"

Is "qryInstr" the name of your table? You are trying to use "*" in the query definition string after you have already specified a specific field (ClassID). If you want to return all fields you can use the "*" otherwise you need to specify each field you want to return from your table.

Try:
Code:
strSQL = "SELECT * " & _
"FROM qryInstr" & _
"WHERE (((qryInstr.[ClassID])= " & _
ClassNumber & "));"
The statement above will return all fields.

or:
Code:
strSQL = "SELECT [qryInstr].[ClassID] " & _
"FROM qryInstr" & _
"WHERE (((qryInstr.[ClassID])= " & _
ClassNumber & "));"
The statemen above will only return the "ClassID" field.
 
I had an issue a while back where I was using asterisks (*) as wildcards in the WHERE clause of a query I was building in code and could not figure out what was going on. I ended up replacing the * with % and it resolved the problem. I do not know if that would work for an asterisk wildcard in the SELECT clause of the query.
 
I ended up replacing the * with % and it resolved the problem.

DAO objects need the * character for a wild card in a LIKE query while ADO objects need the % character.

SQL BE DB's usually prefer the % character for a wild card in a LIKE query as well. This can show up in Access code which executes a Pass-Through query.

I do not know if that would work for an asterisk wildcard in the SELECT clause of the query.

SELECT * is always SELECT *, no matter ADO / DAO / Access / SQL BE DB
 
Thanks Michael. I never could find an adequate explanation of why the % worked in my code.
 

Users who are viewing this thread

Back
Top Bottom