CODEWITHSUNDEEP
sql-serverms-accesssql-server-2005vba
SweetGangster
SweetGangster

Reputation: 217

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

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?

Upvotes: 0

Views: 405

Answers (3)

Clint Davis
Clint Davis

Reputation: 451

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

Upvotes: 0

Philippe Grondier
Philippe Grondier

Reputation: 11148

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 ...

Upvotes: 0

Russ Cam
Russ Cam

Reputation: 125538

Set up an SSIS package to do this

Upvotes: 5

Related Questions