PLEASE READ FIRST!

I've now been blogging here since 2007! ...Wow, has it been that long already?! Over the years, I've covered a wide array of subjects from scripting, troubleshooting, to the occasional hobby project. I have literally heard from thousands of you over the years with questions or even just offering me a bit of thanks!

Recently, I quit my full-time job and started work on an IT business, Rhythm IT Solutions, helping other IT departments manage their IT environments. We're offering consulting & professional services (Office 365 Migrations, Network Discovery, Firewall Management, etc...) as well as Endpoint Management (AV, Patch Management EDR, Managed Backup, etc...)



If there is something (on my blog or not) that you could use some additional help with, I would LOVE to hear from you! Please feel free to reach out to me using the contact links here or the contact form on Rhythm's website.

For those wondering, I do plan on continuing my work on the blog. So don't worry! There are plenty of Greg Strike posts yet to come! 😊 Thanks for stopping by!

PLEASE READ FIRST!




I had some 32-bit Access 2007 Databases that I wanted to configure as Linked Servers on my 64-bit SQL 2008 R2 server. Seemed simple enough, however, I ran into a couple bumps along the way. For instance, every time I tried to add the database I got the following error:

The linked server has been created but failed a connection test. Do you want to keep the linked server?

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "[servername]".

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "[servername]" returned message "Could not find installable ISAM.". (Microsoft SQL Server, Error: 7303)

Quick Note: The error above was easily fixed by not entering anything into the Provider String field. If that doesn’t work follow along below to see how I was able to get it to work.

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.

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "[servername]" reported an error. The provider did not give any information about the error.
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "[servername]". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)

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)

That’s it! It should be working now.



PLEASE READ FIRST!

I've now been blogging here since 2007! ...Wow, has it been that long already?! Over the years, I've covered a wide array of subjects from scripting, troubleshooting, to the occasional hobby project. I have literally heard from thousands of you over the years with questions or even just offering me a bit of thanks!

Recently, I quit my full-time job and started work on an IT business, Rhythm IT Solutions, helping other IT departments manage their IT environments. We're offering consulting & professional services (Office 365 Migrations, Network Discovery, Firewall Management, etc...) as well as Endpoint Management (AV, Patch Management EDR, Managed Backup, etc...)



If there is something (on my blog or not) that you could use some additional help with, I would LOVE to hear from you! Please feel free to reach out to me using the contact links here or the contact form on Rhythm's website.

For those wondering, I do plan on continuing my work on the blog. So don't worry! There are plenty of Greg Strike posts yet to come! 😊 Thanks for stopping by!

PLEASE READ FIRST!





Gregory Strike

Husband, father, IT dude & blogger wrapped up into one good looking package.