Openquery and execute not recognizing parameters

[Bill Murphy]

I'm storing the SQL code for certain queries in a memo field in a table and
executing this code in VBA. This code creates a query with CreateQueryDef
using the SQL code stored in tblAdminLANQueries. If it's a Select query it
opens it, else it executes the action query.

The action and select queries work fine if there are no parameters in the
query. But if TempLANQuery includes parameters such as [Begin Date] I get
an error 2482 - "Microsoft Access can't find the name 'Begin Date' you
entered in the expression." The query TempLANQuery runs with no problems
outside the application, and asks the user to input the required parameter
[Begin Date] as expected.

The code is shown below. Any thoughts on this error will be appreciated.

Bill
__________________________________________________

Private Sub cmdExecute_Click()

' execute the selected action query or open a Select query

Dim db As Database
Set db = CurrentDb

Dim qdf As QueryDef
Dim prm As Parameter

' delete the query if it already exists in the mdb
On Error Resume Next
DoCmd.DeleteObject acQuery, "TempLANQuery"

On Error GoTo Error_Handler

Set qdf = db.CreateQueryDef("TempLANQuery")

qdf.SQL = Me!QueryCode ' stored in tblAdminLANQueries

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

If Left(Me!QueryCode, 6) = "select" Then
DoCmd.OpenQuery ("TempLANQuery")
Else
qdf.Execute
End If

MsgBox "Query executed successfully."

Exit_ExecuteQuery:

Set db = Nothing
Set qdf = Nothing
Exit Sub

Error_Handler:

MsgBox "An error occurred when attempting to execute " & Me!Description
_
& vbCrLf & vbCrLf _
& "Error number: " & Str(Err) _
& vbCrLf _
& "Description: " & Err.Description

Resume Exit_ExecuteQuery

End Sub

 

[peregenem]

I'm storing the SQL code for certain queries in a memo field in a table and
executing this code in VBA. This code creates a query with CreateQueryDef
using the SQL code stored in tblAdminLANQueries.

Do you keep your VBA procedures in a table to call on the fly or maybe
use the VBA method CallByName often? I hope not

The same principle should apply to your SQL procedures i.e. they are
'compiled' as business logic and called when required.

 

[Guest]

If you want Access to evaluate or prompt for parameters,
you have to execute the query in Access, not VBA,
ie docmd.runsql.

If you want to iterate through the parameters collection,
you probably have to use named parameters, not just
undefined values in the sql.

(david)

 

[Bill Murphy]

99% of the time I compile my queries and store them as Access queries so
they are compiled. But this is a special case where I am storing the SQL
code in a memo field in a table. My application is running on a Citrix
server, and I use this approach in order to pull certain admin queries
across to the Citrix server from the LAN server since I do not have hands on
admin rights on Citrix.

Bill