New Query Design in Access 2007 MDE

[Sky]

Does anyone know how to open the standard Access 2007 new query design in a
compiled .mde or .accde?

My advanced customers sometimes need the capability to create queries
themselves.

In Access 2003 and prior, the standard RunCommand worked fine.

Access 2007, however, seems to disable the standard new query design command
once the database is compiled.

All of the following work fine in an uncompiled database:
DoCmd.RunCommand acCmdNewObjectDesignQuery
or
Application.CommandBars.ExecuteMso "CreateQueryInDesignView"
or
CommandBars.FindControl(Id:=12701).Execute

But when the database is compiled, all of the above cause flickering but do
nothing.

The following DOES work in both compiled and uncompiled databases:

DoCmd.RunCommand acCmdNewObjectQuery

The above opens the step-by-step query wizard, which is tedious for advanced
users and my customers do not like it as well. (It also starts out from
Application.CurrentObjectName, so you must be careful about the focus for a
new query, especially if the focus is on a referenced library object.) The
major disadvantage with acCmdNewObjectQuery is that it forces the user to
save an explicit query name before continuing to view or design it.
Nevertheless, acCmdNewObjectQuery is my current solution.

Also the following works fine in a compiled database, for an existing query:
DoCmd.OpenQuery strName, acViewDesign

Clearly Microsoft does not prevent query design in a compiled Mde, but
acCmdNewObjectDesignQuery does not work.

Does anyone know how to execute an equivalent to acCmdNewObjectDesignQuery
in a compiled Mde?

- Steve

 

[Tony Toews [MVP]]

Does anyone know how to open the standard Access 2007 new query design in a
compiled .mde or .accde?

I never knew this would work in an MDE.

That said, given that you state they are running a retail copy of
Access, I would suggest you give the users another MDB with nothing in
it but linked tables and your code your clients use to relink the
tables.

This way they can create queries as they see fit. And when you give
them a new FE MDE it won't wipe out their current queries.

Also note to the lurkers. One problem with this approach is that the
users, hopefully power users who understand the problems, now can
easily update and delete records in the tables.

Tony

 

[Sky]

Comments interspersed...

I never knew this would work in an MDE.

It has worked in every version of Access from at least 95 on. In Access
2007, both the query wizard and query design view both work fine, just not
RunCommand acCmdNewObjectDesignQuery.

That said, given that you state they are running a retail copy of
Access, I would suggest you give the users another MDB with nothing in
it but linked tables and your code your clients use to relink the
tables.

The customers want to be able to view their queries along with the edit
forms. For example, they may want to change data, and it is much better that
they edit in the controlled forms, rather than editing in live queries
(which is discouraged).

It's true they could use a separate database, then Alt-Tab between the
application and their own queries, or resize and tile multiple application
windows, but that gets tedious quickly. At that point, they might start
opening the back-end databases directly, which is much worse.

This way they can create queries as they see fit. And when you give
them a new FE MDE it won't wipe out their current queries.

The install process with a new front-end (using InnoSetup) backs up the old
front-end and prompts the user to select and import their custom user
queries. (Naturally all of the built-in application queries remain hidden.)

Also note to the lurkers. One problem with this approach is that the
users, hopefully power users who understand the problems, now can
easily update and delete records in the tables.

Yes, a user could even Drop Table or Alter Table in a remove database with
SQL, breaking the application completely. Of course, any user can also open
the back end file directly and cause all kinds of havoc; this is true with
any Jet back end. Users know this. Query capability is no worse than that!
(If you need security, you should NOT use a Jet back end.) I do provide a
rebuild capability to fix table structures if they get messed up (its real
usage is to update new table structures if needed for a new install), but of
course it cannot recover deleted records.

This database is for a complex scheduling/simulation model. Sometimes users
need to make large-scale query-driven updates.The customers are smart
analysts; they understand the issues. Ultimately it is their data and it
belongs to them.

The Access query interface is one of its most powerful features, and one of
the reasons this is built with Access in the first place. I could build a
limited and less flexible and safer query interface (with non-editable
snapshot datasheets only), but Microsoft has already spent tons of money on
a query interface, which my customers want to use with its full update
capability.

Anyway, it's not documented that I can see, but acCmdNewObjectDesignQuery
now fails in a .Mde. In the old Access 97 days, we used to be able to call
Access utility functions directly, but I do not know the functions for
Access 2007.

- Steve