Combine multiple access tables into one. (1 Viewer)

I have multiple tables that are created from some software that puts them each into the access database. each table is one year of data. the software cannot put all years into one table because of memory. I want to combine these tables into one. In addition, I need to do some manipulatins: the dates come over as MM-DD-YYYY strings and I need to convert them to dates MM/DD/YYYY, and also I need to parse a string to extract the General Ledger account number. I know how to use this easily from within Excel VBA using dateserial, and Instr etc., but now sure how/if I put some code into access and have it called to do this for me automatically. Each night the most recent year is automatically updated in access, so the combination file needs to be refreshed whenever the most recent year file changes.

I currently doing all of this in excel: first initialize the NewCombinedTable, then one by one dumping each year file into an excel worksheet, doing the work needed, then appending it to the NewCombinedTable. However, I think it would work much quicker if I was able to trigger the Combination to happen in Access along with the data manipulations.

I also need to know whether DAO or ADO is best for me to use with 2010.

?
Stoka.

I would put all the historical data (data that you will no longer be getting) into a table. Then for the current year (the one you get updates every night) I would use a UNION query (http://www.techonthenet.com/sql/union.php). to bring its data together with the historical data. Otherwise every day you will have to run something to bring all that data into one table, this way, it just automatically works without you doing anything.

As for converting strings to dates, you would use the CDate function(http://www.techonthenet.com/access/functions/datatype/cdate.php)