Best way to get data from Ms Access Mdb file to sql server 2005

Question

We use a software which uses Sql server 2005 as back-end.

The problem is we have data coming in access file and we need to store it in sql server tables.

I have the sql server tables ready.

I don't need all the data i get in MDB access file but need specific columns from access and then store it in SQL server.

I get fresh access tables every week and need to migrate it to tables in SQL Server.

What is the best way to do this?

Answer 1

Set up an SSIS package to do this

• An Introduction to SSIS - Tutorial

• Deploying and running SSIS packages

Answer 2

Assuming you can use Access as a client interface, you could open 2 connections:

• An ADODB or ODBC connection to the Access database
• An ADODB or ODBC connection to the SQL server

You'll open a recordset on each connection, then browse the Access Database Recordset to fill the SQL server recordset with code such as:

Do while not rsACCESS.eof
    rsSQL.addNew
    For each rsField in rsACCESS.field
        rsSQL.fields(rsField.name) = rsField
    Next rsField
    rsSQL.update
    rsACCESS.moveNext
Loop

Of course you'll have to make sure that your rsSQl recordset is updatable and so on ...

Answer 3

SSMA will do this a well. You can setup a job and save it to easily run it again.

http://www.microsoft.com/Sqlserver/2005/en/us/migration.aspx