Update matching fields in two tables

[Rick]

Hi,
How would I update data in one table with data from
another table, for only those fields that exist in both
tables?(matching the records by using a primary key field
of course). I am familiar with update queries but have
only seen where it is possible to hard code the actual
field names in the query. Since the fields will be
changing in one of the tables, I can't do this (i.e. if I
set the update query to update the phone number field and
the next time I run the query the one table no longer has
a phone number field, I run into problems). I need it to
match the tables on a primary key (that IS always there),
and update any fields that are found in both tables.
Thanks for any help,
-Rick

 

[Michel Walsh]

Hi,


Generates the string, the text of the SQL code, at runtime, and execute
it.

CurrentDb.Execute strSQL, dbFailOnError


Hoping it may help,
Vanderghast, Access MVP

 

[Rick]

Thanks, but I am not sure what to do with this line of
code. Could you point me in the right direction? Thanks
for your help,
-Rick

 

[Michel Walsh]

Hi,


The dbFailOnError will just warn your VBA code that there was some
error.

In general, DAO, can allow you to update a table if the statement is
valid (no misspelled field name, syntax right) and will also update the
records that would not violate data relation integrity or other rules, but
would not update those records that, updated, would violate those rules.

As example, if you have an autonumber as a primary key, with values from
1 to 6, and you update:

UPDATE myTable SET myPk=myPk + 1

you would probably end with «1, 2, 3, 4, 5, 7 » since, as example for
the first record, 1+1 would have make a 2 for the pk, but there was
already a 2, so the update didn't make it for the first record because doing
so, it would have violated the no duplicated value rule, etc, up to the last
record, which, in that case, accepted the update from a 6 to a 7.

So, have the syntax right, else nothing would be updated.
Then, be aware that only SOME RECORDS (not some rows) can be updated. A
VBA trappable error will occur, if you use the flag dbFailOnError, if such
thing occur.


If you do not want the second behavior, include the statement inside a
transaction, and through error trapping, roll it back, if appropriate,
rather than committing it, a little bit like the dialog you are prompted
with, when you run a query from the designer, and that some error occur (xxx
records have not "this" because of "that", do you want to ... )


Hoping it may help,
Vanderghast, Access MVP