Append Query or update query?

[Lasse T]

Hello !!

Access 2000.

Is it possible to force an append query to overwrite existing records in the
target database?

I want to make it simple for the users to import new and changed (price)
items into their databases, so I thought I could create a small Access
application with a table that holds the new items and prices and an append
query to send the records to their existing database, but I can not get the
query to update existing records. A delete query will not work sins there
are relationships between tables.

Another approach could be an update query but how can i use it between
different databases?

I use Excel spreadsheets today and all the users needs to do is download the
spreadsheet, rename it and place it in C:\ then push a button in the
application. Unfortunately many of them finds that too difficult.

I hope anyone can help me.

Lasse T
-------------

 

[gllincoln]

Hi Lasse,

An append query isn't going to overwrite - that takes an update query.

Ideally, you would have a backend database where the main table (I call
tblTarget below) resides. The user's databases would be linked to that table
and they wouldn't actually have their own copy of that data. This way you
could run a fairly simple macro process to update the table per below and
the users would all be taken care of. This could eventually become an
automated process that ran in the evening and didn't require any
intervention.

Assuming you have something like a part number that is unique to the row,
then this is how I might approach your scenario.
I will call your changes table tblChanges and the table we want to update
tblTarget. I am assuming that tblChanges and tblTarget contain the same
fields with the same names so we can dump a tblSource row straight into the
tblTarget.

Update the existing records, then append the new ones.

UPDATE tblTarget INNER JOIN tblChanges
ON tblTarget.PartNo = tblChanges.PartNo
SET tblTarget.Price = tblChanges.Price;

INSERT INTO tblTarget
SELECT tblChanges.*
FROM tblChanges LEFT JOIN tblTarget
ON tblChanges.PartNo = tblTarget.PartNo
WHERE tblTarget.PartNo Is Null;

Hope this helps,
Gordon

 

[Lasse T]

Hello and thank you for this.
I think I understand how this would work but I´m not sure this is exactly
what I need.

The users has several price lists to choose from and they download and
install the ones they need. Then they calculate their own prices from the
prises in the list so every users price list is unique after the import
process is done. As I said before, they download Excel sheets today and
import them inte my application. This works fine exept for the users that
dont have basic computer knowledge. They find it too hard to download,
rename and place the file where it should be for the import funktion to
work. Therefore I would like to let them download a small Access database
instead, place it anywhere, open it, push a button and the price list will
be transferred to their main backend. All without them needing to do
anything in their application. When one year has passed the same price list
will be available for download again with some new items and new prices for
the existing items. That´s why I can´t use the append query.

I still want to do this from one database to another and I would very much
like to do this without making any changes to their existing applications.
Ther backends holds a lot of other information sush as customers, orders,
invoices and so on. Unique for every user.

Or maybe there is a completely other solution to this?

Lasse T

 

[gllincoln]

Hi Lasse,

If you want to add new items, you have to append them - there is no other
way. The style of the Append query using a left join filtering out all
non-nulls on the right side, only inserts new records - where no matching
part number exists in the currrent table.

Cordially,
Gordon

 

[Pete D.]

I would use two querys, one update and one append, chain them together with
a macro or VBA. I would also include a status field which the one query
could mark as updated and and the other could mark as added. User will
still have to have the file in the correct location to be processed.

Hi Lasse,

If you want to add new items, you have to append them - there is no other
way. The style of the Append query using a left join filtering out all
non-nulls on the right side, only inserts new records - where no matching
part number exists in the currrent table.

Cordially,
Gordon