ADODB.recordset with 2 databases

[TimSki]

Hi,

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

OpenDataConnect ion()
set rsID = CreateObject("A DODB.recordset" )
searchphrase = " SELECT t.*
" FROM table1 t "
" UNION "
" SELECT t.*
" FROM table2 t "
" ORDER BY Name "
rsID.open searchphrase, oConn
CloseDataConnec tion()

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.

[DrBunchman]

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:

SELECT * FROM Database1.dbo.Table1 UNION SELECT * FROM Database2.dbo.Table2

Let me know if this helps,

Dr B

[TimSki]

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

[DrBunchman]

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

[TimSki]

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

Data Source=myServer Address;Initial Catalog=myDataB ase;User Id=myUsername;P assword=myPassw ord;

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

Data Source=myServer Address;Initial Catalog=myDataB ase2;User Id=myUsername2; Password=myPass word2;

??

[Delerna]

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

[dranium]

This is probably an old post but what if my database is on two different servers? How would I open the connection? Would it be something like rsID.open searchphrase, oConn, oConn2?

[Delerna]

This is probably an old post but what if my database is on two different servers? How would I open the connection? Would it be something like rsID.open searchphrase, oConn, oConn2?

No, as far as I know you cannot create a single connection to multiple servers.
At least, not in the way you are thinking.

What is your database application, SQL Server, Access, Oracle,other ?
What exactly do you mean by "My database is on two differen servers?
What are you trying to do, specifically?

The answers to those will determine how you would do it.
For example
with MS SQL Server
It is possible to link databases from different servers together and then write a "view" or "stored procedure" on one of them that pulls data from all of them.
Your asp code then simply connects to the one with the "view" or "stored procedure" and executes SQL on it for the data needed.


PS
This really should have gone into a new thread.
You could have put a link to this thread in your own thread as background info for your question.

[jypw]

Access

Hi!

That is interesting. And what if I have two accdb(access database)?

I have 2 accdb files. I need to create a recordset that will include a link to 2 tables that are in 2 different accdb file.

Is that possible?

Thanks

[Delerna]

In the tables tables panel
1) right click and select the Link option of the context menu

2) Navigate to the database that contains the tables you want and double click it.

3) select the tables you want to link into this database.

Now you have a table in this database that acually exists in another database and can be accessed from both databases.

This is a good technique for giving multiple users access to the same database at the same time.

Put all your tables in one database (the backend).
The "Back End" database goes onto a file share server

Link to those tables from another database and put all your queries forms reports etc into it (the front end)
A separate copy of the "Front end" database goes to as many users as you need to give access to the same data.

Obviously MS SQL server is the better option for this sort of thing but if you have a relatively small user base for the database then this can and does work quite well.


Also noteworthy is that the backend database does not have to be a single database.
The tables can be linked into your front end from multiple backend databases.



Is this what you mean?

[Pritesh19]

Hi, I got stuck in a similar problem. I have 2 databases(one SQL and other Oracle) in two different server. I want to access them simultaneously , using VBS. To be precise , i need to export from SQL to Oracle db , on some condition related to a table in Oracle db. Any help will be really helpful.