Solved

How to link to an Access database from SQL

Posted on 2010-09-22
12
308 Views
Last Modified: 2013-11-28
I currently have an Access 2007 front end connected to a SQL 2005 back end.  I need to connect to another Access database to get additional data.  How do I link from SQL to the access database? or is there a way to Link to the Access data from the Access 2007 front end?
0
Comment
Question by:summitMIS
12 Comments
 
LVL 21

Expert Comment

by:chapmanjw
ID: 33738826
If you want to get Access data from the SQL side, you could create an ODBC connection to the Access db and then setup the ODBC connection as a Linked Server in SQL.

If you want to get Access data from another Access database, you can simply do so in Access by going to import external data and linking to the source rather than importing it into the database.  You can connect either to the physical location of the file or setup an ODBC connection to it and then connecting to the ODBC connection in Access.
0
 

Author Comment

by:summitMIS
ID: 33738871
From my Access 2007 DB I do not get the Link Table option, only Import Table.  I am guessing this is becuase the current Access 2007 DB is connected to a SQL backend.

I setup the Linked Server in SQL Mgmt Consle, but when I try to query the tables I get an error that says "either the columns do not exist or I do not have permission to the table".  I can see all the tables using the sp to show all tables.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33739930
I have 2007 but no sql in order to test out what you have done

Perhaps though when trying to connect to the other db, try using vba to see if that helps

On error resume next

public sub TestMeLink

err.clear
DoCmd.TransferDatabase acLink, "Microsoft Access", "c:\myotherdb.mdb", acTable, "Nameoftable", "Nameoftable"
if err.number <> 0 then
    msgbox "Failed to link " &  vbcrlf & err.description
end if

end sub

Place that code in a module, fill in your bits and run it (F5 on code or something)
then if it fails an error should be shown, perhaps gives you the reason why it can or cant link
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 65

Expert Comment

by:rockiroads
ID: 33739936
did anybody here have trouble gettingi into ee? I tried to submit my last post over an hour ago but kept getting slashcontrol website coming up. no spyware, no host redirection. but now it seems fine obviously
0
 
LVL 4

Expert Comment

by:joevi
ID: 33740470
So the following SP shows all of the Access tables?
exec sp_tables_ex 'LinkedServerName'
Did you set up Security (permissions) in the Linked Server Definition? With a default 'admin' login?
Can you provide the query you used to access a Linked Server table?
FYI: You can use the Jet OLEDB provider to avoid ODBC.
0
 

Author Comment

by:summitMIS
ID: 33744423
Here is the linked definition:

EXEC sp_addlinkedserver @server = 'UAData',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\SummitLive2009.mdb'

I can see all tables using sp_tables_ex...

The error when trying to query a table is the following:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "tblemployees". The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "UAData" indicates that either the object has no columns or the current user does not have permissions on that object.
0
 

Author Comment

by:summitMIS
ID: 33747872
Follow up info from additional  testing:
It appears the issue is permissions on the existing Access database I am trying to access/link.  I created a new database and saved it in the same folder so I could rule out folder permissions etc.  I was able to link to the new database and successfully query the data.  So I know I can access that folder and it has to be related to the database I am trying to link to and query.  Admin is not a user on the database I am trying to quary, is there a way to specify the Workgroup file so I can use a qualified username with access to the database?  or is there another method.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33748112
so this db you are connecting to has ULS (user level security) setup? does it not prompt you?

0
 

Author Comment

by:summitMIS
ID: 33749752
No, there are no prompts.
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 250 total points
ID: 33750297
So if its secured then maybe open up from the mdw?

http://support.microsoft.com/kb/163002
0
 

Author Comment

by:summitMIS
ID: 33753972
Its a security issue on the database...if I copy all the tables into another new Access database the linked server connection work fine.  I will have to use the security login as shown in post above.  Case closed!  Thanks everyone.
0
 

Author Closing Comment

by:summitMIS
ID: 33753986
Removed the user level security on the database and it worked fine.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

777 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