SQL Server can't create linked server to Access

Posted on 2011-05-09
Medium Priority
Last Modified: 2012-05-11
From within SQL Server, I have successfully created a linked server to an Access 2010 database, which I know because the "Test Connection" button says I am successful whenever I press it.

However, when I attempt to look at the tables in the catalogue, I get an access-denied error (see screenshot below).  Would this error have to do with a Trust-Center setting in Access, or something else?  I have tried changing the settings in the trust center, putting them at their least restrictive setting; but nothing has defeated the error message.  Any expert advice?

Question by:PeterFrb
  • 2
LVL 10

Accepted Solution

Thorrsson earned 2000 total points
ID: 35725229

Author Comment

ID: 35725394
The commands work excellently, and I'm over ninety percent there (and I will give the response above at least 90% of the credit).  Here's the extra-credit question: my Access database consists largely of linked tables and passthrough queries, which my SQL-Server catalogue does not show.  Will another change of settings give me these?  Interestingly, the SQL catalogue shows any query that uses the JET engine, even if the query is pulling from a nonnative data source.  I'm trying to avoid involving the JET with large foreign tables that will create a big performance lag.  

To be clear, I've executed two processes in SQL Server thus far: "AllowInProcess" and "DynamicParameters".

Cheers, ~Peter Ferber

Author Closing Comment

ID: 35732978
I've decided to close this account and ask a new question, rather than have an "Extra credit", which is not quite fair.  Very good answer here, my question was fully addressed.  Thanks, ~Peter

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Screencast - Getting to Know the Pipeline

749 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