-
I often use the following code to successfully run Access queries in VB:
strParam = "PARAMETERS [paramName] TEXT; "
strSQL = dbData.QueryDefs("queryName").SQL
Set qdf = dbData.CreateQueryDef("", strParam & strSQL)
qdf("paramName") = textfield
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Does anybody know of anything similar to run an Access report, based on a query that takes a parameter.
Note that I am trying to avoid the situation where I have to open the Access application. I don't want to have to do:
Set acc = New Access.Application
acc.DoCmd.OpenReport rptName, acViewNormal
because this gives me other problems.
I am using VB6, Access 97, Jet/DAO.
Thanks in advance.
[This message has been edited by Michael (edited 06-24-1999).]
-
I'm not sure you can run an Access report from VB without instantiating Access. If you want to make data available for an Access report, why not create a temporary table for the report to use, then populate it with the results of an Append query that takes the parameter. If you know the query ahead of time, you could actually save it to the .mdb file and pass in the parameter.
set qdf = db.querydefs("query name")
qdf!parameter_name = "parameter value"
qdf.Execute
and the query sql would be something like
INSERT INTO tblReport (field1, field2, field3) SELECT fieldthis, fieldthat, fieldother FROM tbl1 WHERE filedother = [param]
Does this help?
bash
-
Hi bash
Thanks for your suggestion. It may work. I know the query in advance, and the value of the parameter is determined at run time.
The only reservation I have is that I am running on a multi-user system and if I use a fixed name for the temp table then I risk overwriting it. If I use a unique table name then I have the problem of telling my report which table to use.
Why is life never simple?
