Daily updates of Access database

[karen scheu via AccessMonster.com]

I have an access database that gets its data from AS400. A separate app
extracts data from AS400 and creates an XLS file. The access database
Transfers the XLS file. This works for a rebuild of the data, but I don't
know what is the best way to do daily updates. I need access to transfer in
a daily update file and then I have to append any new rows and update any
rows that are already in the table. The unique key is on two fields, order#
and line#. I looked at the append query and find unmatched query, but the
joins only allow for one field. I need to join the table on the key which is
order+line. I'm probably missing something basic here. Any ideas would be
helpful.

Thanks,
Karen

 

[Guest]

Karen,

Have you tried creating the desired key via a passthrough query? Create
a query for each of your source tables that passes all columns of the
original table. Add a column that creates the key you wish to match on. Use
these queries in your unmatched query to find the missing rows.

I haven't tried this in Access, so there are no guarantees that it will
actually work, but it's an idea. It is something I do in Excel all the time
when trying to synchronize lists with a concatenated key like this.

HTH,
Keith