What is the best way to do this ???

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

Guest

I have a user directory table. This is populated by importing data from an external telephone directory database (also Access)

However, the complication is that since I have referential integrity defined. I must retain records of people who have left so that the detail records still can refer to them. I therefore added an Active flag (True/False)
So my import needs to
(a) Update data for all matching peopl
(b) Add all records for new peopl
(c) Turn off active flag for anyone missin

What is the best way to code the import routine? I'm using ADO and Access 2000.
 
Hi David,

Simplest to do this with three queries. I'll call the tables tblDir and
tblExternal, and assume their primary key is UserName.

1) an query that joins the two tables on UserName and updates tblDir
fields from the corresponding fields in tblExternal WHERE
tblDir.UserName = tblExternal.UserName

2) an append query that appends unmatched records from tblExternal (i.e.
WHERE tblExternal.UserName NOT IN (SELECT UserName FROM tblDir) ).

3) an update query that turns off the active flag in records in tblDir
WHERE tblDir.UserName NOT IN (SELECT UserName FROM tblExternal)

Build and test the queries in the usual way. Then execute them from your
code (or else paste their SQL statements into the code and execute them
that way).
 
Back
Top