SQL Server can't create linked server to Access

Posted on 2011-05-09
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
    LVL 10

    Accepted Solution


    Author Comment

    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

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    729 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

    20 Experts available now in Live!

    Get 1:1 Help Now