Import and update table

[serverguy78]

I have a macro that calls import queries to import a table from an offline computer to network table database. I would like the macro to update the data in the network table database. It is overwriting the data in the network table database. How do I fix this?

 

[dhookom]

I always import into an temporary table and then run update and/or append queries to the production tables.

Duane
Hook'D on Access
MS Access MVP

 

[serverguy78]

I tried an append query. It creates duplicates. How do perform an update query?

 

[lameid]

If it is cumulative and immutable data, you could append the values that are not there Not IN(<list/sub query>).

You could also delete the values and then append.

You can also join the destination table to the temp table and update all values including the join columns to the import table. Be sure to use an outer join from the import table to ensure it will "update" the missing values. In this scenario I try to use criteria to eliminate things that don't need updated. This may be the slowest performance wise.

 

[dhookom]

You only append records from your import that aren't in your production table. You only update records that require updating. This can sometimes be performed with a single query but might be easier with two queries.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

A generic way:
UPDATE yourTable AS O RIGHT JOIN yourNewTable AS N ON O.PrimaryKey = N.PrimaryKey
SET O.PrimaryKey = N.PrimaryKey, O.SomeField = N.SomeField, O.AnotherField = N.AnotherField

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Our replies rely on appropriate primary key fields which we know nothing about in your system.

Duane
Hook'D on Access
MS Access MVP

 

[serverguy78]

How do I get it not to overwrite the data that's already there?

 

[lameid]

A specific example of how to do it reuires knowing the columns/fields of the data and some knowledge about what identifies a record as unique all of which you would need to provide.

But if you had

People
ID Name

Your query might look like...

Insert Into People (ID, Name)
Select ID, Name
From Import
Where Import.ID Not IN(Select ID From People)