Connecting to a SQL Server database using ADODB

Hi guys,
here's the code example

Code:

---

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

Moved To Database Development

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:

Code:

---

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)...

Code:

---

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

" 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....

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