none
Importing data from access to Microsoft SQL Server 2008 R2

    Question

  • I have the following task - to take tables of a MS Access database and load them into tables in MS SQL Server 2008 R2 on a monthly basis.  The SQL tables need to be uppended with the new data (form the MS Access DB) each month.

    The goal is to write a stored procedure(s) that can be run each month to accomplish this.  Seems like it should be a simple task, but my only experience with SQL is relatively simple queries on MySQL workbench.

    So far I've tried something of the form

     insert into [dbo].[sql_table] (columns)
     values ( 
      select (columns)
      from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','filepath\Access_DB.mdb')...[Access_table]
     )

    but I get an error:

    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

    Please help by telling me how best to do this or pointing me towards somewhere to read up on this.  Thanks!

    Thursday, November 01, 2012 12:09 AM

Answers

  • THe besy way to do that is using SSIS, so i guess you need to learn fast!! :)

    An SSIS package can be scheduled to run whenever you want.

    Thursday, November 01, 2012 12:54 AM
  • SQL server has buildin support for importing data from different kind of sources ranging from ODBC sources to Flat File sources and most of them cover the real world scenarios.

    To Import Data from Microsoft Access file prior to office 2007 it is quite straight forward and you can select "Microsoft Access" from data source selection. but if you are dealing with .accdb file then you need to do some additional steps before you can do import of data.

    untitled

    1. First Download OLEDB Provider for Microsoft Office 12.0 Access Database Engine from here.
    2. Install it. after installing it you will see a new option in Source Seclection list as shown in figure.
    3. Select the above option and then click on Properties, On the Data Link Properties page, write the location of database in to the Data Source field, and enter the User name and password if applicable. Click on the Test Connection and make sure it succeeds.
    4. You can edit the other properties like priviliges on Advanced tab of Data Link Properties page.
    5. Click ok and continue with the wizard. to Select Destinition, Destinition Database.
    6. In Next step select Access tables from where you want to copy data.

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Thursday, November 01, 2012 4:43 AM

All replies

  • THe besy way to do that is using SSIS, so i guess you need to learn fast!! :)

    An SSIS package can be scheduled to run whenever you want.

    Thursday, November 01, 2012 12:54 AM
  • Hi,

    I agree with Fanor P.

    I have to perform this task with multiple access database in remote locations on different servers.

    You can either manually use the Import Export wizard SQL Server or run BIDS that will allow you to make up SISS packages.

    One of the beauty of the SISS package is that you can map data types very easily, there is some work involved in developing the packages but well worth the effort once again MS have created a great GUI that has drag and drop and i think the overall package is easy to learn.

    Here is a link to start you

    http://msdn.microsoft.com/en-us/library/ms173767.aspx


    David

    Thursday, November 01, 2012 1:04 AM
  • HI,

    1.Create the package in Import and export wizard.(In that source is MS access and destination connection is SQL server)

    2.Schedule the package in SQL agent Job.

    Below url explains how to do that

    http://www.wiseowl.co.uk/blog/s231/schedule_data_import_in_sql_server_pt1.htm


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, November 01, 2012 1:27 AM
  • Yes you can create the package in Import Export wizard if you have developer or above versions, as SQL Express does not accomodate this.


    David

    Thursday, November 01, 2012 1:30 AM
  • SQL server has buildin support for importing data from different kind of sources ranging from ODBC sources to Flat File sources and most of them cover the real world scenarios.

    To Import Data from Microsoft Access file prior to office 2007 it is quite straight forward and you can select "Microsoft Access" from data source selection. but if you are dealing with .accdb file then you need to do some additional steps before you can do import of data.

    untitled

    1. First Download OLEDB Provider for Microsoft Office 12.0 Access Database Engine from here.
    2. Install it. after installing it you will see a new option in Source Seclection list as shown in figure.
    3. Select the above option and then click on Properties, On the Data Link Properties page, write the location of database in to the Data Source field, and enter the User name and password if applicable. Click on the Test Connection and make sure it succeeds.
    4. You can edit the other properties like priviliges on Advanced tab of Data Link Properties page.
    5. Click ok and continue with the wizard. to Select Destinition, Destinition Database.
    6. In Next step select Access tables from where you want to copy data.

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Thursday, November 01, 2012 4:43 AM
  • Thank you to everyone for your help!!!
    Thursday, November 08, 2012 6:54 PM