none
Microsoft Office 12.0 Access Database Engine OLE DB Provider does not appear in list of Data Sources in SQL Server Import and Export Wizard

    Question

  • Environment
    Windows 7
    Microsoft SQL Server 2008 R2

    I am trying to use the SQL Server Import and Export Wizard.

    The instructions on the help page regarding How to Connect to an Access Database under

    To create an OLE DB connection from the SQL Server Import and Export Wizard

    instruct me -

    "On the Choose a Data Source page, for Data Source, select Microsoft Office 12.0 Access Database Engine OLE DB Provider, and then configure the connection as appropriate."

    My system did not offer me Microsoft Office 12.0 Access Database Engine OLE DB Provider as an option.

    I have then downloaded and run 2007 Office System Driver: Data Connectivity Components.  I have also then restarted the computer.

    I still don't get the MO12 ADE OLE DB P option.

    On another help page I have read -

    On a 64-bit computer, Integration Services installs the 64-bit version of the SQL Server Import and Export Wizard (DTSWizard.exe). However, some data sources, such as Access or Excel, only have a 32-bit provider available. To work with these data sources, you might have to install and run the 32-bit version of the wizard. To install the 32-bit version of the wizard, select either Client Tools or Business Intelligence Development Studio during setup.

    As I have already set up, how do I step back and install the 32-bit version of the wizard, if that is what I have to do? 

    Suggestions as to how to get at my Access data, through this installation of SQL Server 2008 R2, would be welcome.

    Thank you

    Philip Hunt
    Medina, Kwinana
    Perth, Western Australia

    Friday, June 18, 2010 11:36 AM

All replies

  • Assuming SQL Server 2008 R2 is X64 

    MS recently released x64 bit driver for Jet it can be used to connect to Office 2007 Access.

    Download it from here

    http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en 

    provider is

     Provider=Microsoft.ACE.OLEDB.12.0

    Friday, June 18, 2010 1:40 PM
  • I tried Chirag's idea but I got the message -

    "Yoiu cannnot (sic) install the 64-bit version of Microsoft Access Database Engine 2010 because you currently have 32-bit Office products installed."

    It then goes on to tell me about uninstalling 32-bit Office etc.  As I do not have the funds to afford a new copy of Access, I would seem to be back to my problem.

    This error occurred when I downloaded and ran AccessDatabaseEngine_X64.exe, which is what I thought your instructions were implying I should do.

    More suggestions please.

    Friday, June 18, 2010 2:58 PM
  • I then tried loading the 32-bit version from the same download page and the installation of that failed.

    Help please.

    Friday, June 18, 2010 3:06 PM
  • What edition of SQL 2008 R2? (Express, Standard or Enterprise)

    Are you running X64 or X32 windows 7?

     If you are running X64 Windows 7 then another alternative is to

    install 32 bit version of SQL Server express side by side with your X64 SQL and create a linked server to access 32 bit SQL Express.


    1) Install SQL Server Express 2005/2008, 32-bit
    2) Configure both SQL Servers to use distributed queries
    3) Create a database with a stored procedure in SQL Server Express to handle OLEDB requests
    4) Link up SQL Server Express to SQL Server x64
    5) Use  MS Access from SQL Express 32 bit

    Friday, June 18, 2010 3:14 PM
  • I am using SQL Express on Windows 7 Home Premium system - 64-bit.

    I'm sorry Chirag but that all just went in the too hard basket for me.  Though my computer expertise are fairly good (ex mainframe applications programmer with 2nd and 3rd GLs), I have always been an applications programmer, not a systems person, and that just crossed the line for me.

    I suppose the bottom line of my query is that the instructions, as written by Microsoft, say that I should be able to simply access my Access database by using the SQL Server Import and Export Wizard and choosing my Data Source as  Microsoft Office 12.0 Access Database Engine OLE DB Provider.  This doesn't work, as that Data Source is not in the offered list.  My expertise with this is at the Wizard using level, so that is the level where I need a solution.  I was so pleased to have found the Wizard, which wasn't so clearly available with the 2005 SQL version I was trying to use on my old PC, but that now is seeming like a false hope.

    All I am trying to do is recover my Access data to the SQL Server database 'format' and move ahead.  I have been held up in my website building for nearly 6  months with this problem and have even bought my new computer hoping that would help me get the SQL working properly, which it wasn't on my old XP machine, and get my Access held data across to SQL, thus then being able to move ahead with ASP.NET 3.5 etc.

    More wizard related / baby step formatted suggestions, including URLs for downloads of any necessary software,  please.

    With thanks in anticipation.

    Philip

    Friday, June 18, 2010 3:38 PM
  • Philip, I am sorry

    I have SQL 2008 x64 devloper edition on Windows 7 x64 Ultimate. I also have office 2007 and access 2007 so

    I do see Office 12 Access database engine OLEDB provider in my import export wizard on my machine so instructions are correct.  Don' t know how that list is populated?

     

    Friday, June 18, 2010 4:06 PM
  • In order to install the 32-bit "bits" for SSIS/SQL, you just have to "modify" your installation to ensure you've installed the "complete" management tools and/or BIDS.

    After that, you'll then have to (manually) locate the wizard in the Program Files (x86) folder in order to run the 32-bit version of the wizard.


    Todd McDermid's Blog Talk to me now on
    Friday, June 18, 2010 5:10 PM
    Moderator
  • Todd McDermid can you tell us where the 32bit version of the DTSWizard.exe resides on the HDD if you selected to install thw 32bit version of the wizard during your installation.

    many thanks

    Colin 

    Tuesday, May 22, 2012 12:20 PM
  • It should be located at %ProgramFiles(x86)%\Microsoft SQL Server\100\DTS\Binn

    Todd McDermid's Blog Talk to me now on

    Tuesday, May 22, 2012 3:35 PM
    Moderator
  • Have you tried installing via the command prompt with the "passive" switch?

    C:\> AccessDatabaseEngine_x64.exe /passive

    More info:

    http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx

    Friday, August 31, 2012 6:07 PM