Automate the Import of New table from Existing Table using a Query

[w860098]

From time to time I have 'imported' a new table, based upon the contents of an existing table but using a query to modify that data (all in the one Access database). I use the Options in the Import Objects subscreen, setting 'Import Queries' to 'As Tables' - driving the whole process manually.
I now wish to automate that process and did expect to be able to define a macro to achieve that. However, so far, I have been unable to create such a macro.
Is it possible to do it this way ? Alternatively, can I build a module to achieve this ?

 

[PHV]

Have a look at the DoCmd.TransferDatabase method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[w860098]

Thanks for that tip. Using that information I have made use of the TransferDatabase action within a macro - basically it did work but unfortunately, it would appear I either create a new table from the original table or create a new version of the query itself (whereas in fact I wish to use the query to create a different table from the original table). [The help details appear to indicate that the same set of parameters is available for TransferDatabase regardless whether I use it within a macro or via the DoCmd method]
I have now used a different approach whereby I have tried to output the detail created by the query as an external spreadsheet and then load it back as a table. The sequence of lines in the macro are OpenQuery, OutputTo (a spreadsheet as *xls), OpenTable, TransferSpreadsheet (Import as Microsoft Excel 97).
Unfortunately this fails at the last step with the message "External table isn't in the expected format". The version of Excel we use is 97; any thoughts as to where my difficulties now lie ?

 

[lespaul]

Do you have to put it in the spreadsheet? If your going to do that why not just make the query you use for the export be a make table query?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[w860098]

The perfect answer !! (I had not used this feature before)
This has allowed me to automate the whole process by using the 'OpenQuery' action within the macro.
Many thanks.

 

[lespaul]

no problem! glad you got it,

leslie