[thelist] Problems with deleted records in Access

[Oliver Lineham]

At 09:48 7/11/2000 -0500, you wrote:

>try to narrow it down by isolating the culprit
>run everything from the query window in access and i bet you won't be able
>to reproduce the problem (if you can, please send me your mdb file
>offlist... i gotta see this for myself, eh)

the problem is, it isn't terribly reproduceable.  sometimes it happens, 
sometimes it doesn't.  doesn't seem to matter whether the delete comes from 
a query window, or an ODBC SQL query.

i put it down to the flaky mdb format, or a "mildly" corrupt database.

>maybe your mdb is set up as read only on the server -- sorry, i don't know
>how to check this, i'm not a sysadmin

nope, that's not it.

>try doing a query on the deleted records immediately after deleting them
>and before adding the others

when the problem happens, after the record is deleted it appears to be 
deleted.  it doesn't show up in any queries.  you can add/remove things 
from other tables, or modify existing records in the same table - and the 
record stays deleted.  go and add a new record, and (sometimes) the record 
reappears.

i deleted the whole table and recreated it, and the problem hasn't happened 
since.  but it's got me worried enough.

><tip>
>do not add a "delete" field to the data model unless compelled -- and being
>compelled has nothing to do with getting it to work now, but rather for the
>purpose of data retention for later reporting, e.g. recalculating an amount
>as of a specific point in history
></tip>

the table is of job vacancies.  there are showdate and expiredate fields, 
describing the time period for the vacancy's display.  the "correct" way 
for a vacancy to be pulled off the site early is to amend the expiredate to 
read yesterday (allows us to determine how long any vacancy ever on the 
site, was displayed for).

given that design, the only truly "deleted" records would be ones with junk 
data that never were and never will be displayed on the site (showdate and 
expiredate fields with the same date would imply it was displayed for a 
day, and a showdate that is after the expiredate is whack - i'd rather 
avoid that kludge).

so rudy, can you think of a good justification for marking records as 
deleted rather than actually deleting them?   (so i don't feel so dirty for 
using a "deleted" field purely to safeguard against the Access problem)

</ol>

____________________________________________________
     v i b e   m e d i a    http://www.vibe.co.nz/
  po box 10-492              wellington, new zealand
  phone +64 21 210-7845         oliver at lineham.co.nz