4

I just installed SQL Server 2012 to test differences and make sure it is ok for my Work. I may not be a pro at SQL, but I don't see how to add a Link to Jet database on my server. I have imported Jet Databases, but when going thru the GUI to Link a JET Database I see no Provider for JET. I am running Access 2003 (I don't like the 2008+ GUI)

I searched an found nothing simular to my issue; at least nothing I understood. Nothing mentioned how to add Jet db Provider.

I ran a connection string to the Jet Database thru QA:

sp_addlinkedserver 'USGEScrub', 'Access', 'Microsoft.Jet.OLEDB.4.0', 
'D:\Lead$\SupList\USGEScrub.mdb'

The connection string works till I try to access the data and then I get this the errorand of course if I click the links nothing shows up:

TITLE: Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked
server "USGEScrub". (Microsoft SQL Server, Error: 7302)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=7302&LinkId=20476

Crazyd
  • 406
  • 2
  • 7
  • 15
  • 1
    Is your app 64-bit? There's no 64-bit provider for `Microsoft.Jet.OLEDB.4.0` – Robert Harvey Jun 25 '12 at 20:07
  • Windows 7 is 64-bit; and probably SQL is 64-bit (Don't recall), but Access is 32-bit. By App what do you mean? – Crazyd Jun 25 '12 at 20:41
  • This is a programming Q&A website, so I assume you wrote an application? Otherwise, your question is probably better suited for http://superuser.com – Robert Harvey Jun 25 '12 at 20:49
  • Well honestly I typically Program in Access, but I'm currently learning how to transfer stuff to SQL and Using Access as a front end to SQL. I can move things from Access to SQL and back fairly quickly, but it'd be easier to just directly Link to Access database for small amounts of data. VBA I'm a pro - where SQL I am yet a newb. But thank you I will try Superuser.com... Is there no SQL 2012 Link to Earlier Access? – Crazyd Jun 25 '12 at 21:05
  • Honestly I know VBA isn't a real popular method of programming, but I learned it so well when programing in something else it takes a lot more time for me to learn the new syntax. VB I can write in due to the simularities, but learning the SQL-Server syntax takes more time than I have. And honestly I thought T-SQL was a Language. – Crazyd Jun 25 '12 at 21:36
  • As a “general” rule you don’t need nor want to link SQL to an Access database. You can MOST certainly link the Access “front end” to SQL server and bit size don’t matter. However going the reverse is in general a bad idea nor is it required, especially if you going to continue using Access as the UI “front end” part. As others pointed out, if you running x64 SQL server, then you have to install a x64 bit version of Access (or at least the new JET (now called ACE) data engine. And when it comes to office development, VBA remains as popular as ever. – Albert D. Kallal Jan 13 '15 at 22:00

3 Answers3

2
  1. download the 2010 Access database engine from Microsoft
  2. Allow "InProcess"
  3. Add linked server using the ACE OLEDB provider.

more details here

FistOfFury
  • 6,735
  • 7
  • 49
  • 57
0

I had a similar problem and in my case it turned out to be related to the permissions on the %TEMP% folder for the account under which the SQL Server service was running. For more details see my answer here.

Community
  • 1
  • 1
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
0

Sometimes this problems occurs in computers x64 with office x84 versions installed. Try this:

Install pasive OleDb Driver x64

manugomezmq
  • 1
  • 2
    “While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.” – Divyang Desai Nov 16 '16 at 16:43