Replicated Queries

MS-Access makes it very easy to change the SQL of a query simply by using a statement like

CurrentDb.QueryDefs("MyQuery").SQL = "SELECT * FROM MyTable"

there is no need to Dim or Set anything, simply plug in the SQL and you're ready to go.

However, I just discovered that this does not work in a replicated database. Or I should say, it works in the Design Master but it does not work in a Replica. I needed to apply some complex criteria, more complex than could be expressed simply by using a WHERE condition when opening a report. Testing the code in the DM worked just fine, but users received a message "cannot update...object is read-only" because Access considers this to be a design change & does not allow that in a Replica.

You can code things like changing visibility, font colors, enabling & disabling controls, but QueryDefs are off limits.

The challenge: I had an existing report that used a query for the detail section, and the report footer contained a subreport with a summary that used the same query as the body of the report. Opening the report with a filter or where argument would properly filter the detail section, but the subreport would display all of the data. I tried using the OnOpen or OnFormat event to apply a filter to the subreport but that did not work.

(EDIT) At this point, I tried to change the SQL for the QueryDef using the type of statement shown above, to filter the data for both the body of the report and the summary subreport; this worked in the DM but not the Replica. My original post has not been clear about that. (EDIT)

My solution was basically to create a staging table for the subreport, and flush-and-fill the table prior to opening the report.

In the DM I did this:

• Ran the old (bad) code to set up the SQL statement
• Switched the query to a make-table, to create the staging table
• Switched the query to an append query
• Copied the append SQL and used that to build up a dynamic SQL statement in VBA

The button OnClick would then delete all records from the staging table, run the dynamic SQL to populate that table, and then open the report.