Merging Data from TWO databases into One recordset??? 
Author Message
 Merging Data from TWO databases into One recordset???

Hello:

I need some assistance on how I can create a single recordset from TWO
seperate Databases. The field names and properties are indenticle.
One database is for current and live data. One is for archival data.

I hope someone out there can help!

Thanks in advance .

Joe



Sat, 17 Jan 2004 01:39:30 GMT  
 Merging Data from TWO databases into One recordset???
Where are the databases? Jet? SQL Server? Oracle? Both Jet and SS can join
data from external data sources.

--
William (Bill) Vaughn
President Beta V Corporation
www.betav.com


Quote:
> Hello:

> I need some assistance on how I can create a single recordset from TWO
> seperate Databases. The field names and properties are indenticle.
> One database is for current and live data. One is for archival data.

> I hope someone out there can help!

> Thanks in advance .

> Joe



Sat, 17 Jan 2004 08:39:50 GMT  
 Merging Data from TWO databases into One recordset???
I agree, it depends on which kind of database you are using.

For Access database, we can add link table in one database. Then we can use
one connection to query both tables at the same time and union the two
recordsets into one recordset.

For SQL Server database, it's much easier. We only need to use fully
qualified name to reference the other table in another database. Then we
can use union keywords to combine the two recordsets.

If you do not want to touch the database and want to implement this in VB
code solely, we can only do it manually this way:

Note: Pay attention this method is some complex and requre two connections
and therefore will be some slow if your database is large.

1. Create two recordsets each corresponding to one of the databases.
2. Setup another recordset with the same field structure as the above.
2.Manually merge the records in the two recordsets into the new one.

        In this example, I combine two tables (both named "table1") with the same
field structure from two different databases (db1.mdb and db2.mdb). Refer
to the sample code below:

        ******Sample code begins here************

        Dim rs As ADODB.Recordset 'Recordet to store the records from two
databases.

        Private Sub Command1_Click()

        Dim cn1 As ADODB.Connection
        Dim cn2 As ADODB.Connection
        Dim rs1 As ADODB.Recordset 'For the first database
        Dim rs2 As ADODB.Recordset 'For the second database

        Dim fdCount As Integer 'Used to store the number of fields
        Dim fdIndex As Integer 'Used to access each field

        'Establish connection and populate rs1 and rs2
        Set cn1 = New ADODB.Connection
        Set cn2 = New ADODB.Connection
        cn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source=F:\My Documents\TEMP\db1.mdb;" & _
                           "User Id=admin;" & _
                           "Password=;"
        cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source=F:\My Documents\TEMP\db2.mdb;" & _
                           "User Id=admin;" & _
                           "Password=;"

        Set rs1 = New ADODB.Recordset
        Set rs2 = New ADODB.Recordset
        rs1.Open "select * from table1", cn1, adOpenStatic, adLockOptimistic
        rs2.Open "select * from table1", cn2, adOpenStatic, adLockOptimistic

        'Set up rs to the same field structure as rs1
        Set rs = New ADODB.Recordset
        fdCount = rs1.Fields.Count

        For fdIndex = 0 To fdCount - 1
            rs.Fields.Append rs1.Fields(fdIndex).Name, rs1.Fields(fdIndex).Type, _
                             rs1.Fields(fdIndex).DefinedSize,
rs1.Fields(fdIndex).Attributes
        Next fdIndex

        'Combine records in rs1 and rs2 into rs
        rs.Open

        rs1.MoveFirst
        While Not rs1.EOF
            rs.AddNew
            For fdIndex = 0 To fdCount - 1
                rs.Fields(fdIndex) = rs1.Fields(fdIndex)
            Next fdIndex
            rs.Update
            rs1.MoveNext
        Wend

        rs2.MoveFirst
        While Not rs2.EOF
            rs.AddNew
            For fdIndex = 0 To fdCount - 1
                rs.Fields(fdIndex) = rs2.Fields(fdIndex)
            Next fdIndex
            rs.Update
            rs2.MoveNext
        Wend

        End Sub

        ******Sample code ends here************

        Now recordset rs contains all the records from rs1 (from db1.mdb) and rs2
(from db2.mdb).



Sat, 17 Jan 2004 16:51:37 GMT  
 Merging Data from TWO databases into One recordset???
Thanks to all.

Should have been more specific. But the are Both MDB's

I kind of figure that this was the way to do it.
Kind of like setting up a dummy table and adding in both recordset from the
tables. I just thought that a more elegant and simple approach was out
there!

Again thanks

Joe


Quote:
> I agree, it depends on which kind of database you are using.

