Overwriting existing record using DAO

[David Anderson]

I have designed a form to display all the fields of a record in each of two
versions of a table, where there is some difference in the values of any
field. The form will let me manually update any of the fields but in many
cases I simply want to overwrite the entire record in one table with its
matching record in the other. I am looking for advice on the slickest way to
do this using DAO. If possible, I would like to work at the record level
rather than rewriting the values of individual fields.

Do I just delete the original record in the target table and then append the
record from the source table? If so, what is the best way to temporarily
save the record to be deleted in case of problems? I want to minimise the
risk of data loss.

David

 

[Dirk Goldgar]

I have designed a form to display all the fields of a record in each of two
versions of a table, where there is some difference in the values of any
field. The form will let me manually update any of the fields but in many
cases I simply want to overwrite the entire record in one table with its
matching record in the other. I am looking for advice on the slickest way
to do this using DAO. If possible, I would like to work at the record level
rather than rewriting the values of individual fields.

Do I just delete the original record in the target table and then append
the record from the source table? If so, what is the best way to
temporarily save the record to be deleted in case of problems? I want to
minimise the risk of data loss.

It seems to me the simplest thing to do would be to execute an update query,
using a join of the tables to provide the values from the "correct" table,
with a WHERE clause that identifies the specific record to be updated.
Something like:

UPDATE
BadTable AS B INNER JOIN GoodTable AS G
ON A.ID = G.ID
SET
A.Field1 = B.Field1,
A.Field2 = B.Field2,
A.Field3 = B.Field3
WHERE A.ID = 1234

I'm guessing you would build this statement on the fly, in a string
variable, and then execute it using the CurrentDb.Execute method.

That does require you to list all the fields in the SQL statement. If you
really don't want to do that, you could use a delete query and an append
query, as you were considering, and wrap them in a transaction so that the
whole transaction can be rolled back. But be aware that, if there are
records related to the original (deleted) record, with referential integrity
enforced and cascading deletes enabled, those records would be deleted using
this method.

 

[David Anderson]

Hi Dirk,
Thanks for a very useful reply. I had totally forgotten about referential
integrity stuff, so I'll probably use your code suggestion.

David.

 

[David Anderson]

Dirk,
There appear to be some minor errors in your sample code. I'm guessing that
you didn't complete the process of altering a standard sample to use your
convention of B=Bad and G=Good. Unless I'm much mistaken, the code should
really be as follows:

UPDATE
BadTable AS B INNER JOIN GoodTable AS G
ON B.ID = G.ID
SET
B.Field1 = G.Field1,
B.Field2 = G.Field2,
B.Field3 = G.Field3
WHERE B.ID = 1234

Thanks again for your help on this.

David

 

[Dirk Goldgar]

Dirk,
There appear to be some minor errors in your sample code. I'm guessing
that you didn't complete the process of altering a standard sample to use
your convention of B=Bad and G=Good. Unless I'm much mistaken, the code
should really be as follows:

UPDATE
BadTable AS B INNER JOIN GoodTable AS G
ON B.ID = G.ID
SET
B.Field1 = G.Field1,
B.Field2 = G.Field2,
B.Field3 = G.Field3
WHERE B.ID = 1234

You're absolutely right, David. I originally wrote it up with the tables
aliased as "A" and "B", then decided that "B" and "G" (for BadTable and
GoodTable) would be more evocative. But then something came up and I
hurriedly sent off my reply without realizing I hadn't finished the
translation. I'm sorry about that.

 

[a a r o n . k e m p f]

why would you be using DAO?

DAO is obsolete, it doesn't let you connect to -ANYTHING- other than
Jet.

I mean, what a waste of time!!

 

[Tony Toews [MVP]]

why would you be using DAO?

DAO is obsolete, it doesn't let you connect to -ANYTHING- other than
Jet.

Totally wrong.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/