?
Solved

SQL Server can't create linked server to Access

Posted on 2011-05-09
3
Medium Priority
?
241 Views
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?
~Peter

SQLServer-AccessLink-Error.bmp
0
Comment
Question by:PeterFrb
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
Thorrsson earned 2000 total points
ID: 35725229
0
 

Author Comment

by:PeterFrb
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
0
 

Author Closing Comment

by:PeterFrb
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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

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