You are using an outdated browser. For a faster, safer browsing experience, upgrade for free today.


Compare database tables in Access

We'll show you how to compare Access database tables:

  • 1. Start the program:

  • 2. Then go to the ODBC Administrator via the BDE.

    Otherwise, it can be found "Start menu -> Settings -> Control Panel -> Administrative Tools -> ODBC data sources.

    Here on BDE interface -> Databases tab and click right mouse button on the white area:

  • 3. Add a path to .mdb file!

  • 4. Data source name can be anything, it will be referred to the alias name, through which we reach the Access table.

    Now name is for example pelda1. With select button should browse the database and then OK button is pressed always.

  • 5. You can leave BDE:

  • 6. Start the next menu item:

  • 7. Then select the source table and the target table after!

    Select the BDE SQL icon and write in alias field pelda1 in our case. This is optional of course, and if you have more database access, then you can create multiple alias too.

    Underneath, on text field write the SQL text. The following SQL lists all data fields in table named "cikktorzs" and sorted by "product number". This sort is very important, because the comparison must be set the key fields! The key field means what we provide in orderliness.

  • 8. If the target (destination) table is selected, this screen view:

  • 9. Go to the next tab at the bottom there!

    Here we can see the field names, which should be matched with each other. If you do not see the fields properly, press the following icon:

  • 10. You need to specify the key fields, above and below the left list box select "product number" field, and the bottom "add key fields" icon we will pressed:

  • 11. Now comes the comparison field, in this case the "Quantity" field is chosen.

    Select this and add it "add fields Compare" named button.

  • 12. After that have to push the "comparison" button (above)

  • 13. Then move to the next tab where you can see the result.

    If the source table the item missing the color is green. If the target table the item missing the color is yellow. And in this case the 'quantity' different, the color is red. All fields bring up the bottom. The "s_" signal means the source table fields, the "d_" sign means the target table fields, the "db_compstate" field indicates the status (missing or not equal).
    This table can be exported, push over the table with right mouse button:

  • 14. Those projects that do not have to "play" it all again, you can save anywhere, the next time you need to be re-compare:

    Good luck!