How to link to an Access database from SQL

Posted on 2010-09-22
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?
Question by:summitMIS
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
LVL 21

Expert Comment

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.

Author Comment

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.
LVL 65

Expert Comment

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

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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

LVL 65

Expert Comment

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

Expert Comment

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.

Author Comment

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.

Author Comment

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.
LVL 65

Expert Comment

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


Author Comment

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

Accepted Solution

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

Author Comment

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.

Author Closing Comment

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

Featured Post

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

726 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