Dynamic Queries

[tmcneil]

I have two databases, one resides on a network and contains the live data, the second will be stored on a users computer and has a live link to the network database. The second database contains queries, forms and macros that the user will use to search/edit/remove records.

However, the live database may have fields added/removed, which may cause some havoc to the queries and forms written in the linked database.

How can I create dynamic queries and/or forms that will detect these changes that may occur when a field is removed or added to the live database?

Todd

 

[MichaelRed]

Basically, I do not believe this is pratical. There are to many User issues involved. In the general case the development of this capability would be more 'expensive' than just doing the process manually.

On the other side, if you do develop a programatic process for removing unwanted fields from an application (w/o out user intervention) and can correspondingly add new fields to the users app (again w/o thier cooperation), I would be glad to review the code and possibly be a beta tester.

I might suggest that you consider alternative approaches to the (code) maintenance issue. Somewhere within the Ms. Access forums, I have discussed this in another Post, basically suggesting that they maintain a copy of the front end database (app) and make all necessary changes there and have any/all new /changed objects in that database be copied to the Users database each time the user 'started' the app. I'm sure you can find the thread just doing a search on my 'handle' (MichaelRed).



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[tmcneil]

I don't think I made it clear, but I was able to figure this one out. Set the property, UpdateAlllFields. To do this, open your query in design view and click the Properties tool on the toolbar. It will give you the option of UpdateAllFields to yes or No. Of course, if fields are added or changed, this property will know this and can update the queries. Make sure you leave all your fields that have SQL or specific statements needed to run the query. The rest of the fields can be removed.

This solves my query problem, but I do not think that Access froms are smart enough to update itselfs when fields are added/deleted.

Thanks for replying and I hope this helps others.

Todd