Compare Two Access Database VBA - Unique Join

Question

Need to compare two access databases. Please guide on 1. how to use VBA to connect multiple database 2. how to dynamically choose which database to connect 3. how to find if match is unique / and if not insert result into different table 4. query to match

First Database (Actually set of database files of same type - date wise)
(Called Database 1 for easy understanding):
File Names are : 
2013-03-01.mdb
2013-03-02.mdb
2013-03-03.mdb
Each file will have over a million records - Hence speedy comparison required.

Fields
ID  RowInfo     Description     Value       Region  


Database 2 :
2013-03.mdb
Fields
Table 1 :   InvoiceNumber   Value CompanyName       Date    
Table 2 :   InvoiceNumber   Region
Table 3- Match Result:InvoiceNumber Value ID RowInfo Description CompanyName Date
Table 4- Match Result:InvoiceNumber Value ID RowInfo Description CompanyName Date 

For each and every Invoice in Database2 , Look up in the database 1 (in the file specified in the Date Column). find coresponding match in Database1 based on Region and Value. In case Region not mentioned then only based on value. In case no match found , then check Date+1 file also. But must ensure unique match only. No Two InvoiceNumbers should match to the same ID (within same date) in Database1.

For each Invoice , if there is an unique match in DB1 , then insert result into Table 3 of DB2. If there is more than one match(more than one ID in DB1 for single Invoice) , then insert result into Table 4.

Answer 1

re: 1. how to use VBA to connect multiple database

In Access you can use Linked Tables (External Data > Import & Link) to manipulate tables that reside in another database file. In fact, multi-user Access databases should always use a "split" database format where the "back-end" file contains the tables and the "front-end" contains everything else (queries, forms, reports, etc.). The front-end uses Linked Tables to access the "real" tables in the back-end. (See the next point for how VBA fits into this.)

re: 2. how to dynamically choose which database to connect

The manual method involves using the "Linked Table Manager" in Access to "point" existing table links to a different file. That can also (not surprisingly) be automated using VBA if necessary, but using the Linked Table Manager is the logical place to start.

re: 3. how to find if match is unique / and if not insert result into different table

If the two "destination tables" are to have the exact same structure then you should consider maintaining just one such table with an extra column to differentiate the records it contains. In this case the extra column would be something like [SourceRowCount] where a value =1 would correspond to "Table 3" and a value >1 would correspond to "Table 4". You could still treat them as separate "Tables" in queries and reports by creating saved queries...

• query name: [Invoice_unique_match], condition: "WHERE [SourceRowCount]=1"

• query name: [Invoice_non_unique_match], condition: "WHERE [SourceRowCount]>1"

...and still have all of the records available in the same table in case there are times when you want to select (or aggregate, or report) on the records regardless of their "uniqueness" status.

Answer 2

Won't be it much more simple to add linked tables from 2013-03-02.mdb & 2013-03-03.mdb databases in 2013-03-01.mdb file and search the linked tables with a DLookup?

Answer 3

1. how to use VBA to connect multiple database

File -> Get External Data -> Link tables

1. how to dynamically choose which database to connect

It's probably best to create a query that UNIONs the tables from all the 'Database 1' databases.

1. how to find if match is unique / and if not insert result into different table

I think you said your match criteria are:

For each and every Invoice in Database2 , Look up in the database 1 (in the file specified in the Date Column). find coresponding match in Database1 based on Region and Value. In case Region not mentioned then only based on value. In case no match found , then check Date+1 file also. But must ensure unique match only. No Two InvoiceNumbers should match to the same ID (within same date) in Database1.

Suggested code:

INSERT INTO Table3
WHERE DCOUNT("[InvoiceNumber]", "[big_union_query]", _
    "( [Region] = " & [Region] & " AND [Value] = " & [Value] & " ) OR " & _
    "( [Region] IS NULL AND [Value] = " & [Value] & " )" _
    ) = 1;

INSERT INTO Table4
WHERE DCOUNT("[InvoiceNumber]", "[big_union_query]", _
    "( [Region] = " & [Region] & " AND [Value] = " & [Value] & " ) OR " & _
    "( [Region] IS NULL AND [Value] = " & [Value] & " )" _
    ) = 1;

1. query to match

There are a couple of approaches.

One is to write two INSERT queries, one to populate Table 3 and one to populate Table 4.

The other approach is to write a VBA script that does a big SELECT from the invoices in 'Database 2', iterates through them, and deals with them one by one. The "one-by-one" approach will be slow. There is lots of overhead. But it gives you that control and allows you to debug while running.