Help - How to join two datasets

[Guest]

Hi all,
My task is to identify duplicate records in 2 tables that each located in
different Access database. These tables have exactly the same structure. My
immediate solution is to create two datasets from each table and loop
through one and compare it with another one, but I'm sure there are better
way to this. Is it possible to join these datasets or could dataview be
used? I have asked this question before, but I have not found my way to
resolve this.
Any suggestions would be appreciated.
Roy

 

[Cor Ligthert]

Roy,

The first question has to be, why do you want this? Because when one has by
instance to be equal to the other one it is easier just to update the one
that has to change with that which have the values as it has to be.

Cor

 

[Guest]

Cor,
Would you further explaain your suggestion by using a sample.
Thanks,
Roy

 

[Cor Ligthert]

Roy,

Table one is
1,4,5

Table two is
1,3,6

The result has to be
The 1,3,6 from table two and 4 and 5 from table one.

Than you can with the datareader and the insert do a lot, however it is
easier to tell when you do it like this.

\\\just paste a datagrid in a form and past in this, the most is just
creating the tables.
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Dim ds1 As New DataSet
Dim ds2 As New DataSet
Dim dt1 As New DataTable("Roy")
Dim dt2 As New DataTable("Roy")
dt1.Columns.Add("index")
dt1.Columns.Add("table")
Dim keys1(0) As DataColumn
keys1(0) = dt1.Columns("index")
dt1.LoadDataRow(New Object() {1, "one"}, True)
dt1.LoadDataRow(New Object() {4, "one"}, True)
dt1.LoadDataRow(New Object() {5, "one"}, True)
dt2.Columns.Add("index")
dt2.Columns.Add("table")
Dim keys2(0) As DataColumn
dt1.PrimaryKey = keys1
keys2(0) = dt1.Columns("index")
dt2.LoadDataRow(New Object() {1, "two"}, True)
dt2.LoadDataRow(New Object() {3, "two"}, True)
dt2.LoadDataRow(New Object() {6, "two"}, True)
ds1.Tables.Add(dt1)
ds2.Tables.Add(dt2)
ds1.Merge(ds2)
ds1.Tables("Roy").DefaultView.Sort = "index"
DataGrid1.DataSource = ds1.Tables("Roy").DefaultView
End Sub
///



I hope this helps,

Cor

 

[Guest]

Thanks again Cor,
I guess my problem is that I have db1.table1 with 10,000 records and then I
have db2.table2 with thousands of records. Table1 and table2 have exact
structure, I need to identify records in table2 that exist in table1. If it
was SQL Server I could do a remote join query to get these records, but since
I'm working with Access I don't how to approach this.

 

[Cor Ligthert]

Roy,

If you do a Join you are reading your records as well in memory and are in
fact creating one table with a long record which you cannot update.

Therefore trying it with OleDB (only because of the memorylimit, I dont know
how large the fields are in those datarows).
Create two dataadapters using the OleDBdataadapter in the designer, which is
a wizard in that you have to choose a connection that is automaticly build.
Use for each wizard a seperate dataset
Fill both datasets (keep the names equal of the tables) using the
OleDbDataadapters
Add the primary keys as in my sample
Do the merge as in the sample
Update the dataset using the OleDpDataAdapter.

I hope this helps,

Cor

 

[Andrew D. Newbould]

Thanks again Cor,
I guess my problem is that I have db1.table1 with 10,000 records and then I
have db2.table2 with thousands of records. Table1 and table2 have exact
structure, I need to identify records in table2 that exist in table1. If it
was SQL Server I could do a remote join query to get these records, but since
I'm working with Access I don't how to approach this.

You can simulate an SQL Server remote join by modifying one on the
Access databases to have a "Linked Table" from the second Access
database. This way the queries are done in the one database similar to
SQL Server.