none
how to convert MS Access 2007 database to SQL server 2005

    Question

  • Hi

    I am new to SQL Server 

    I have a database in MS Access 2007 which has 5 tables each containing almost 100 records

    I want to convert that database to SQL Server 2005 Enterprise edition database

    Is there a way to do this?

    If so then how?

    Thank you,


    NB
    Wednesday, September 21, 2011 10:11 AM

Answers

  • Hi Fireblade123,

    As Uri Dimant pointed, you can import the Access tables into SQL Server database by using Linked Server or OPENDATASOURCE. There are other options which might help you to convert Access 2007 database to SQL Server 2005 database as well. 

    • Use the import / export wizard to convert MS Access tables to SQL Server. For more information: http://msdn.microsoft.com/en-us/library/ms141209.aspx

    • Use SSIS (Sql Server Integration Services ) to load data to SQL Server following the steps below.
    1. Create a new SSIS Solution
    2. Select your package
    3. Drag a Control Flow Item "Data Flow" to your "Control Flow" pane.

    Set up your connection manager for your MS Access database;
    1. Right click your mouse in the connection manager pane.
    2. Select "New OLE  DB Connection Manager".
    3. Click the new button.
    4. From within the look up box labeled "Provider:" select "Microsoft Jet 4.0 OLE DB
       Provider" or similar.
    5. Browse to the MS Access database you want to import.
    6. Click "OK" to confirm the "Configure OLE DB Connection Manager" settings.

    Set up your connection manager for your SQL Server table;
    1. Right click your mouse in the connection manager pane.
    2. Select "New OLE DB Connection Manager".
    3. Click the new button.
    4. Type in the name of your SQL Server, e.g. (local)
    5. Select your SQL Server database name; e.g. AdventureWorks
    6. Click on "Test Connection" button.
    7. Click "OK" if test is successful.

    Import process together within the data flow for MS Access data source
    1. Drag an "OLE DB Source" component to the "Data Flow" task.
    2. Right click this component and select "Edit"
    3. Select the name of the MS Access connection manager.
    4. Select the name of the table you wish to import.
    5. Click "OK"

    SQL Server destination:
    1. Drag an "OLE DB Destination" to the "Data Flow" pane.
    2. Select the "OLE DB Source" component again.
    3. Drag the green arrow from the "OLE DB Source" component to point to the "OLE DB Destination"
    4. Right click the "OLE DB Destination"  component and select "Edit".
    5. Click on "New" if you wish to create a new SQL Table.
    6. Over type "OLE DB Destination" with a meaningful table name
    7. Click "OK"
    8. Click the "Mappings" tab to check the mappings from the MS Access table columns to the SQL Server table columns are OK.

    Execution: 
    1. Click the green right pointing arrow to start debugging.
    2. On successful execution the components will light up green.
    Here is a further SSIS tutorial: http://msdn.microsoft.com/en-US/library/ms169917(v=SQL.90).aspx
    Further link on using OLE DB Source:  http://msdn.microsoft.com/en-US/library/ms139767(v=SQL.90).aspx
    "Microsoft Office 12.0 Access Database Engine OLE DB Provider" is for MS Access 2007 connectivity: http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

    I hope the above helps!


    Regards, Amber zhang
    • Marked as answer by Stephanie Lv Wednesday, September 28, 2011 3:12 AM
    Thursday, September 22, 2011 8:03 AM

All replies

  • Not directly.  You will have to move those tables into SQL Server by using Linked Server or OPENDATASOURCE command
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, September 21, 2011 10:35 AM
  • Hi Fireblade123,

    As Uri Dimant pointed, you can import the Access tables into SQL Server database by using Linked Server or OPENDATASOURCE. There are other options which might help you to convert Access 2007 database to SQL Server 2005 database as well. 

    • Use the import / export wizard to convert MS Access tables to SQL Server. For more information: http://msdn.microsoft.com/en-us/library/ms141209.aspx

    • Use SSIS (Sql Server Integration Services ) to load data to SQL Server following the steps below.
    1. Create a new SSIS Solution
    2. Select your package
    3. Drag a Control Flow Item "Data Flow" to your "Control Flow" pane.

    Set up your connection manager for your MS Access database;
    1. Right click your mouse in the connection manager pane.
    2. Select "New OLE  DB Connection Manager".
    3. Click the new button.
    4. From within the look up box labeled "Provider:" select "Microsoft Jet 4.0 OLE DB
       Provider" or similar.
    5. Browse to the MS Access database you want to import.
    6. Click "OK" to confirm the "Configure OLE DB Connection Manager" settings.

    Set up your connection manager for your SQL Server table;
    1. Right click your mouse in the connection manager pane.
    2. Select "New OLE DB Connection Manager".
    3. Click the new button.
    4. Type in the name of your SQL Server, e.g. (local)
    5. Select your SQL Server database name; e.g. AdventureWorks
    6. Click on "Test Connection" button.
    7. Click "OK" if test is successful.

    Import process together within the data flow for MS Access data source
    1. Drag an "OLE DB Source" component to the "Data Flow" task.
    2. Right click this component and select "Edit"
    3. Select the name of the MS Access connection manager.
    4. Select the name of the table you wish to import.
    5. Click "OK"

    SQL Server destination:
    1. Drag an "OLE DB Destination" to the "Data Flow" pane.
    2. Select the "OLE DB Source" component again.
    3. Drag the green arrow from the "OLE DB Source" component to point to the "OLE DB Destination"
    4. Right click the "OLE DB Destination"  component and select "Edit".
    5. Click on "New" if you wish to create a new SQL Table.
    6. Over type "OLE DB Destination" with a meaningful table name
    7. Click "OK"
    8. Click the "Mappings" tab to check the mappings from the MS Access table columns to the SQL Server table columns are OK.

    Execution: 
    1. Click the green right pointing arrow to start debugging.
    2. On successful execution the components will light up green.
    Here is a further SSIS tutorial: http://msdn.microsoft.com/en-US/library/ms169917(v=SQL.90).aspx
    Further link on using OLE DB Source:  http://msdn.microsoft.com/en-US/library/ms139767(v=SQL.90).aspx
    "Microsoft Office 12.0 Access Database Engine OLE DB Provider" is for MS Access 2007 connectivity: http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

    I hope the above helps!


    Regards, Amber zhang
    • Marked as answer by Stephanie Lv Wednesday, September 28, 2011 3:12 AM
    Thursday, September 22, 2011 8:03 AM