connect SSMS to MS Access

I have SQL Server 2005 and Microsoft Access 2010 installed on the same computer.   Is it possible to connect SSMS to the MS Access database?  If so, how can I do this?    If possible I would like to create queries in SSMS to run against the MS Access database.   Thanks.
LVL 3
fjkaykr11Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Dev-Soln LLCCommented:
not sure about 2005, don't have it installed, but in 2008 they have a feature called "linked servers" which you can configure to link SQL Server to Access and run queries against the Access database from within SQL Server.

Found this doing a Bing search:
fjkaykr11Author Commented:
Dale I figured it out.   I had to enable permission for linked server using the Surface Area Configuration Features and then run the following query below* I had trouble at first because I am using a newer version of MS Access and had to find the old Northwind .MDB database.  

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
             'C:\Program Files (x86)\Microsoft Office\Office15\Northwind.mdb';
             'admin';'',Customers)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Dev-Soln LLCCommented:
and I don't normally post links myself, but in this case, since I have not used 2005 in a number of years, I felt it appropriate to post the link to the article, as it was very informative.

glad I could help.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

PatHartmanCommented:
Just because you can do something, doesn't mean that you should.  I don't know what you are doing this for but it will be very fragile due to the way you have to hard-code links to the Access database.  It would be far better to convert the Access BE to SQL Server and have the Access app link to SQL Server tables.  You have a lot more flexibility and it will be very easy to switch between test and production.  Going the other way, it will be much more difficult to swap one BE for another.
Dale FyeOwner, Dev-Soln LLCCommented:
Pat,

There are times when you don't have that option.  I've been working with an Oil/Gas company  which has a production system that uses Access as the BE.  But had the need to pull data from that system into SQL Server on a nightly basis.  Using the linked server method allowed me to link the Access tables into SQL Server and then use SQL Server stored procedures to pull data from the Access via scheduled SQL Server job.
PatHartmanCommented:
Dale,
I understand that sometimes you just have to do something like this.  I was simply suggesting that if the OP had any flexibility, he should look at going the other way.
fjkaykr11Author Commented:
Ordinarily I don't like awarding full points for when someone posts a link for a solution. However, in this case the hyperlink you provided was very informative, thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.