View Full Version : Connect to 2 databases with one query

10-09-2007, 04:55 PM
I have an Access database and an SQL database. I want to create a query that pulls from both based on a job number field. Is this possible? Thanks!

10-09-2007, 05:04 PM
Unfortunately, it is not possible with just 1 query.

You will have to open 2 different connections, 1 to Access and 1 to SQL, then run 2 different queries, then combine the information together either in an array or by relating the 2 queries together.

10-09-2007, 05:10 PM
How would you suggest relating the two?

10-09-2007, 05:24 PM
If the queries are the same or very similar, then I would use the Recordset.GetRows(), For Next Loops, and ReDim to put your arrays together.

GetRows() will push the recordset information into an array. ReDim along with FOR NEXT will allow you to add (or delete) items from your array.

Check out 4guysfromrolla and asp101 for more information on working with arrays.

Sorry I can't be more helpful, but I was just called from my desk. Once I return I will be able to help out more...

10-09-2007, 05:40 PM
I might have found a way around it so don't waste your time. If I need more help I'll post back. Thanks!