Post reply

Automatically update SQL table with contents of Access Table

  • December 19, 2011 at 4:36 am

    #249972

    I have an Access database with a ref table that is maintained by an individual.

    I need to have this table reflected in SQL Server Management Studio in a table that can either be updated via a daily job or other means

    Could someone please point me in the right direction please?

    Thanks in advance

  • December 19, 2011 at 5:24 am

    #1423050

    Check out Integration Services (SSIS).

    It allows you to build a package that extracts the data from the Access table and dumps it into the SQL Server table. You can schedule this package daily with the SQL Server Agent.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • December 20, 2011 at 12:07 pm

    #1423869

    You could also set up the Access database as a linked server. This way you can do pretty much what you want with it from the SQL Server side. I've done this before when I wanted to import web orders from an Access database into a SQL Server database and it worked like a charm.

    Todd Fifield

  • December 21, 2011 at 12:36 pm

    #1424412

    do you trust the person that maintains the MS Access table? If so, you might consider dropping the table from Access and just making a linked table from Access to the SQL Server table. Then you'd always have up to date info and not need to worry about updating the SQL Server - the Access user would be doing it.

  • December 21, 2011 at 2:27 pm

    #1424470

    Please let us know what you decide to do. It will be great if you let us know how does it goes. I have used linked Servers and also linked tables in access.



    My blog

  • December 28, 2011 at 8:47 am

    #1426125

    Hi

    Just to let you know i went with the linked access table solution as it works fine and also the maintenance if the data is done by the user at the other end and I don t have to worry about it.

    Thanks for your help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply