Append query - change source table at runtime

[TheToeOfPooh]

I would appreciate any sugestions as to how I can make the following process as simple as possible for the user.

Currently to update a master table on a daily basis they have to:-
TABLES
Link Table
Link a new Excel sheet each day (e.g. Augprn31)
QUERIES
Append new source table to a master table called tblCurrentMonth
By using
In "Design View"
Show table (the new table is selected e.g. Augprn31)
Table criteria using drop down option to replace the existing source table with the new one. e.g. Augprn31 will replace Augprn30

THE PROBLEM
This has to be done for each of the 21 fields which are appended to tblCurrentMonth.

I have tried using the right-click method to display the query properties which could be used to update the Source database (current). This scared the bejazus out of the user.

If possible a list box containing the table names would pop-up when the design view is opened for this append query?

Any sugestions please as I can build a pretty basic database incl tables, forms, queries and reports but have no experience of "customising".

Bernard

 

[TheToeOfPooh]

Mike

I continue to be surprised at the time taken by people who are evidently both experienced and busy, to help others such as myself on this board.

I too use a whole script of SendKeys to automate a series of repetative daily tasks. I did take a look at your link but due to my lack of knowledege it was difficult for me to follow. I will stay with my current Heath Robinson inspired Shell scripts for the time being.

Using the new process I did come across an "Overflow" error when running my old Append Query. So I changed it to a Make Table, ran it then changed back to an Append. All has been well since.

Again - thanks to everyone. The process works, the user is confidant and I can move onto the next job in hand.

Bernard

 

[TheToeOfPooh]

Just to close my questions -

Peter
I have used the Link Wizard combined with Mikes "processor" .

Using the Macro option in Access to produce routines - which the Performance Optimiser advised me to turn into a Module.
I have added a Control Button to the Main Menu form and attached the module to a "On Click" function.
The user can now very easily produce the MIS reports they require.

One problem I did experience was an Overflow Error 3349 sudenly appeared.
I deleted the existing Linked table.
Re applied the Link
Analysed the new link using Excel and found headers had changed due to a lapse in my concentration.

A very fine effort by all - thanks again.

Bernard

 

[bat17]

Glad you have it all up and running now

Peter