connect SSMS to MS Access

fjkaykr11
fjkaykr11 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
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:
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)
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017

Commented:
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 LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.
Distinguished Expert 2017

Commented:
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.

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial