Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2011

    Unanswered: importing .mdb table

    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?


  2. #2
    Join Date
    Jul 2003
    San Antonio, TX
    Perfect scenario for an SSIS 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.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts