Possibility Thinking - Importing Queries

[DreamGenius]

First, I apologise if this is in the wrong forum - please feel free to move it. I think though that it's more VBA related than Query.

My client has a split database on which development of the front end is ongoing but the database is in use. On upgrading the front end, it's necessary to open the old and new front ends to drag and drop queries that have been created since the last upgrade into the new copy of the front end.

I've been thinking that if queries were saved to a text file, with name and code, a VBA function could read the text file as a recordset and, using CreateQueryDef, recreate all the backed-up queries.

Is this possible, or even feasible, or am I just dreaming. Is it possible, easier, simpler, better to store these queries in the back end, where they won't be replaced?

 

[DJkarl]

What you're proposing is possible but not a great idea. Your client should be replacing the entire front-end for each update, ideally this would be done automatically, the forum has an example of an Auto-Updating utility for front-ends. Right now all your client is changing is queries, but what if a new macro or report is created, or a new code module is added. Replacing the entire FE is typically what's done.

 

[boblarson]

Yes, users should NOT be creating their own queries in your front end. If they need their own queries they should have a separate database LINKED to your frontend (notice I didn't say backend).

 

[DreamGenius]

boblarson, I understand your point, but it isn't quite that simple - it never is!

The developer recreated Query Analyzer within the front end, so that empowered users could create their own queries. However, those queries aren't preserved across database upgrades. I could go back to the developer but it'd be an unneccesary cost, if I can figure out a simpler way that the administrator can preserve the queries.

Since starting the thread, I've been thinking that there are more ways to do this:

1. The text file solution that I've already suggested, but this would require scripting which will take me time to work out
2. Exporting the queries to the backend and reimporting them on upgrade, effectively using the back end as a backup
3. Creating a separate database for people to build their own queries in, but this would render some of the developer's work null and void

Can anyone else add any that I missed? What do you think the preferred one should be?

 

[odin1701]

I would not start exporting and importing objects to your backed automatically, etc. The back end is for data only, and should never store other things like forms - it's done that way to help prevent any possible corruption. Perhaps some simple code to handle anything if someone tries to open it, but that's it -and that could be handled with a database password anyway.

Bob's way should work - how is it not that simple? All main functions they can use the front end. If they need to work with queries, then they can work with the "Query" database and generate their own queries/reports if they want.

They shouldn't be editing/changing/adding/removing objects from your front end, ever.

Also - allowing users to create their own queries introduces the possibility of them deleting data which you don't want them to be able to do for the most part.\

Your front end isn't going to know about their queries that they create - so you don't have to worry about the code not working, or whatever. So there should be no need for them to be able to create any objects in your front end.

The front end is there as a development platform - the developer should be the only one making changes to it.

 

[boblarson]

And if necessary, modify a copy of the frontend to keep the query wizard intact but remove all other functionality and then link to your frontend.