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
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
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.
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.


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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now