Friday, September 05, 2008

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.

No comments: