none
SQL Server 64 bit connecting to MS Access

    Question

  • Hello,

    I have a machine with Windows 7 64 bit running:
    - SQL Server 2008 64 bit
    - MS Access 2003

    I would like to have the MS Access database as a linked server in my SQL Server instance. I've seen serveral suggestions in this forum.

    I installed:
    AccessDatabaseEngine.exe
    and
    AccessDatabaseEngine_x64.exe

    I tried a suggestion to create a linked server with
    EXEC sp_addlinkedserver @server = 'MSACCESS', @provider = 'Microsoft.ACE.OLEDB.12.0', @srvproduct = 'Microsoft OLE DB Provider for Jet',
    @datasrc = 'E:\MSAccess\db1.mdb'
    or
    EXEC sp_addlinkedserver @server = 'MSACCESS', @provider = 'Microsoft.Jet.OLEDB.4.0',  @srvproduct = 'Microsoft OLE DB Provider for Jet',
    @datasrc = 'E:\MSAccess\db1.mdb'

    and query with:
    select * from MSACCESS...dwh_dim_product
    or
    select * from openquery (MSACCESS, 'select * from dwh_dim_product')

    The first linked server gives the following error with both queries:
    Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MSACCESS".

    The second linked server gives the following error with both queries:
    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.

    Another suggestion I tried:
    - run syswow64\odbcad32.exe
    - System-DNS -> Add -> Driver to MS Access
    - and then give a name and select the MS Access database file
    Then in the SQL Server Manamgement Studio:
    - Server objects -> Linked servers -> New linked server
    - I hoped to be able to use now the above configured ODBC driver to MS Access, but it isn't there or I don't know how. Is this possible ?

    Also I read that it's just not possible to create a linked server from SQL Server 64 bit to MS Access. But this was a message from some time ago, and maybe there is a solution since then.

    I hope someone can help.

    Thanks in advance.
    Regards, André.

    Friday, September 16, 2011 9:18 PM

Answers

  • There is no difference between "different machines" and "same machines". What I meant was that you could consider to uninstall the Access 32-bit if it is not used, so that you can install the 64-bit ACE driver / provider. However, it seems that this workaround does not work for you.

    If this issue is critical to your business, I think that you should contact the Microsoft customer support to express your need - to have both 32-bit and 64-bit ACE driver / provider installed on the same machine.

     

    Thanks,
    Ming.
    WDAC Team, Microsoft.

     


    Pak-Ming Cheung - MSFT
    Saturday, October 08, 2011 2:30 AM

All replies

  • Hi Andre_Analytics,
     
    A 64-bit version of SQL Server needs a 64-bit driver/provider. Since there is no 64-bit Jet, you need download and install the 64-bit version of "Microsoft Access Database Engine 2010 Redistributable". I would suggest you uninstall the existing providers and re-install AccessDatabaseEngine_X64.exe.

    After that, please refer to the following links to create a linked server that points to the Access file:

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

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/273faf1a-77e1-4ffc-8409-befc421f26c0/

    Reference:

    How to get a x64 version of Jet?
    http://blogs.msdn.com/b/psssql/archive/2010/01/21/how-to-get-a-x64-version-of-jet.aspx

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.
    • Marked as answer by KJian_ Friday, September 23, 2011 9:40 AM
    • Unmarked as answer by Andre_Analytics Friday, September 23, 2011 7:36 PM
    Monday, September 19, 2011 6:27 AM
  • Jian, thanks for your answer, but I when I install the AccessDatabaseEngine_X64.exe I get the following error:

    You cannot install the 64-bit version of Microsoft Access Database Engine 2010 because you currently have 32-bit Office products installed. If you want to install 64-bir Microsoft Database Engine 2010, you will first need to remove the 32-bit installation of Office products.

      

    I don't understand this, I understand the driver is intended for MS Access 32 bit, so what's the sense of this installation error ?

    By the way, I have MS Access 2003 and 2007 both installed, but my need is to access the 2003 version.

     

    Thanks in advance, André.

    Tuesday, September 20, 2011 10:36 AM
  • Please refer to this KB article: http://support.microsoft.com/kb/2269468


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.
    Wednesday, September 21, 2011 6:33 AM
  • The article in the link in your last message tells to de-install all Office 32 bits products. That makes no sense to me, because the goal is to connect to the 32 bits MS Access database. De-installing it, is of course not an option then.
    Friday, September 23, 2011 7:36 PM
  • A 32-bit Access database does not mean the 32-bit of “Microsoft ACE OLEDB Provider”. The diver/provider version depends on “who” uses the driver/provider to connect to a data source. In your case, since it’s a 64-bit SQL Server, you need to install the 64-bit version of “Microsoft ACE OLEDB Provider” to connect to an Access file (32-bit or 64-bit) from SQL Server.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.
    Monday, September 26, 2011 6:07 AM
  • But I tried to install the 64 bits driver, but it is giving me an installation error, saying I have to de-install the 32 bits products. So the 64 bits driver doesn't work.

     

    Monday, September 26, 2011 7:12 PM
  • The previous  KB article explains the cause and provide the solution, you can refer to it.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.
    Thursday, September 29, 2011 8:00 AM
  • You are guiding me in circles. As I said, the described solution for the installation problem of the 64 bits driver is not a real solution, because I have to de-install my MS Access.
    Thursday, September 29, 2011 10:15 AM
  • The problem is that Office 32-bit and 64-bit are not compatible to each other.

    If you don't need MS Access in the same machine, you may uninstall MS Access from that machine, and then install the 64-bit ACE driver.

    If you really need MS Access in the same machine, you may need to uninstall MS Access 32-bit from the machine, then install the 64-bit MS Access and the 64-bit ACE driver.

     

    In case if you must use the 32-bit MS Access, another option is to install the 32-bit SQL Server.

     

    Thanks,
    Ming.
    WDAC Team, Microsoft.

     

     


    Pak-Ming Cheung - MSFT
    Friday, September 30, 2011 1:47 AM
  • The MS Access database is a production environment at the customers site, and it is not an option for me to de-install it and install a 64 bits version. It is also not an option to de-install the existing SQL Server 64 bit and install the 32 bits version, of course. An additional installation of a SQL Server 32 bits, only providing the link to MS Access would be the only option then. I find this quite horrible.

    One question. What is the difference between MS Access on the same machine as SQL Server, or on a different machine. In both cases you say to un-install MS Access and install 64 bit MS Access.

    Saturday, October 01, 2011 12:21 PM
  • There is no difference between "different machines" and "same machines". What I meant was that you could consider to uninstall the Access 32-bit if it is not used, so that you can install the 64-bit ACE driver / provider. However, it seems that this workaround does not work for you.

    If this issue is critical to your business, I think that you should contact the Microsoft customer support to express your need - to have both 32-bit and 64-bit ACE driver / provider installed on the same machine.

     

    Thanks,
    Ming.
    WDAC Team, Microsoft.

     


    Pak-Ming Cheung - MSFT
    Saturday, October 08, 2011 2:30 AM