none
Conversion from Access 2003 to sql server 2005

    Question

  • Hello, I have an exiting project in visual basic 6.0 and access 2003.now i want to use the latest technology so i decide to convert the back end to sql server 2005 and front end to asp.net.My question is that i searched many conversion tool but most of them are just converting data table.I want to convert access table,forms,query,reports,macro etc..into sql server triggers,views,functions,procedures etc..automatically. Is there any conversion tool to convert the database? Please let me know.
    Tuesday, December 06, 2011 3:50 PM

Answers

  • Hi Bhavik Pandya,

    As Jeff Wharton pointed, you can import the Access tables into SQL Server database by using Upsizing Wizard. Moreover there are two more options for you such as Linked Server and OPENDATASOURCE. There are other options which might help you to convert Access  database to SQL Server 2005 database as well. If you want to use import / export wizard or SSIS methods, please refer to the details as below:

    • Use the import / export wizard to convert MS Access tables to SQL Server. Please follow the steps in this article.

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

    There are two articles, please refer to:
    1. SSIS tutorial
    2. "Microsoft Office 12.0 Access Database Engine OLE DB Provider" is for MS Access 2007 connectivity


    Regards, Amber zhang
    Thursday, December 08, 2011 6:25 AM

All replies

  • There is no tool that will do all these functions for you.

    Have a read of the following for some information on this topic http://support.microsoft.com/kb/237980 and http://office.microsoft.com/en-us/access-help/move-access-data-to-a-sql-server-database-by-using-the-upsizing-wizard-HA010275537.aspx


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Thursday, December 08, 2011 3:08 AM
  • Hi Bhavik Pandya,

    As Jeff Wharton pointed, you can import the Access tables into SQL Server database by using Upsizing Wizard. Moreover there are two more options for you such as Linked Server and OPENDATASOURCE. There are other options which might help you to convert Access  database to SQL Server 2005 database as well. If you want to use import / export wizard or SSIS methods, please refer to the details as below:

    • Use the import / export wizard to convert MS Access tables to SQL Server. Please follow the steps in this article.

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

    There are two articles, please refer to:
    1. SSIS tutorial
    2. "Microsoft Office 12.0 Access Database Engine OLE DB Provider" is for MS Access 2007 connectivity


    Regards, Amber zhang
    Thursday, December 08, 2011 6:25 AM