ADODB.recordset with 2 databases

Hi,

We have been running a UNION queriy on our single db using an ADODB.recordset like this...

OpenDataConnection()
set rsID = CreateObject("ADODB.recordset")
searchphrase = " SELECT t.*
" FROM table1 t "
" UNION "
" SELECT t.*
" FROM table2 t "
" ORDER BY Name "
rsID.open searchphrase, oConn
CloseDataConnection()

However table1 and table2 have grown so large they need to be placed in their own dbs, so now we will have 2 dbs but still want to run this query.

So presumambly i now open 2 connections
Also, i will need to qualify the db name in front of the table in the searchPhrase
BUT, how can a refer to the 2 open connection when i open the recordset ie. here

rsID.open searchphrase, oConn

as it only takes a single parameter.

Any help would be great, thanks in advance.

re: ADODB.recordset with 2 databases

Hi TimSki,

Assuming that you have two databases called Database1 and Database2 on the same server then you only need one connection and can use the following SQL query:

Let me know if this helps,

Dr B

re: ADODB.recordset with 2 databases

thanks very much for the prompt reply. perhaps a stupid question but presumably i still need to open and close both database connections ?

re: ADODB.recordset with 2 databases

No problem.

The crucial question is whether both databases are on the same SQL Server. If so then you only need to open and close one connection. Think of the connection as being to the SQL server with a default database specified in the connection string rather than being a connection to the database itself.

Give it a try and let me know how it goes.

Dr B

re: ADODB.recordset with 2 databases

i think this is where i'm getting confused. I thought a connection string was unique to a db ie.

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

if i want to connect to a second db on the same sql server i would also have to do.

Data Source=myServerAddress;Initial Catalog=myDataBase2;User Id=myUsername2;Password=myPassword2;

??

re: ADODB.recordset with 2 databases

TimSki
Very late reply here but I came here from another thread and saw the ?? at the end of you post and thought I would try and answer it.

Yes, indeed you could create a second connection as you have detailed.

But, if both those databases are on the same server then you don't have to.

Indeed, why add the extra overhead on system resources by creating a second connection when you don't need to.

When you create a connection and specify the "Initial Catalog", all that really means is that you can execute queries on that database without having to specify it in your query. The database is implied by the connection.

However, you can still query other databases through that connection by specifying it in the query as DrBunchman did.

In fact, you can actually create a connection without the "Initial Catalog" specified at all. In that case you MUST always specify which database in your query.

NOTE
The above only apply if the databases exist on the same server