Check Fields In An Append Query

[DevelopV]

I have the need to archive data in certain tables.

To do this I run an:
* append query which adds the data to an archive table.
* delete query which deletes the records from the original table.

I run code that compares the the structure of the original table to the structure of the archive table

However I want to ensure that the append query is correct.

How can I interrogate the append query to see which fields are included from the original table and to which fields the data will be appended to in the archive table?

Thanks in advance

 

[dhookom]

What do you mean by "interrogate"? You can open the append query in design view. Or use debug.print or some other method.

Duane
Hook'D on Access
MS Access MVP

 

[DevelopV]

I have code to get a list of all the fields in the original and achieve tables
With this I can ensure that each field in the original table exist in the achieve table and thee field properties are the same.

I now want code to look at the append query and ensure that each field in the original table is being appended to the same field in the achieve table.

Many thanks

 

[dhookom]

While you are running your "code to get a list of all the fields in the original and achieve tables" consider building the SQL statement of the append query and running it.

Duane
Hook'D on Access
MS Access MVP

 

[DevelopV]

Rather than have a append query, can I not simply use:

docmd.RunSql "INSERT INTO tblArchive SELECT * FROM tblOriginalData;"

Provided the field names are the same in both tables, job done?
I have tested this and it appears to work.

Is this correct?

 

[dhookom]

I never use SELECT * with this type of query. I always specify column names.

Duane
Hook'D on Access
MS Access MVP