Problem Deleting records using DAO in Access 2000

  • Thread starter Thread starter Julian Cropley
  • Start date Start date
J

Julian Cropley

I have some code which I am writing, and it currently has
me baffled. After the below procedure has run once and
presumably deleted a record, the next piece of code to
run that accesses this table comes up with error 3167:
Record is deleted. Am I forgetting something when I
delete?

Private Sub makeAvailable(Day, hours, minutes)
Dim hoursUnavailableRST As DAO.Recordset
Dim sql As String

sql = "select * from staffHrsAvailability where staffid="_
& Me.StaffID & " and day=" & "'" & Day & _
"' AND [Start Time]= #" & _
Format$(Format$(Str(hours), "00") & ":" & _
Format$(Str(minutes), "00"), "Long Time") & "#;"


Set hoursUnavailableRST = CurrentDb.OpenRecordset(sql,
dbOpenDynaset, dbSeeChanges)

hoursUnavailableRST.MoveFirst
hoursUnavailableRST.Delete
hoursUnavailableRST.Close

Set hoursUnavailableRST = Nothing

End Sub




help much appreciated
Julian Cropley
 
Julian Cropley said:
I have some code which I am writing, and it currently has
me baffled. After the below procedure has run once and
presumably deleted a record, the next piece of code to
run that accesses this table comes up with error 3167:
Record is deleted. Am I forgetting something when I
delete?

Private Sub makeAvailable(Day, hours, minutes)
Dim hoursUnavailableRST As DAO.Recordset
Dim sql As String

sql = "select * from staffHrsAvailability where staffid="_
& Me.StaffID & " and day=" & "'" & Day & _
"' AND [Start Time]= #" & _
Format$(Format$(Str(hours), "00") & ":" & _
Format$(Str(minutes), "00"), "Long Time") & "#;"


Set hoursUnavailableRST = CurrentDb.OpenRecordset(sql,
dbOpenDynaset, dbSeeChanges)

hoursUnavailableRST.MoveFirst
hoursUnavailableRST.Delete
hoursUnavailableRST.Close

Set hoursUnavailableRST = Nothing

End Sub
Click to expand...

If "the next piece of code to run that accesses this table" has its
recordset already open and containing the record you just deleted, any
reference to that record is going to give the "record deleted" error.
Is that what's going on? You may have to requery that recordset.
 
Dirk has doubtless solved your problem.

However, how does it make sense to delete "the first record" from the
recordset? The recordset is based on a table (it seems) & it does not have
any ORDER BY clause. So the identity of the "first" record in unpredictable,
in a relational db like Access. It might >seem< to be consistent (due to PK
indexes etc.), but this should never be relied on.

HTH,
TC
 
If you posted your own code that does not work, someone would doubtless
solve your problem in 5 minutes!

Cheers,
TC


Donna Watling said:
Hi Julian

I'm afraid I don't know why your code it not working, but thanks tonnes -
Click to expand...
I've been struggling with getting any sort of recordset working in my code
and I've just nicked your first few lines and managed to move through some
records!
 
If "the next piece of code to run that accesses this table" has its
recordset already open and containing the record you just deleted, any
reference to that record is going to give the "record deleted" error.
Is that what's going on? You may have to requery that recordset.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Thankyou Dirk, but thats not the problem. The next piece of code to run could be one of several procedures, which each open a new recordset object on the same table, this always fails with the record deleted error. The recordset that I deleted a record from has already been closed and set to nothing.

tia Julian Cropley
 
-----Original Message-----
Julian Cropley said:
I have some code which I am writing, and it currently has
me baffled. After the below procedure has run once and
presumably deleted a record, the next piece of code to
run that accesses this table comes up with error 3167:
Record is deleted. Am I forgetting something when I
delete?

Private Sub makeAvailable(Day, hours, minutes)
Dim hoursUnavailableRST As DAO.Recordset
Dim sql As String

sql = "select * from staffHrsAvailability where staffid="_
& Me.StaffID & " and day=" & "'" & Day & _
"' AND [Start Time]= #" & _
Format$(Format$(Str(hours), "00") & ":" & _
Format$(Str(minutes), "00"), "Long Time") & "#;"


Set hoursUnavailableRST = CurrentDb.OpenRecordset(sql,
dbOpenDynaset, dbSeeChanges)

hoursUnavailableRST.MoveFirst
hoursUnavailableRST.Delete
hoursUnavailableRST.Close

Set hoursUnavailableRST = Nothing

End Sub
Click to expand...

If "the next piece of code to run that accesses this table" has its
recordset already open and containing the record you just deleted, any
reference to that record is going to give the "record deleted" error.
Is that what's going on? You may have to requery that recordset.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...

Thanks for your response Dirk. No that is not whats going on, the next piece of code to run could be one of two procedures, it could be the same procedure again, or it could be one to add a record to the table. However in both instances a new recordset object is created and opened, this is where the error occurs (on openrecordset).

Julian Cropley
 
Are all of your recordsets running off the same database reference
(dbengine(0)(0), or currentdb)? Or are some of them running off
dbengine(0)(0), and others off currentdb()? Maybe that's the problem.

HTH,
TC


If "the next piece of code to run that accesses this table" has its
recordset already open and containing the record you just deleted, any
reference to that record is going to give the "record deleted" error.
Is that what's going on? You may have to requery that recordset.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Thankyou Dirk, but thats not the problem. The next piece of code to run
could be one of several procedures, which each open a new recordset object
on the same table, this always fails with the record deleted error. The
recordset that I deleted a record from has already been closed and set to
nothing.

tia Julian Cropley
 
Julian Cropley said:
Thankyou Dirk, but thats not the problem. The next piece of code to
run could be one of several procedures, which each open a new
recordset object on the same table, this always fails with the record
deleted error. The recordset that I deleted a record from has already
been closed and set to nothing.

tia Julian Cropley
Click to expand...

Have you set a breakpoint and traced through the code to see exactly
which line it happens on? Please do, and report back with the substance
of the relevant code procedures. I saw this error raised just recently
in a newsgroup post, and it turned out that the user had deleted, in
code, the current record of the form, and then was attempting to build
an SQL string that referred to the bound controls of the form. I don't
suppose that is what's happening to you?
 
TC said:
Dirk has doubtless solved your problem.
Click to expand...

Apparently not. said:
However, how does it make sense to delete "the first record" from the
recordset? The recordset is based on a table (it seems) & it does not
have any ORDER BY clause. So the identity of the "first" record in
unpredictable, in a relational db like Access. It might >seem< to be
consistent (due to PK indexes etc.), but this should never be relied
on.
Click to expand...

TC's point is well taken. If the criteria of your SELECT statement are
expected to reliably return at most a single record, then it would be
much more efficient to execute a delete query with those criteria,
rather than open a recordset just to delete that record. If on the
other hand the criteria *don't* identify a single record, then how do
you know which record you are deleting? Or do you not care?
 
Back
Top