Does anybody know if it is possible to create a recordset based on a query across tables in different databases? I have one database with a table of customer details, one database with a table of customer visits and I want to get both customer details and visit details in a single query. Any ideas?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Query 2 Databases
- Thread starter aesseal
- Start date
What type of databases are they? If they are SQL Server databases on the same SQL Server then you can write a query statement to join the data in a single query. For example, if you have a Database1 with Table1 and a Database2 with Table2 that both have a common field "Foo" you can write the following statement to join them:
select a.*, b.*
from Database1..Table1 a, Database2..Table2 b
where b.Foo = a.Foo
This presumes that the login with which you connect to the SQL Server has access to both databases.
select a.*, b.*
from Database1..Table1 a, Database2..Table2 b
where b.Foo = a.Foo
This presumes that the login with which you connect to the SQL Server has access to both databases.
- Thread starter
- #3
Greetings!
I've done something similar, but not exactly the same. I have data that crosses multiple platforms (Oracle, PervasiveSQL, Access). In one case, using a connection object to an access database (so I could insert the data into a static table), I used:
dim strdbl as string
dim strSQL as string
dim cnAccess as new ADODB.connection
string db = chr(34) 'set to double quotes
strSQL = "INSERT INTO tActiveJobs ( Job_Number, Machine, etc) FROM CIM_FOLDER11 INNER JOIN CIM_FOLDER10 ON CIM_FOLDER11.ID=CIM_FOLDER10.ID IN " & strDBL & strDBL & "ODBC;Provider=MSDASQL.1;PWD=cim;UID=moi;DSN=cmlcl;];"
So, the recordset accessed data in a different location and platform (CIM is an Oracle database on a different server), using only one connection object.
One other alterantive is to lin the tables into an access database and run you query there...
I hope this helps!
(By the way, if anyone has a better idea, I could really use it!)
I've done something similar, but not exactly the same. I have data that crosses multiple platforms (Oracle, PervasiveSQL, Access). In one case, using a connection object to an access database (so I could insert the data into a static table), I used:
dim strdbl as string
dim strSQL as string
dim cnAccess as new ADODB.connection
string db = chr(34) 'set to double quotes
strSQL = "INSERT INTO tActiveJobs ( Job_Number, Machine, etc) FROM CIM_FOLDER11 INNER JOIN CIM_FOLDER10 ON CIM_FOLDER11.ID=CIM_FOLDER10.ID IN " & strDBL & strDBL & "ODBC;Provider=MSDASQL.1;PWD=cim;UID=moi;DSN=cmlcl;];"
So, the recordset accessed data in a different location and platform (CIM is an Oracle database on a different server), using only one connection object.
One other alterantive is to lin the tables into an access database and run you query there...
I hope this helps!
(By the way, if anyone has a better idea, I could really use it!)
- Status
Similar threads
- Locked
- Question
