?
Solved

Create a linked  server to connect ot an Access Database.

Posted on 2016-08-10
26
Medium Priority
?
67 Views
Last Modified: 2016-08-23
I would like to link an Access database into SQl Server.  I think I can do this using a linked server.  And I need and ODCB data source for the Access database.  Can anyone help me with this?
0
Comment
Question by:HLRosenberger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 11
26 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41751034
0
 
LVL 4

Expert Comment

by:Daniel Jones
ID: 41751484
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41754056
Has anyone every done this?   I'm having trouble with security.  I don't understand what username and password to use.  See images.

images
error
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 27

Expert Comment

by:Zberteoc
ID: 41754080
In that image it says that you are trying to connect to an SQL server. Weren't you trying to connect to Access?
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41756242
I want to connect to Access.  I have create a ODBC system Data Source called "Gravity".  What am I doing wrong?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41756404
Use the Jet  Engine drive as in the video.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41761500
Zberteoc - are you saying to use the JET engine in the ODBC datasource?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41761530
Yes.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41761546
ah, I see what you mean.  I'll give that a shot.  Currently, I have Office 2010 32 bit, and I can't install the JET engine download.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41761550
Isn't it there already? It comes with Access and Excel, usually.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41762400
no, it' not in my list.   I'm using SQL 2008, which is 64 bit?  I was thinking that only 64-bit providers would be shown?   I tried to install the 64-bit version of the Access Database engine, but I can't - it tell me that I have to uninstall the 32-bit Office stuff first.

providers
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41762555
Install this:

https://www.microsoft.com/en-us/download/details.aspx?id=13255

And then you can do this:
EXEC sp_addlinkedserver
   @server = 'Test'
  ,@provider = 'Microsoft.ACE.OLEDB.12.0'
  ,@datasrc = '\\srv\public$\path_hete\YourAccess.mdb'
  ,@srvproduct='Access'
GO 

Open in new window

0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41764708
I did install that.  But I do not see JET 4.0 listed as a provider on the Create Linked Server screen.

I'll give that a shot.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41765185
I used your SQL.  However, when I try to open a table using Studio Manager, I get this error below.   Is 'Microsoft.ACE.OLEDB.12.0' 64 bit?  What would I use for 32-bit?  Can 64 SQL server use the 32 bit access drivers?
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41765186
error:image1.png
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41765278
That has to be installed on the same computer where the SQL server is. Also maybe a restart could help.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41765390
ah, that got me further.  Now I get authentication error.

authentication error.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41765458
Try adding the default Admin user:
EXEC sp_addlinkedserver
   @server = 'Test'
  ,@provider = 'Microsoft.ACE.OLEDB.12.0'
  ,@datasrc = '\\srv\public$\path_here\YourAccess.mdb'
  ,@srvproduct='Access'
  ,@rmtuser='Admin'
GO 

Open in new window

Of course you would have to use the real names.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41765477
I got it working.  I had to put the MDB in a folder on the SQL server,.  Then I selected "Be made without a security context" for Authentication.    

Now, how do I issue SQL command against those tables?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41765508
If the liniked server was created successfully then you should be able to see it in the Server Objects > Linked Server node in the Object Bowser in the Management Studio. Make sure you refresh that first. One way is to expand that node and then the linked server you have created > Catalogs > Database name and then you will be able to see all the tables there. Right click on any table > Select top 1000...

For a linked server object you have to use a full reference:

select * from linked_server.database.owner.table

You will see in the first method how it is refereed to. The main idea is that you can use them the same way you would use any SQL database object(table/view). You can select, join, update insert and delete as long as you use the proper name.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41765532
ah, I got it to work! like this:

SELECT * FROM OPENQUERY(gravity , 'select * from cal_agreement')

UPDATE OPENQUERY(gravity, 'select * from cal_agreement where id = 7')  SET frequency_months = 9999
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41765545
in Management Studio, I cannot right click on the table to get records, like with a SQL table.  

Now, next question - How does one connect to the database in code?  Is there a connection string involved?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41765588
Again. you have to remember this concept: objects on a linked server that your SQL server login has access to can be referred to and access like any other objects in any SQL database your login has access to. So let's say that on your SQL server you have 2 database, called DB_1 and DB_2 and you also have the Access linked server named ACC.

Then a you could do this:

select 
    *
from
    DB_1.dbo.some_tble_on_DBA_1 t1
    inner join DB_2.dbo.some_tble_on_DBA_2 t2
	   on t2.col=t2.col
    inner join ACC.AccessDatabaseName..some_tble_on_AccessDatabase a
	   on a.col=t1.col

Open in new window

Maind you the two dots, are not a typo, that is what you are supposed to do. They mean "whatever owner"(default)
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41765640
Isn't it three dots, not two?
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 41765665
If you remove any of the inside elements from the complete 4 way qualification you will have the empty string between the dots. So the following are equivalent given the fact there are no ambiguities:

select * from server_name.db_name.owner.table

remove owner:
select * from server_name.db_name..table

remove database as well:
select * from server_name...table

hence the 3 dots.
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 41766834
Thanks so much for all your help!
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question