Writing a macro to run delete and append queries

[chantil]

Hi,

I'm trying to put together a new database at work and I want to write a macro for it. The trouble is I'm not really sure what I'm doing.

The database consists of a number of seperate tables which I recieve an update for each week, and I've just followed the process outlined on the Microsoft help website to set up two queries, one which deletes duplicates and the second which appends the new rows to the 'Master' table.

(See section called - "I want to delete duplicate records from one table and merge its remaining records into another table" -

http://office.microsoft.com/en-gb/a...A001034558.aspx?CTT=1&origin=EC001022704#BM24

)

Now onto my problem, because I'm going to have to do this weekly with multiple sets of data I wanted to set up a query that would run all the necessary queries, however I can't work out how to do this for Action queries and have failed to find anything that would help in my usual internet searches.

Would anyone be able to help me out with this? It would be very much appreciated.

 

[substitute]

You've said what you need to do - create a macro. There are two options:

1. Create a separate query for each table operation and then create a macro in the database window that just consists of a load of "OpenQuery" lines. You can use a condition including the DCount() function to check you have data to append before running each append query.

2. Create a VBA macro that includes a number of "docmd.openquery" statements as above. The advantage is that with VBA you have flexibility to issue prompts, check table existence etc. and modify the queries programmatically to deal with day to day variations.

It is confusing that MS call both options macros!

 

[chantil]

Thanks substitute. I've been really stupid! When I saw the 'openquery' option on the macro I automatically assumed it meant more of a select query rather than thinking it would actually 'run' a query.

I think I'll go with option 1 out of the two, my coding skills are non-existant!