> For Access database, we can add link table in one database. Then we can
use
> one connection to query both tables at the same time and union the two
> recordsets into one recordset.

> For SQL Server database, it's much easier. We only need to use fully
> qualified name to reference the other table in another database. Then we
> can use union keywords to combine the two recordsets.

> If you do not want to touch the database and want to implement this in VB
> code solely, we can only do it manually this way:

> Note: Pay attention this method is some complex and requre two connections
> and therefore will be some slow if your database is large.

> 1. Create two recordsets each corresponding to one of the databases.
> 2. Setup another recordset with the same field structure as the above.
> 2.Manually merge the records in the two recordsets into the new one.

> In this example, I combine two tables (both named "table1") with the same
> field structure from two different databases (db1.mdb and db2.mdb). Refer
> to the sample code below:

> ******Sample code begins here************

> Dim rs As ADODB.Recordset 'Recordet to store the records from two
> databases.

> Private Sub Command1_Click()

> Dim cn1 As ADODB.Connection
> Dim cn2 As ADODB.Connection
> Dim rs1 As ADODB.Recordset 'For the first database
> Dim rs2 As ADODB.Recordset 'For the second database

> Dim fdCount As Integer 'Used to store the number of fields
> Dim fdIndex As Integer 'Used to access each field

> 'Establish connection and populate rs1 and rs2
> Set cn1 = New ADODB.Connection
> Set cn2 = New ADODB.Connection
> cn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>                    "Data Source=F:\My Documents\TEMP\db1.mdb;" & _
>                    "User Id=admin;" & _
>                    "Password=;"
> cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>                    "Data Source=F:\My Documents\TEMP\db2.mdb;" & _
>                    "User Id=admin;" & _
>                    "Password=;"

> Set rs1 = New ADODB.Recordset
> Set rs2 = New ADODB.Recordset
> rs1.Open "select * from table1", cn1, adOpenStatic, adLockOptimistic
> rs2.Open "select * from table1", cn2, adOpenStatic, adLockOptimistic

> 'Set up rs to the same field structure as rs1
> Set rs = New ADODB.Recordset
> fdCount = rs1.Fields.Count

> For fdIndex = 0 To fdCount - 1
>     rs.Fields.Append rs1.Fields(fdIndex).Name, rs1.Fields(fdIndex).Type, _
>                      rs1.Fields(fdIndex).DefinedSize,
> rs1.Fields(fdIndex).Attributes
> Next fdIndex

> 'Combine records in rs1 and rs2 into rs
> rs.Open

> rs1.MoveFirst
> While Not rs1.EOF
>     rs.AddNew
>     For fdIndex = 0 To fdCount - 1
>         rs.Fields(fdIndex) = rs1.Fields(fdIndex)
>     Next fdIndex
>     rs.Update
>     rs1.MoveNext
> Wend

> rs2.MoveFirst
> While Not rs2.EOF
>     rs.AddNew
>     For fdIndex = 0 To fdCount - 1
>         rs.Fields(fdIndex) = rs2.Fields(fdIndex)
>     Next fdIndex
>     rs.Update
>     rs2.MoveNext
> Wend

> End Sub

> ******Sample code ends here************

> Now recordset rs contains all the records from rs1 (from db1.mdb) and rs2
> (from db2.mdb).



Sun, 18 Jan 2004 01:12:07 GMT  
 Merging Data from TWO databases into One recordset???
My pleasure.

Since they are both mdb's, as i mentinoed in my first post, we can use link
table, which is more convenient:

1. In one of the mdb database, insert a link table (by clicking menu
"Insert" | "table" and select "Link Table").
2. In VB code, use the following code instead:

Dim rs As ADODB.Recordset
*****sample code starts here*************
Private Sub Command1_Click()

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source=pathname\filename.mdb;" & _
                           "User Id=admin;" & _
                           "Password=;"
Set rs = New ADODB.Recordset
rs1.Open "select * from table1 union all select * from table11", cn,
adOpenStatic, adLockOptimistic
*********sample code ends here*************
where "table11" is the link table coming from the other database.

Best,

Chris



Sun, 18 Jan 2004 13:48:50 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. How to merge two ADO RecordSets into one

2. How to combine the data from two .dbf database file to one new .dbf database fil

3. Merge two wave file one after one

4. Merging data from two databases

5. Multiple Data Controls, One Database, Two Forms...

6. merge two queries in one form?

7. Merging Two Word97 docs into one

8. Merge two wave file in one single wave file

9. Possible to merge two XML recordsets?

10. Merging two access databases/tables

11. Merging Two MDB Databases Through VB !

12. Merging of two access databases/tables

 

 
Powered by phpBB® Forum Software