Solved

How to link to an Access database from SQL

Posted on 2010-09-22
12
310 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
[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
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 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