Update Table

[Brian Branco]

I need a program that will take records from an input
file and add them to a table if they don't already
exist. If they exist, update just the description.

Example:

INPUT_TABLE
CODE DESCRIPTION

PERMANENT_TABLE
CODE DESCRIPTION

I need it to skip through the INPUT_TABLE and find the
record in the PERMANENT_TABLE. If it does not find it,
add the CODE and DESCRIPTION to the table, otherwise just
update the DESCRIPTION.

I am new to Access, but not new to programming. It's a
simple program in Visual Foxpro, but I am struggling with
it in ACCESS.

Thanks

 

[John Nurick]

Hi Brian,

In Access the usual way is with two queries. First, an update query that
joins the two tables on the key field and updates records that exist in
both tables. This is from my test database; the tables are T97A
(permanent) and T97B (import), the key field is ID, and the field being
updated is City:

UPDATE T97A INNER JOIN T97B
ON T97A.ID = T97B.ID
SET T97A.City = T97B.City
;

Second, an append query that joins the two tables and appends records
from the import table that don't have counterparts in the permanent
table:

INSERT INTO T97A
SELECT T97B.*
FROM T97A RIGHT JOIN T97B
ON T97A.ID = T97B.ID
WHERE (T97A.ID Is Null)
;

If you create and save the queries, all your code has to do is run them,
e.g.

Dim dbD As DAO.Database
Set dbD = CurrentDB()

dbD.Execute "MyQuery", dbFailOnError
dbD.Execute "MySecondQuery", dbFailOnError

Set dbD = Nothing