asked on 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.
Microsoft SQL Server 2005Microsoft Access
Last Comment
Star Gazr1
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
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.
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.
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.
ASKER
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.
glad I could help.