Process Automation 1

[davisto01]

I need to automate a process for updating hundreds of tables.
For each table I will need to:
1) Truncate (delete) the information that is currently in the table but leave the headings intact.
2) Run append queries to add the new data.

Each table will essentially look the way it did the day before except it will have brand new data and perhaps have a few more additional rows (~100 or so)

Are there modules that I can create to do this? Helpful Subroutines?
Any suggestions or should I just get use to hiring temps to do it the old fashioned way?

Thanks!!

TD
Business Analyst
Enterprise Data Warehouse

 

[JoanneM]

Ok it's Friday & I'm feeling Generous



1) Truncate (delete) the information that is currently in the table but leave the headings intact.

Do you mean delete all data from a table and but not the table itself ( are headings the field names is what I am asking here?) if thats the case

for that create yourself a module

sub clr_appendTbls()

dim strsql as string

strsql = "DELETE * from tblname"
docmd.runsql strsql

' data's gone now run your append queries

docmd.openquery "qryappendtblname"
end sub

tblname is replace obviously by the actual name of your table.

ACTION queries - delete, append etc all can be run from
code

NB IF you have an AUTONUMBER Primary key on this table and you follow this process your numbers will change so be aware this may or may not be straight forward

Take a backup before you do anything and keep it safe.
Work in a development area which cannot be confused with live data.

any probs let me know.

regards

Jo