none
Check if the Access Database is Installed and Configured

    Question

  • All, I want to check if the Access Database Engine is installed and linked to SQL Server. On my development machine (x64-bit) the following queries both show the Access Engine to be available as a linked server

        SELECT srv.provider
        FROM sys.servers As srv
        WHERE is_linked = 1;

    and

        EXEC sp_linkedservers;

    but these do not work (that is they do not return the Access Engine as a linked server, although it is installed and configured) on another x64-bit machine.

    Why is this inconsistency happening and is there another SQL Query I can use to perform this check?


    "Everything should be made as simple as possible, but not simpler" - Einstein

    Tuesday, April 17, 2012 10:52 AM

Answers

  • As I stated above I do not know of a query to get a list of the providers - however if you look in the registry at

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers and check for the key Microsoft.ACE.OLEDB.12.0 this will tell you that it is installed.  If you have a 32-bit version installed on a 64-bit box you would need to look under the Wow6432Node, that key would be HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers.

    You would have to make sure that both SQL Server and the ODBC/OLEDB driver are the same bitwise either both x64 or x32.

    I hope this helps.

    Noral


    Thursday, April 19, 2012 4:59 PM

All replies

  • did you install Access and SQL Server both using the same platform (32-bit vs. 64-bit).

    you can't call a 32bit Access from within SQL Server 64-bit.

    see this thread 64-bit ODBC support for Linked Server queries

    • Proposed as answer by Peja Tao Wednesday, April 18, 2012 5:35 AM
    Tuesday, April 17, 2012 7:23 PM
  • Yes. I have installed using the same platform (both x64).

    This seems to be a problem with the queries above and not the installation.

    Thanks for your time.


    "Everything should be made as simple as possible, but not simpler" - Einstein

    Wednesday, April 18, 2012 8:03 AM
  • Hello,

    Please double check to make sure you did install the x64 version of Microsoft Access Database Engine here - http://www.microsoft.com/download/en/details.aspx?id=13255.  Please note that only one version x64 --OR-- x86 can be installed, not both.

    Once it is installed and you have checked for updates, there is a SP, make sure SQL Server show's it registered.

    Here is a SQL Server x64 server with the Microsoft Access Database Engine x32 installed - see it does not show up.

    Here is the same server with the Microsoft Access Database Engine x64 installed - see how it shows up.

    On any server you want to use this link you must have the software installed and you must register a linked server for every database you want to access, no pun.

    The query to sys.servers returns current information about current linked servers.  The information being returned for your query is likely for that SQL Server instance.  The query does not return all the providers that are installed.  The same can be said for the second query as well.  I do not know of a query that will return all the Providers that are installed for SQL Server - I believe that is a registry call that SSMS makes. (Readers do please comment on this - I would like to know)  It is best to use SSMS to verify.

    Here is a script how to create a connection to an Access 2010 database once OLEDB v12 shows up.  This example is for an Access database located at 'C:\Temp\Test.accdb' and the creditials being pass are the some ones that you use to connect to SQL Server.  Please do make sure that the @srvproduct is unique:

    /****** Object: LinkedServer [TEST ACCESS 2010] Script Date: 04/18/2012 15:18:54 ******/ EXEC master.dbo.sp_addlinkedserver @server = N'TEST ACCESS 2010', @srvproduct=N'Test Access 2010 database - this should be unique', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:\Temp\Test.accdb'

    /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST ACCESS 2010',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL GO EXEC master.dbo.sp_serveroption @server=N'TEST ACCESS 2010', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'TEST ACCESS 2010', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'TEST ACCESS 2010', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'TEST ACCESS 2010', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'TEST ACCESS 2010', @optname=N'rpc', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'TEST ACCESS 2010', @optname=N'rpc out', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'TEST ACCESS 2010', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'TEST ACCESS 2010', @optname=N'connect timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'TEST ACCESS 2010', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'TEST ACCESS 2010', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'TEST ACCESS 2010', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'TEST ACCESS 2010', @optname=N'use remote collation', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'TEST ACCESS 2010', @optname=N'remote proc transaction promotion', @optvalue=N'true' GO

    I hope this helps.

    Noral

    • Proposed as answer by Noral Kuhlmann Wednesday, April 18, 2012 10:28 PM
    Wednesday, April 18, 2012 10:28 PM
  • Thanks very much for your time. I am aware of how to use the Access Database Engine (but thatnk you for the clarification). The problem is that I need to check if it is installed programatically from C#. If it is installed then I want to automatically consifure it using

    -- These can be run as one batch.
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
    GO 
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 
    GO
    
    -- Run each of the following queries individually.
    EXEC sp_configure 'show advanced options',1
    reconfigure
    EXEC sp_configure 'Ad Hoc Distributed Queries', 1
    Reconfigure
    

    then test using

    SELECT * FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;DATABASE=C:\ThisFolder\ThatFolder\SomeExcelFile.xls', 'Select * from [FieldName]');
    GO
    SELECT * FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', ' C:\ThisFolder\ThatFolder\SomeMDBFile.mdb'; 'admin';'',TableName);
    GO
    

    to confirm that it can read from Access and Excel files.

    Thanks again for your time.


    "Everything should be made as simple as possible, but not simpler" - Einstein

    Thursday, April 19, 2012 9:01 AM
  • As I stated above I do not know of a query to get a list of the providers - however if you look in the registry at

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers and check for the key Microsoft.ACE.OLEDB.12.0 this will tell you that it is installed.  If you have a 32-bit version installed on a 64-bit box you would need to look under the Wow6432Node, that key would be HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Providers.

    You would have to make sure that both SQL Server and the ODBC/OLEDB driver are the same bitwise either both x64 or x32.

    I hope this helps.

    Noral


    Thursday, April 19, 2012 4:59 PM