I am trying to figure out how to automate a process I am doing each month. I am a newb when it comes to SQL Server and Access, so this is my first attempt at automation.

Every month I receive 60 .mdb files that must be appended to an already created table.
- Each file contains just a single table.
- Each file has a similar name (data extract MM.DD.YY.mdb)

What I have been doing recently is the following:

1) right click database
2) tasks > import data
3) Choosing "Microsoft Access" as the data source on the first step
4) Choose the file name (data extract MM.DD.YY.mdb)
5) click next 3 times (until you are at the data destination step
6) choose the same table for all 60 files
7) click next and run immediately

I am having to repeat that for each of the 60 files. The only thing that changes is the file name. Can someone point me in the right direction as to what would be the best way to automate this process?

Thanks!

Perfect scenario for an SSIS package...as well as for a TSQL-based SQL Agent job...or for a batch script-based job...Take a pick. The main concept will be to iterate through *.mdb files, import the table from each, then move mdb files to archive (one-at-a-time). In SSIS you'll use For Each container for iteration, in TSQL you can load the contents of the directory using either xp_cmdshell or sp_OAxxx procedures, or you can preceed the TSQL with a CmdExec step where you can save the output from filtered DIR into a file, then import that file into a table, and then iterate through it. In a batch-based script you can use FOR loop, etc.