Linked Servers in SQL Server Standard 2014 to MS Access 2007 Database

http://www.gregorystrike.com/2010/11/15/access-2007-database-as-linked-server-in-sql-2008-r2/

Step 1. Install the Access 2010 Database Engine (32-bit)
This is a free download directly from Microsoft.

The Access 2010 Database Engine is required because Windows or SQL server do not include the engine part of their installs. I am using the 32-bit engine because my databases are 32-bit. Could be wrong, but, I don’t believe the 64-bit engine can read a 32-bit database… At least Access 2007 64-bit couldn’t read my 32-bit databases…

If you skip this Microsoft Office 12.0 Access Database Engine OLE DB Provider will not appear in the drop-down box when you go to create the Linked Server.

Step 2. Allow InProcess For The Microsoft.ACE.OLEDB.12.0 Provider
After I was created a Linked Server (as we will do in the next step) I noticed that when attempting to expand the tables in the database the following error appeared. Step 2 fixes this.

In Microsoft SQL Server Management Studio expand Server Objects / Linked Servers / Providers and Right-Click on Microsoft.ACE.OLEDB.12.0 and choose Properties.

Place a checkmark in the “Allow inprocess” checkbox and click Ok.

Step 3. Create the Linked Server
Right-Click on “Linked Servers”
Click “New Linked Server…”

Enter the following fields:
Linked Server: MYLINKEDSERVER (You choose what you want it to be called)
Server Type: Other data source
Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider
Product Name: Access
Data Source: File Location (Enter the location of the Access database, UNCs are allowed just make sure share and file permissions are ok)
Provider String: (empty)

I installed the 32bit Database Engine but I still don't see the "Microsoft Office 12.0 Access Database Engine OLE DB" in the Provider dropdown list when I go to add a new Linked Server

Also, I do not see "Microsoft.ACE.OLEDB.12.0" in the Providers list under Linked Servers in the tree view.

I'm not sure if Access 2007 still a JET database. Could you find the JET engine in the providers list?

Nope, that wasn't in the list either.

The correct name should be "OLE DB for Microsoft JET ..." (Microsoft.Jet.OLEDB.4.0).
Anyway I was checking and looks like that was the way to connect to versions previous that MS Access 2007 (.mdb files). So, since Access 2007 (.accdb files) you should really use the "Microsoft.ACE.OLEDB.12.0".
Can you post here how's your linked server configuration looks like?

You mean this? See attached screenshot...
Doc1.docx

Where would I find the Linked Server Configuration? Not sure where to look for that exactly.

Here's what I got when I tried to run that query:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

Also, the 32bit DB Engine was the Access Database Engine. I am running office 32bit but my system is a 64bit.

This is what I am running for SQL

Product Version:          12.0.4213.0
Product Name:             SQL Server 2014
Product Level:            SP1 + Security update(GDR)
Product Edition:          Standard Edition (64-bit)

Ok now the query runs fine. Does that mean I can now set up a linked server to the Access Database? I still don't see the jet engine in the list of providers though.

The machine is a 64bit as well as the SQL server 2014 version. The only thing that is 32bit is office 2007.

Well it did create the linked database but when I try to expand the tree to list all the tables I get the attached error. I also included the properties of the new linked DB.
Doc2.docx

When I try to test the connection I get this error....
Doc3.docx

The machine is a 64bit as well as the SQL server 2014 version. The only thing that is 32bit is office 2007.

I think you really need to install the Office 2007 64bit.

Do you think that will solve the problem of the provider for A2007 not showing up in the list?

Thank you for your help!