Querydef Error

[Paul]

Hello,

I am trying to open a DAO recordset with a parameter query, but I am getting
the following error message:

"The Microsoft Jet database engine could not find the object '#Temporary
QueryDef#'. Make sure the object exists and that you spell its name and path
name correctly."

The following is my code where the error is generated in the last line of
the With block statement.

mstrSql = "PARAMETERS lngPrmCustId Long, lngPrmCompanyId Long; " & _
"SELECT lngCustId, lngCompanyId " & _
"FROM tblCustomer " & _
"WHERE lngCustId = [lngPrmCustId] AND " & _
"lngCompanyId <> [lngPrmCompanyId];"

Set mdb = CurrentDb()
Set mqdf = mdb.CreateQueryDef("", mstrSql)
With mqdf
.Parameters(0).Value = Me.txtCustomer
.Parameters(1).Value = Me.txtCompanyId
.SQL = mstrSql
Set mrs = .OpenRecordset(dbOpenTable)
End With

I am using Access 2003 SP 2 on Win XP SP 2. Any help would be appreciated.

Regards,

Paul


~ The views expressed here are mine and do not reflect the official opinion
of my employer or the organization through which the Internet was accessed.

 

[George Nicholson]

From the Help entry for OpenRecordset:

"For QueryDef, Recordset, and TableDef objects:
" Set recordset = object.OpenRecordset (type, options, lockedits)
"....
"In a Microsoft Jet workspace, if object refers to a **QueryDef** object,
or a dynaset- or snapshot-type Recordset,
"or if source refers to an SQL statement or a TableDef that represents a
linked table,
" you **can't use dbOpenTable for the type argument** ; if you do, a
run-time error occurs."

FWIW, the code example for the Parameters Collection Help entry uses
CreateQueryDef with a ForwardOnly Type, so I presume they work together, but
Dynaset and Snapshot probably do too. Been a while since I've visited the
issue and my brain has already left for the day.