"triaging" records from external DB to either update or insert into local access DB

[bbasberg]

Hello,

I thought I would do a "sanity check" by asking the experts how to do the following:

I need to process external data (it will be in an Access DB table and will be updated daily) into a local Access DB table that has the same structure with additional fields. The local table is prexisting and the designer used an autonumber field as its key. There is a "real key" between the two tables (let's call it PK1 for this discussion (it's not the autonumber field)). The external DB table is not keyed. My main question is: if I want to insert new records (those not having a corresponding field PK1 in the local table), and update existing records (the ones with the PK1 field in common), should I set up two recordsets and a case statement to send the new records to insert and the matching records to update? I'm an Access/VBA newbie who sometimes tends to get too complicated for my own good.

I saw an answer to an earlier post: http://www.thescripts.com/forum/thread558797.html

by Mary McCarthy that was interesting. Should I try something like that?

Thanks!!

Barbara

[bbasberg]

Hi again,

Did I make this post hard to understand? I'm a little sleep deprived so please let me know if I should rewrite this so that it makes more sense ;-) and it's clearer what I'm asking.

Thanks.

[MMcCarthy]

First question Barbara

Why are you using a local table. Wouldn't linking to the table in the original database solve your problem.

Mary

[bbasberg]

First question Barbara

Why are you using a local table. Wouldn't linking to the table in the original database solve your problem.

Mary

Hi Mary,

The external Access database will be a file that comes from another group, is cumulative, and refreshed daily with new records added and/or changed. I need to import the data and insert new records into our version of the database, and update existing records. There are extra fields in our local Access DB so it isn't an exact copy but it will have all the fields in the external Access DB. I don't think that's a problem.

What I want to do is set up a form that an end user can use that will let them see only the new records (new meaning whatever they haven't processed yet either this day or previous days). Processed in this case means that they will click a radio button that says "Accepted" or "rejected" based on whether the record pertains to their group or not. Behind the scenes, all the records could go into our local database but I would give them a filtered view of those that they have accepted (in another form that would include all accepted records for the history of the application). The reason I wouldn't mind having all the records I could explain but it would take another page so I will leave that for another time...

The main thing I'm asking is: how can I set up some VBA to process the records to either update existing records or insert new ones? Should I use case statements? Should I use recordsets?

I *think* I might be able to do the other things I have mentioned but am having a hard time with the update/insert VBA since I'm a newbie.

Thanks and sorry this is so long!

Barbara

[MMcCarthy]

Hi Barbara

The question is too broad to answer. The method used will depend on where and from what the inserts and updates will be completed. The easiest method is by form but without a lot more information I can't tell.

Maybe I'm still misunderstanding the question.

Mary

[bbasberg]

Hi Barbara

The question is too broad to answer. The method used will depend on where and from what the inserts and updates will be completed. The easiest method is by form but without a lot more information I can't tell.

Maybe I'm still misunderstanding the question.

Mary

Maybe I should ask one thing at a time instead of building such an elaborate scenario. How about for the first step I ask: Is there a way to "compare" two tables to see which records are new?
Thanks for hanging in with my long-winded questions.

[NeoPa]

Maybe I should ask one thing at a time instead of building such an elaborate scenario. How about for the first step I ask: Is there a way to "compare" two tables to see which records are new?
Thanks for hanging in with my long-winded questions.

I think your first post is along the right lines Barbara.
In Access, it is not even critical to separate the records first.
If you run an update query, (INNER JOIN) linking your two tables via your PK1, then only the correct records will be selected (and updated).
After that you can run an append query (INSERT INTO).
You can, if you prefer, restrict this query to just those records you don't already have, but this is not absolutely necessary as any duplicate records will be dropped anyway (assuming you have set PK1 to a Unique Index in your table).

[MMcCarthy]

Maybe I should ask one thing at a time instead of building such an elaborate scenario. How about for the first step I ask: Is there a way to "compare" two tables to see which records are new?
Thanks for hanging in with my long-winded questions.

Assuming Table1 has the new records and both tables have a primary key called ID ...

SELECT * FROM Table1 LEFT JOIN Table2
ON Table1.ID = Table2.ID
WHERE Table2.ID Is Null;
 

This will return all records from Table1 that don't exist in Table2.

Mary