update multiple tables from .csv or .xls file

[John B. Smotherman]

I have an inventory database application now installed simultaneously on two
networks. (Yeah, I know, but it wasn't my call). There exists a permanent
"air-gap" between the two networks, so the only way I have to synchronize the
back-end tables is to take the audit table (thanks Allen Browne) created on
one network, fold it into the back-end on the other network, then
redistribute the updated (synchronized) back-end to the first network.

I've done monthly archives of the audit table for some time now, exporting
the table to an excel file, then emptying the table. My plan is to use this
..xls file (or convert to .csv if necessary) to update the tables. I should
mention two things: first, the audit table is based on a query that displays
data from five linked tables so the audit table has all the fields from all
five and I need to ensure all five tables are synchronized; second, I've
modified Allen's original audit code slightly such that only fields with a
change are recorded for the "EditFrom" and "EditTo" entries in the table -
all others are null - and there are no "Insert" records in the audit table.

Finally, my questions:
1. Would it be best to update these tables from an excel file or should I
import the file into a single table first?
2. Would VBA be the best route to handle the updates, or should I be looking
at an update query? (I've not yet written an update query, but I'm willing to
learn).
2a. If VBA, does anyone have a code snippet they'd be willing to post for
this?
2b. If a query, can a single update query update multiple tables, or do I
need to create separate queries for each table?

Thanks for wading through my long-winded explanation, and thanks in advance
for any assistance.

 

[John B. Smotherman]

Well, given those two options, I'd say a link to the Excel file would be
best, so you don't bloat your database with imported data. But why not just
link to the table on the other server, instead of exporting to an Excel file
and linking? Or, if an export is necessary, why not just export the table

As I wrote in my original post, there's an "air-gap" between the two
servers, meaning no point of connectivity.

An update query would be best, as it would be much faster. If your updates
are too complex/conditional, you'll need to use VBA. Shouldn't be a big deal

OK, I've played around a bit with an update query, and can't seem to make it
update more than a single record in the table. As a start, I'm just trying to
update the fields HomeLocation and CurrentLocation. I have 248 records in the
audit table - 1 that has a change to HomeLocation and 35 that have changes to
CurrentLocation (coincidentally, one of these records is the one with the
change to HomeLocation). Since only one record passes both criteria
(HomeLocation in the audit table is not null and CurrentLocation in the audit
table is not null), only one record gets updated. How can I change the query
criteria such that they are not additive?

Thanks again.

 

[John W. Vinson]

OK, I've played around a bit with an update query, and can't seem to make it
update more than a single record in the table. As a start, I'm just trying to
update the fields HomeLocation and CurrentLocation. I have 248 records in the
audit table - 1 that has a change to HomeLocation and 35 that have changes to
CurrentLocation (coincidentally, one of these records is the one with the
change to HomeLocation). Since only one record passes both criteria
(HomeLocation in the audit table is not null and CurrentLocation in the audit
table is not null), only one record gets updated. How can I change the query
criteria such that they are not additive?

You wouldn't - typically anyway - need any criteria on the field being
updated, if you're updating the records in TableA to the values in TableB.
Instead you'ld just have a query joining the two tables on the Primary Key
(the matching identifier in the two tables), and update TableA.HomeLocation to
[TableB].[HomeLocation], or whatever the appropriate field would be.

Perhaps you could post the table description and the SQL of the query you're
trying to use.

 

[Neil]

As I wrote in my original post, there's an "air-gap" between the two
servers, meaning no point of connectivity.

Again, why not export to an MDB file? But, whatever.

OK, I've played around a bit with an update query, and can't seem to make
it
update more than a single record in the table. As a start, I'm just trying
to
update the fields HomeLocation and CurrentLocation. I have 248 records in
the
audit table - 1 that has a change to HomeLocation and 35 that have changes
to
CurrentLocation (coincidentally, one of these records is the one with the
change to HomeLocation). Since only one record passes both criteria
(HomeLocation in the audit table is not null and CurrentLocation in the
audit
table is not null), only one record gets updated. How can I change the
query
criteria such that they are not additive?

You would put the criteria on different rows of your query's criteria area.
If the criteria are on the same row, it means AND; if they're on different
rows, it means OR.

Neil

 

[John B. Smotherman]

You wouldn't - typically anyway - need any criteria on the field being
updated, if you're updating the records in TableA to the values in TableB.
Instead you'ld just have a query joining the two tables on the Primary Key
(the matching identifier in the two tables), and update TableA.HomeLocation to
[TableB].[HomeLocation], or whatever the appropriate field would be.

Back in the days when I only had to maintain the db on a single network, I
modified the audit code I got from Allen Browne's page. Instead of writing
the complete "EditFrom" and "EditTo" records, with all the fields filled in,
it empties the fields with no change and only writes the before and after for
fields that were changed. This made those fields really stand out when I
reviewed the audit logs. I'm beginning to think I need to change it back so
it writes all the fields...