Sunday, February 15, 2009

Access replicas and runtimes

We recently completed a lengthy project, migrating a replicated MS-Access 2003 application to a client/server model using Access as the front-end and SQL Server 2005 as the database. The application had previously been modified as necessary to utilize the Access 2003 runtime on terminal server.

There are a few features in the app that display query results in a datasheet view, and every one of those queries (even queries that are not updateable) is opened as read-only to prevent accidental changes, for example:

DoCmd.OpenQuery "MyQuery", acViewNormal, acReadOnly

Since the Access runtime leaves out all the built-in toolbars, a custom toolbar was added to allow the user to filter & sort the data for analysis. After the user completes their analysis & closes the query, any filter or sort is discarded by the runtime, so subsequent views return to the predefined values. At least, that is what we expected.

Final testing of the client / server version of the app revealed that query filter / sort operations, and even changing column widths, upon close would prompt to the user "save design changes?" how could that happen with the runtime & the read-only status?

As it turns out, the answer is quite simple. In the replicated system, changes could only be made in the design master. Any filter / sort etc. in the replica disappeared when the query is closed. However, the new front-end is not replicated, and so although the Access runtime prevents access to the full design environment, we are exposing the filter / sort capability to the user & therefore they can persist changes to their query.

Now, is that defect or a feature? Each user has their own copy of the front-end file, so they are now able to customize their vision of each query for their needs. The data itself continues to be protected due to the read-only status. That sounds like a feature.

No comments: