CODEWITHSUNDEEP
databasevb.netms-access
user1570048
user1570048

Reputation: 880

vb.net Comparing 2 Databases

I have Database1 and Database2, i add each of them to a datatable object. now Database1 is changed through Access, Records maybe added removed or row data might change. so the database Looks like

+----+----------+-------+-------+
| ID |   Name   | Price | Sales |
+----+----------+-------+-------+
|  1 | ProductA |   453 |    55 |
|  2 | ProductB |    43 |   156 |
|  3 | ProductC |    22 |   161 |
+----+----------+-------+-------+

so if i Delete the row with Product ID=1 i want it to be deleted in the second Database2 if i add new product with ID = 4 i want it to be Added to Database2, also if i change Price or Sales i want it to change in Database2 for that record.

now the problem is the Database records are not in the same order in both databases so looping might take alot of time..for example if ID=4000 was removed than i've to loop through the whole Database to find this out.

so are thre anyother solutions than alot of loops?

Upvotes: 0

Views: 838

Answers (2)

StingyJack
StingyJack

Reputation: 19479

If ID is the identifier for the row, then I do not think you can safely do what you are asking.

Given the example above for Table 1

+----+----------+-------+-------+
| ID |   Name   | Price | Sales |
+----+----------+-------+-------+
|  1 | ProductA |   453 |    55 |
|  2 | ProductB |    43 |   156 |
|  3 | ProductC |    22 |   161 |
+----+----------+-------+-------+

And simulating the out of order condition for Table 2

+----+----------+-------+-------+
| ID |   Name   | Price | Sales |
+----+----------+-------+-------+
|  1 | ProductB |    43 |   156 |
|  2 | ProductA |   453 |    55 |
|  3 | ProductC |    22 |   161 |
+----+----------+-------+-------+

If you delete ID 2 from table 2, you will end up deleting Product B from Table 1 (not product A). I don't think this is what you want.

Are you sure that ID is the primary key for these values?

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91376

It is possible to refer to another database in SQL run against an MS Access connection:

 SELECT t1.*, t2.* FROM Table1 t1 
 INNER JOIN [;DATABASE=Z:\Docs\Test.accdb].Table1 t2
 ON t1.ID=t2.ID

From here, it is easy enough to create queries to find missing or changed records.

Upvotes: 1

Related Questions