Trying to create a linked server to a Microsoft Access 2013 database in SQL 2012

• system243trd

  Right there with Babe

  Points: 758

  More actions

  September 15, 2015 at 1:51 pm

  #311553

  I am trying to configure (via SSMS) a linked server to a Microsoft Access 2013 database that I have on my desktop.

  When I select New Linked Server, the only option I have available for the Provider are (which I think could be used):

  Microsoft OLE DB Provider for SQL Server

  Microsoft OLE DB Provider for Simple Provider

  I have selected Microsoft OLE DB Simple Provider and entered the following criteria

  Product Name: ACCESS

  DataSource : C:\test.mbd

  When I select OK, I receive the following warning:

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

  Please can somebody advise if I am using the correct Provider and if I need to enter any further configuration.

• Lowell

  SSC Guru

  Points: 323522

  More actions

  September 15, 2015 at 2:33 pm

  #1826907

  I've never gotten a DSN-less Access linked server to work.

  the link below will get you rolling, but if you have Office installed on your local machine, a side affect is that Office will report it needs to repair office every time you open a word document or excel document after you install the 64 bit versions of the access

  on a server with nothing installed, it's no problem, but local machines and development boxes make this a real pain...

  i just tested this yet again, and it worked for me again with an .accdb file i created:

  http://www.sqlservercentral.com/Forums/Topic1687011-392-1.aspx

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!