How to link to an Access database from SQL

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?
summitMISAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
So if its secured then maybe open up from the mdw?

http://support.microsoft.com/kb/163002
0
 
chapmanjwCommented:
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
 
summitMISAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rockiroadsCommented:
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
 
rockiroadsCommented:
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
 
joeviCommented:
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
 
summitMISAuthor Commented:
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
 
summitMISAuthor Commented:
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
 
rockiroadsCommented:
so this db you are connecting to has ULS (user level security) setup? does it not prompt you?

0
 
summitMISAuthor Commented:
No, there are no prompts.
0
 
summitMISAuthor Commented:
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
 
summitMISAuthor Commented:
Removed the user level security on the database and it worked fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.