Delete only appended records (1 Viewer)

[diberlee]

Hi,

I have a database that is used by managers to log activity of staff. We wanted to allow staff to write to the DB, but not allow them full access. We decided to accomplish this by adding a VBA function to an Excel sheet that they already use every day. It allows them to select a description, start time and end time and writes records to a database containing just 1 table.

The database used by the managers periodically runs an append query, and then a delete query to update with details of entries made by staff, and then clear the table to avoid duplicates during the next append.

I'm sure those of you with experience can already guess what's been happening and are shaking your head right now. It seems like entries made while the append query is running are being deleted when the delete query runs. So potentially I append 4 records, but delete 6 as 2 new entries were made before the delete query ran.

Is there a way to delete only the records that have just been appended? Or is there a more acceptable way of achieving this without using a delete query?

Thanks in advance
Duane

 

[llkhoutx]

How do you known what's "newly" appended? Can it be multiple records?

 

[diberlee]

Hi,

I don't know how I would identify what has been newly appended. My append query just appends everything from tbl_outbound, and the delete query deletes everything from the same table.

The queries are run by the timer event of one of my forms, and since 3-4 people have it open there's a whole mess of activity going on. I didn't realise until now that data was being lost until one person who keeps a log of everything came forward to say some of her stuff was missing from the report.

I'm looking into building an unmatched query, so that I can check for records in tbl_outbound that don't match those in tbl_activity aqnd only write those over. Then I can just clear out all data from tbl_outbound a couple of times a month. That seems a more sensible way to go.

 

[Dhamdard]

Create a third query to view only those entries that have been appended. Then run a delete query over this query. This will delete those records only that are already appended. New entries that have not been appended will not be deleted.

Hope this helps.

Dhamdard