Executing stored queries with parameters

[jonathanknez]

I have a program that uses Access 2003 and VBA. I have several queries
that need to be dynamically generated. For example, I want to get all
the information about a particular program from the program table.
Currently I would construct an SQL string and execute it as follows:

query = "SELECT * FROM program where Program.name = "+ nameVar
Set rsQuery = thisDB.OpenRecordset(query)

For maintainability and possible performanc reasons, I would like to
execute a stored query that takes a name parameter.

I have created a stored procedure (called qProg) within access that
requires a program name. The SQL for this stored query is as follows:

PARAMETERS progName Text ( 255 );
SELECT *
FROM Program
WHERE progName=Program.program_name;

I can not figure out how to call that query and specify the progName
parameter from within my VBA code. Is this possible? I was hoping there
it was easy to do, something like this:

Set rsQuery = thisDB.OpenRecordset(qProg, nameVar)

where qProg is the stored procedure and nameVar is my dynamic name. Any
suggestions? Thanks.

 

[Marshall Barton]

I have a program that uses Access 2003 and VBA. I have several queries
that need to be dynamically generated. For example, I want to get all
the information about a particular program from the program table.
Currently I would construct an SQL string and execute it as follows:

query = "SELECT * FROM program where Program.name = "+ nameVar
Set rsQuery = thisDB.OpenRecordset(query)

For maintainability and possible performanc reasons, I would like to
execute a stored query that takes a name parameter.

I have created a stored procedure (called qProg) within access that
requires a program name. The SQL for this stored query is as follows:

PARAMETERS progName Text ( 255 );
SELECT *
FROM Program
WHERE progName=Program.program_name;

I can not figure out how to call that query and specify the progName
parameter from within my VBA code. Is this possible? I was hoping there
it was easy to do, something like this:

Set rsQuery = thisDB.OpenRecordset(qProg, nameVar)

where qProg is the stored procedure and nameVar is my dynamic name. Any
suggestions? Thanks.

Dim qdf As QueryDef
Set qdf = thisDb.QueryDefs!qProg
qdf.Parameters(0) = nameVar
Set rsQuery = qdf.OpenRecordset()

 

[jonathanknez]

Thanks! Worked like a charm.