...as expr1,

[BikeToWork]

I'm not sure why, but there is a split database which regularly has fields in the sql code of front end queries change from "FieldName1, FieldName2" to "Fieldname1 as expr1, Fieldname2 as expr2, etc, etc, etc. This happens on about half the queries in the database on a regular basis. I think it is probably corruption, but wanted to see if others here had similar issues. I compact the database regularly. I was thinking of running an autoexec procedure on the database that would go through all queries' sql and replace "FieldName as expr1" with "FieldName," etc. wherever it finds expr in the sql code. Does that seem like a viable plan? The problem is that people copy the front end down to their local hard drives and it would be nice if I could at least fix the issue in the front end that they copy.

[jforbes]

You might be able to do that. It might take some time to perform that operation, so if you were to do it every time the database is opened, it may become bothersome.

What really concerns me is that I'm having trouble imagining how that could be happening unless your users are some how getting into the definition of the Query itself. You wouldn't happen to be opening the Query and showing it to your users? ...allowing them to go into design view. If you are, you might want to wrap the query in a Form in attempt to have this problem go away.

[BikeToWork]

With Access (especially now without mdw workgroup files) users can do pretty much whatever they want with an Access database. I don't "think" any users are changing queries in design mode and knowing the users here it is doubtful. Could it be that the database becomes disconnected from it's back end tables momentarily and queries can't find the fields, thus replacing field names with expr1, expr2, etc.? I have been working with Access for years and in the past this only happened if a table's fields were gone from the database, but the queries remained. Thanks for the reply.

[jforbes]

With your experience, your familiar with the way Access will attempt to update any expressions a Query when it's opened in the Query By Example editor. Which can be trying at times.

But, in my experience, it will only update the Query when the QueryDef is saved by the user (or through VBA). So, someone, either the application itself or a user, has to open the QueryDef and then Save it for the QueryDef to change. I would look for what is causing your changes to happen and attempt to control it.

If you determine that you can't control it, I would go the route of creating a backup copy of the unruly Queries as templates. And then on startup of the database, copy the template queries over the working queries (the ones you intend to use). I think this would be simpler and quicker than walking the QueryDefs and performing replacements on the SQL.