Connecting to a SQL Server database using ADODB

[nodeffect]

Hi guys,
here's the code example

01	Private Sub Command1_Click()
02	 
03	    Dim MyConnObj As New ADODB.Connection 'ADODB Connection Object
04	    Dim myRecSet As New ADODB.Recordset 'Recordset Object
05	    Dim sqlStr As String ' String variable to store sql command
06	     
07	    MyConnObj.Open _
08	        "Provider = sqloledb;" & _
09	        "Data Source=172.16.1.60;" & _
10	        "Initial Catalog=TESTATV;" & _
11	        "User ID=sa;" & _
12	        "Password=p@ssW0rd;"
13	     
14	     sqlStr = "select * from employee"
15	     
16	    myRecSet.Open sqlStr, MyConnObj, adOpenKeyset
17	         
18	    MsgBox "Total Number of records = " & myRecSet.RecordCount
19	     
20	    Dim i As Integer 'variable to keep count
21	    i = 1
22	     
23	    Print "#"; Tab; "ID"; Tab; "Name"; Tab; "Salary"
24	    Print ""
25	    
26	    While Not myRecSet.EOF ' Loop until endd fo file is reached
27	     
28	        Print i; Tab; myRecSet(0); Tab; myRecSet(1); Tab; myRecSet(2)
29	            '0- 1st filed, 1- 2nd Field and so on...
30	             
31	        myRecSet.MoveNext 'Moves the RecordSet pointer to the next position
32	         
33	        i = i + 1
34	    Wend
35	         
36	    MyConnObj.Close
37	     
38	End Sub

This code will only select data from a table from one database. Do you guys know what can I do if I want to select data from multiple database? Thanks

[Hack]

Moved To Database Development

[techgnome]

you could make multiple connections to the different databases and run your select.

If we're talking about SQL Server (and based on sqloledb, that would be reasonable)... and all of the databases are on the same server, you can do a Union query to pull the data, as long as all of the tables have the same structure:

Select Field1, Field2, Field3
FROM tbl1
  UNION
Select Field1, Field2, Field3
FROM secondDB.tbl1
  UNION
Select Field1, Field2, Field3
FROM thirdDB.tbl1

If they are on different servers and you have linked servers set up (so that each server can see the others)...

Select Field1, Field2, Field3
FROM tbl1
  UNION
Select Field1, Field2, Field3
FROM server2.secondDB.tbl1
  UNION
Select Field1, Field2, Field3
FROM Server2.thirdDB.tbl1
  UNION
Select Field1, Field2, Field3
FROM Server3.fourthDB.tbl1

If none of that applies, then you'll need individual connections for each one.

-tg

[nodeffect]

" myRecSet.Open sqlStr, MyConnObj, adOpenKeyset "

notice that it opens only one database.... even if I use UNION, it's still not selecting values from other databases. I'm trying to figure out how to open more than one database....

[techgnome]

Did you read what I posted.... LOOK very hard at the SQL I posted... you'll see that each part of the union is different slightly....

And it can be done in one connection... depending on how it's setup...

-tg