June 17, 2008 at 8:12 am
I am converting from an in-house ACCESS system to the vendor ACCESS system then (via the vendor S/W) to the vendor's SQL system. The conversion is complete. However, there are tidbits of data that need to be sent from the original in-house system into the SQL-based system. I have set up the SQL system in it's own connection to the SQL server. The database is fully accessible. I have set up the ACCESS files in a separate connection to the other ACCESS server. The database is fully accessible in MSSQL2005. I can connect to both servers simultaneously and play with both databases. However, can I query from one into the other ? I have queried across databases but they have both been in the same connection. Can I query across connections without having to couple the databases into each other's connection ?
June 17, 2008 at 8:17 am
What you might want to do is set up the Access database as a linked server in SQL. In Management Studio, under Server Objects, set up a connection under Linked Servers. Then you can query the Access database from the SQL server. That should give you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2008 at 9:54 am
Excellent...Thank you !! I set up the linked server and wrote several test queries across the two DB's without affecting the other. It gets verbose but thank heaven for aliases.
June 17, 2008 at 2:34 pm
Glad I could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply