Table Creating New Table Problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have got my CSV file import working perfectly, thanks to this forum. And,
I can programmatically execute queries to build the new table.......but (and
a big but), I can't figure out how to update the second table conditionally.
Here's the details:

Import table has: Unit Number, Resident Last Name, Resident First Name

New Table has: Unit Number, Resident Full Name, Date of Birth

The queries are fine when I want to populate the New table from scratch, but
during production processing, I want to only update in this manner:

If the [Import table].Unit Number & [Import table].(Resident First Name +
Resident Last Name) is NOT on the New table, add it.

If the [Import table].Unit Number & [Import table].(Resident First Name +
Resident Last Name) is on the New table, skip to the next record in the
Import table

If the [New Table].Unit Number & [New Table].Resident Full Name is Not in
the Import Table, delete it from the New table.


I've done something similar to this a hundred years in COBOL/DB2, but can't
seem to find the way in VBA Access. Doing it programatically in VBA with
recordsets started out fine, but I can't get to the actual field values in
the resulting recordset!

Sorry about the "Great American Novel" here.......
 
I think you can solve it this way:

Create a primary key on the combination of unit Number and Resident Full
Name.
Prior to the import, delete everything from the new table.
Make sure that the import continues upon an error (On error resume
next). This is necessary because you don't want duplicates to be
imported (this relieves you from checking and skipping the imported
record if it already exists).

kr
Ben.
 
Back
Top