none
MS AC 2010: How data is stored on the disk/exported to text file. RRS feed

  • Question

  • Hello all,

    I'm trying to compare 2 accdb files to see if they have duplicate data:

    • File A has 4 tables: 2015-01, 2015-02, 2015-03, and 2015-04 (please ignore (un-/de,)normalized format...client just uses AC to send us data)
    • File B has 3 tables: 2015-02, 2015-03, and 2015-04
    • I exported the 3 matching tables from both DBs to individual text files
    • Using old MS-DOS COMP command, Feb and March files match exactly
    • April files sizes match exactly (COMP wouldn't work otherwise) and the data matches up until line 101,909 (out of 118,363)

    Can 2 tables contain the exact same data, display it in the exact same way when opening the table in Datasheet View (I have not verified all records are in the same order in both tables), but export to text files in different order?

    Is my only option to export data via a sorted query to compare the data in the 2 tables?

    Thanks for any help anyone can provide,

    CTB

    Thursday, May 26, 2016 5:48 PM

Answers

  • Hi CTBarbarin,

    According to your description, if you have two Access desktop databases, or maybe two versions of the same database, and you want to compare them to find design changes to queries, forms, or reports. Or maybe you want to find the differences in Visual Basic for Applications (VBA) code modules. If you have Microsoft Office Professional Plus 2013 or 2016, you can use Microsoft Database Compare to run a report on the differences it finds.

    For more information, click here to refer about Basic tasks in Database Compare

    If you want to review records from one table only if there are corresponding records in another table that contain one or more fields with matching data.

    Create a query that joins fields from each table where those fields contain corresponding information, either by using an existing relationship or by using a join that you create for the purpose of the query. This method provides optimum performance (the speed with which the query returns results), but you cannot join fields that have dissimilar data types.

    Create a query that compares fields by using one field as a criterion for the other. Using a field as a criterion for another field is generally slower than using joins, because joins eliminate rows from a query's results before the underlying tables are read, whereas criteria are applied to a query's results after the underlying tables are read. However, you can use a field as a field criterion to compare fields that have dissimilar data types, which you cannot do by using joins.

    For more information, click here to refer about Compare two tables and find only matching data and here to refer about Compare two tables and find records without matches

    Friday, May 27, 2016 5:38 AM

All replies

  • Sorry for posting in Access for Developers.  This isn't really a development question.  It doesn't appear that MSDN forums have all of the more specific sub-forums for Access/each product like they used to.
    Thursday, May 26, 2016 5:55 PM
  • Hi. Are you comparing only certain fields or all the fields? You can create a query to compare the tables and display the mismatches. Exporting to text files may not be necessary. Just a thought...
    Thursday, May 26, 2016 6:30 PM
  • Hi. Are you comparing only certain fields or all the fields? You can create a query to compare the tables and display the mismatches. Exporting to text files may not be necessary. Just a thought...

    Thank you for the feedback the DB guy.

    I am comparing whole tables to each other...all records and all columns/fields.

    Thanks again.

    Thursday, May 26, 2016 6:51 PM
  • How many are field are we talking about? If you have both tables to compare within the same database container, you should be able to create a query linking (joining) all the fields to each other and then use an OUTER JOIN to see the mismatches. Just my 2 cents...
    Thursday, May 26, 2016 7:13 PM
  • Hi CTBarbarin,

    According to your description, if you have two Access desktop databases, or maybe two versions of the same database, and you want to compare them to find design changes to queries, forms, or reports. Or maybe you want to find the differences in Visual Basic for Applications (VBA) code modules. If you have Microsoft Office Professional Plus 2013 or 2016, you can use Microsoft Database Compare to run a report on the differences it finds.

    For more information, click here to refer about Basic tasks in Database Compare

    If you want to review records from one table only if there are corresponding records in another table that contain one or more fields with matching data.

    Create a query that joins fields from each table where those fields contain corresponding information, either by using an existing relationship or by using a join that you create for the purpose of the query. This method provides optimum performance (the speed with which the query returns results), but you cannot join fields that have dissimilar data types.

    Create a query that compares fields by using one field as a criterion for the other. Using a field as a criterion for another field is generally slower than using joins, because joins eliminate rows from a query's results before the underlying tables are read, whereas criteria are applied to a query's results after the underlying tables are read. However, you can use a field as a field criterion to compare fields that have dissimilar data types, which you cannot do by using joins.

    For more information, click here to refer about Compare two tables and find only matching data and here to refer about Compare two tables and find records without matches

    Friday, May 27, 2016 5:38 AM
  • CTBarbarin –

    Why not use the Query Wizard to compare table A with table B? You can look for duplicates or unmatched records. If you choose the latter and the result is an empty dataset, the tables match.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Saturday, May 28, 2016 8:12 PM