Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Problem with exporting an Access database into a SQL Server (be it 2005, 2008 or 2012)

Answered Problem with exporting an Access database into a SQL Server (be it 2005, 2008 or 2012)

  • Sunday, August 19, 2012 3:17 PM
     
     
    Sign In to Vote
    0

    Hi, 

    Having done this for a while... thought, we have essentially two options, 
    a) using Access's Upsizing Wizard 
    b) using MS SQL Server's built-in DTS capability or its equivalent 

    Background info: 
    The Access I have is 2000 and I have 3 instances of MS Server running, 
    one is 2000, one is Express (2005?) and another SBiz, none of them 
    have the DTS equivalent for importing an Access DB. 

    My computer is: a 64bit Windows 7 Professional with 4GB RAM.

    Solution attempts: 
    Attempted the above a) option, received err msg: "overflow", googled 
    solution at Microsoft site is no longer available. 
    For option b),  none of the SQL Server instance and its management 
    studio express has DTS equivalent. 
    Downloaded an eval copy of  SQL Server 2012 (standard edition), installation failed because .NET FRAMEWORK 4 failed to be installed and err msg is: generic trust issue.  Attempted to install this framework separately failed as well.

    Since my goal is to import an Access database to a SQL Server (not too specific about its version, preferably 2008 and above), so, which SQL Server version (eval/trial ) might have a better chance to be installed with either DTS or SSIS?

    Thanks in advance. 

     

All Replies

  • Monday, August 20, 2012 6:36 AM
     
     
    Sign In to Vote
    0

    Hello,

    Follow the links below. It may help you.

    http://stackoverflow.com/questions/6709670/how-do-i-import-ms-access-data-into-sql-server-using-ssis-package

    http://blogs.technet.com/b/vipulshah/archive/2011/06/07/importing-data-from-ms-access-2010-64-bit-accdb-database-into-sql-server-using-ssis.aspx

     
  • Tuesday, August 21, 2012 9:46 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi knowledgenotebook,

    Windows 7 operation system has build in the .NET 4.0 Framework , you do not need to install it in fact, if you still have the issue, please post the issue to DataBase Engine forum: http://social.technet.microsoft.com/Forums/en-US/sqldatabaseengine/threads  

    To connect an Integration Services package to a Microsoft Office Access data source requires an OLE DB connection manager and a data provider. The data provider that you use depends on the version of Access that created the data source:

    1. For Access 2003 and earlier, the package requires the Microsoft Jet OLE DB Provider.
    2. For Access 2007, the package requires the OLE DB provider for the Microsoft Office 12.0 Access Database Engine.

    You can create an OLE DB connection manager and select the corresponding data provider from either the Connection Managers area in SSIS Designer or from the SQL Server Import and Export Wizard.

    Please note that On a 64-bit computer, you must run packages that connect to Microsoft Access data sources in 32-bit mode. Both the Microsoft Jet OLE DB Provider and the OLE DB provider for the Microsoft Office 12.0 Access Database Engine are only available in 32-bit versions. For more information about it, please refer to: http://msdn.microsoft.com/en-us/library/cc280478(v=sql.100).aspx

    Please feel free to ask if you have any question.

    Thanks,
    Eileen

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.