Passing parameters from VB to ACCESS

[srag]

We have some reports based on MS ACCESS Queries, taking parameters from the user. When we try to access these reports from the VB front-end, how can we pass parameters from VB to ACCESS?

 

[RickSpr]

You can't assign values to the stored query's parameters, only to a copy that's been loaded in memory. If you were opening the query itself, you would access CurrentDb.QueryDefs![query name], then use the resulting QueryDef object's Parameters property to assign parameter values. Then you could run the query and Access wouldn't prompt for parameters.

Unfortunately, a report based on this query won't get those parameters, because it's going to load its own copy of the query into memory. And there's no way you can access the report's copy from code.

I can think of two ways around this:
1. Have your VB code construct an SQL statement similar to the query's, but with the parameter names replaced by their values. Then it can open the report, and then assign the SQL statement to the report's RecordSource property. This will cause the report to flicker when you first open it, as it automatically requeries from the new record source.
2. I'm not sure this will work, because of VB and Access having separate namespaces, but maybe you could put the parameter values into public variables in the Access application, and modify the query to retrieve its parameters from the public variables (probably by calling custom functions in an Access module to return the variable values).
Rick Sprague