Start Free Trial

developingprogrammer

asked on

error: Could not update; currently locked.

guys,

here's my situation - it's very very much like the poster of the link here (http://stackoverflow.com/questions/5072486/page-level-locking-in-jet-4-0-insert-cant-update-currently-locked-error)

difference is that i'm trying to delete records instead of add records.

i have a form which has a subform. the subform is populated with a paramter query opened as a dynaset. when the user chooses a different date on the form, i want to delete all the records from the subform's table and insert new ones based on the new choice.

when i do this without transactions, it works perfect. when i do this with transactions, i always get this problem

Error Description: Could not update; currently locked.
Error Number: 3218

i am very very new to page and record / field level locking. i know what it is theoretically but i've not solved problems / configured locking before because i didn't need to delve that deep.

here's my sample database. when you open the database, the DashboardManager form opens. click on Work Time on the upper portion. then keep click between sunday and monday. the error will pop up.

i tried to isolate the error by deleting all other unnecessary forms, queries, tables, code modules as well. i've managed to get it to work with this other database that i've attached here as well. so i tried to zoom in to delete only specific objects and compile and decompile and see if it works. however after much testing it seems like it's random. i'm really not sure why this is happening guys.

could yall guide me as to why this is happening or share with me how i can troubleshoot? thanks!!

edit guys - the pared down database i attached here named as working - this also doesn't work now. it's really weird. i think it's an access application thing not the database? i'm not sure. if yall could help guide me that would be great!! thanks guys!! = ))

User generated image

User generated image

problem.zip
working.zip

I'm unable to open your attachments (policy).

Generally, I will either remove the sourceObject property of the subform before trying to delete from the table that the subform is based upon.

developingprogrammer

ASKER

i did think of that as well and i'm trying that right now. but just for learning and moot sake - why does it matter? so what if the subform is still bound to the objectcontainer?

i was thining that the thing that might make more sense is really setting the recordset to nothing and closing it.

let me try both right now and report.

btw

mx's comments on this issue
https://www.experts-exchange.com/questions/24079974/Error-3188-Could-not-update-currently-locked-by-another-session-on-this-machine.html
i've made sure everywhere is set to no locks.

LSMConsulting and boag2000's comments on this issue
https://www.experts-exchange.com/questions/24045938/Error-message-when-deleting-last-record-Could-not-update-currently-locked.html
my table is a stand alone table - just a temporary table with no required fields, no relationships, no referential integrity. nothing. just standalone.
================
ok fyed, i tested and i realised that

Me.subDetails.Requery

causes the problem. if i comment out that line, all the delete and insert queries work fine. but now my subform isn't showing the right records.

hrmm, why is this happening and what can i do to fix it fyed?

don't know why the requery would cause the problem.

can you post the code before and after that line (maybe that entire procedure)? like I said, I cannot open the attachments at this time.

developingprogrammer

ASKER

ok subform is bound to the actual table itself. no queries in between. how can i do a requery then. think think. does it make sense to requery a table? does it lock the table? it's not like the recordset right, or maybe putting the recordsource as a table it is doing like a recordclone and setting that to the form's recordset.

locking on subform is set to no locks

developingprogrammer

ASKER

yes sir i can! = )

Private Sub lstDates_AfterUpdate()
    ' Comment :
    ' Params  :
    ' Created : 10 Sep 13 1:13:00 PM ix
    Dim strSQL_Insert   As String
    Dim strSQL_Delete   As String
    Dim strDayOfWeek    As String
    Select Case optNewArchived
        Case 1 'new
            If Me.lstDates = "Date" Then
            Else
                strDayOfWeek = Format(Me.lstDates, "dddd")
                            
                strSQL_Delete = "DELETE * FROM perfWorkTimeActual_RollForward;"
                strSQL_Insert = "INSERT INTO perfWorkTimeActual_RollForward ( FKCategory, " & _
                                            "StartTime, EndTime ) " & _
                                            "SELECT perfWorkTimeStandard.FKCategory, " & _
                                            "perfWorkTimeStandard.StartTime, perfWorkTimeStandard." & _
                                            "EndTime " & _
                                            "FROM perfWorkTimeStandard WHERE perfWorkTimeStandard.DayOfWeek = """ & strDayOfWeek & """;"
                
                'Me.subDetails.Form.Recordset.Close
                modGlobalErrorHandler.DoNotShowDebugDialog
                DBEngine.Workspaces(0).BeginTrans
                    With CurrentDb
                        Call .Execute(strSQL_Delete, dbFailOnError)
                        Call .Execute(strSQL_Insert, dbFailOnError)
                    End With
                modGlobalErrorHandler.ShowDebugDialog
                DBEngine.Workspaces(0).CommitTrans
                Me.subDetails.Requery 'if i take this line out, it works but subform isn't showing correctly records
            End If
                            
        Case 2 'archived
        Stop
    End Select
ErrEx.CatchAll
    DBEngine.Workspaces(0).Rollback
    modGlobalErrorHandler.ShowDebugDialog
    modGlobalErrorHandler.ShowCustomErrorMessageToUser
    modGlobalErrorHandler.Unwind
ErrEx.Finally

End Sub

Open in new window

developingprogrammer

ASKER

so ok fyed i did more testing.

it seems that the insert sql is causing the problem.

if i take away the where clause, it works perfect. if i reinsert the where clause, it throws the error again.

Why all this hassle? You already have the table at hand.

All you need is to loop through the RecordsetClone of the subform and delete the records one by one. Very speedy.

/gustav

developingprogrammer

ASKER

then reinsert using a recordset filled by the insertSQL right?

hrmm ok can do so but i wanna try and use SQL in a database instead of VBA.

i mean i can solve this using VBA but then i am missing a huge learning opporunity that would definitely affect me in the future when my database fails in front of my user for the same problem and cause i didn't figure this out now i can't fix it on the spot as well = (

I think your problem is that you don't have an "Exit Sub" in there somewhere, so no matter what you do, it gets to your error handler.

I don't understand why you would be using transaction processing with this temporary table anyway.

developingprogrammer

ASKER

ah fyed, i'm using vbwatchdog - the global error handler software. so ErrEx.CatchAll and there is no error i jumps to ErrEx.Finally

developingprogrammer

ASKER

hrmm i'm using transaction cause... err... ok don't laugh or get mad ok? i'm using it from a stupid purist purpose and also training myself haha = P

but that it can't work here though it looks all very logical, there is something that i haven't gotten to the bottom of and that will make me look like a moron very soon if i don't understand it and this bug happens again. kinda improving by learning all the ways how things can go wrong instead of how they can go right = )

> then reinsert using a recordset filled by the insertSQL right?

No. Continue using the RecordsetClone.

> hrmm ok can do so but i wanna try and use SQL in a database instead of VBA.

Well, that is just to make it longwinded. Using the RecordsetClone you don't even have to requery the subform. It is too easy. And fast.

You will have plenty of other areas where you can play with SQL.

/gustav

developingprogrammer

ASKER

hrmm gustav, so sorry to ask a dumb question, but could you elaborate what you mean?

in my head when you tell me to use recordsetclone, i'm thinking,

1) recordsetclone is used so that the user won't see the cursor move around.
2) i use recordsetclones for moving around and then setting clone's bookmark to the recordset's bookmark so won't jump around.
3) if i delete i have to delete from the recordset, not the clone cause the clone is just a shadow duplicate.
4) then i have the insert into the recordset again the new records i want.

so sorry gustav, i don't think i really understand what you mean when you say use recordsetclone. could you share with me a bit more? thanks!! = ))

developingprogrammer

ASKER

guys so for the code above (repeated here for easy reference),

Private Sub lstDates_AfterUpdate()
    ' Comment :
    ' Params  :
    ' Created : 10 Sep 13 1:13:00 PM ix
    Dim strSQL_Insert   As String
    Dim strSQL_Delete   As String
    Dim strDayOfWeek    As String
    Select Case optNewArchived
        Case 1 'new
            If Me.lstDates = "Date" Then
            Else
                strDayOfWeek = Format(Me.lstDates, "dddd")
                            
                strSQL_Delete = "DELETE * FROM perfWorkTimeActual_RollForward;"
                strSQL_Insert = "INSERT INTO perfWorkTimeActual_RollForward ( FKCategory, " & _
                                            "StartTime, EndTime ) " & _
                                            "SELECT perfWorkTimeStandard.FKCategory, " & _
                                            "perfWorkTimeStandard.StartTime, perfWorkTimeStandard." & _
                                            "EndTime " & _
                                            "FROM perfWorkTimeStandard WHERE perfWorkTimeStandard.DayOfWeek = """ & strDayOfWeek & """;"
                '"FROM perfWorkTimeStandard WHERE perfWorkTimeStandard.DayOfWeek = """ & strDayOfWeek & """;"
                
'problems - if i remove WHERE from SQLinsert- ok works. if i remove transactions, works. if remove requery, works.
                
                
                'modGlobalErrorHandler.DoNotShowDebugDialog
                'DBEngine.Workspaces(0).BeginTrans
                    With CurrentDb
                        Call .Execute(strSQL_Delete, dbFailOnError)
                        Call .Execute(strSQL_Insert, dbFailOnError)
                    End With
                'modGlobalErrorHandler.ShowDebugDialog
                'DBEngine.Workspaces(0).CommitTrans
                Me.subDetails.Requery 'if i take this line out, it works but subform isn't showing correctly records
            End If
                            
        Case 2 'archived
        Stop
    End Select
ErrEx.CatchAll
    'DBEngine.Workspaces(0).Rollback
    'modGlobalErrorHandler.ShowDebugDialog
    'modGlobalErrorHandler.ShowCustomErrorMessageToUser
    modGlobalErrorHandler.Unwind
ErrEx.Finally

End Sub

Open in new window

1) if i remove the WHERE clause, it works. ==> but i don't get the records i want
2) if i remove transactions it works. ==> but i'm avoiding understanding this problem then
3) if i remove the requery, it work. ==> but i'm not showing the right thing then
4) if i close the subform's recordset, it work. ==> but i might as well not even have a subform then

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

developingprogrammer

ASKER

Great thanks so much fyed!! = ))

Please guide me if I'm wrong on the following - the with end with block only gets 1 currentdb right? So it's shorthand for putting the currentdb into a variable. That's what I understand but please correct me if I'm wrong! = ))

I am not sure about how the With/End with will work with CurrentDb. I would think it would only get 1 instance, but honestly don't know. I got in the habit of instantiating a new object a long time ago, and that just became one of my "best practices".

developingprogrammer

ASKER

cool thanks fyed! hrmm maybe i can share my experience with with and end with?

i believe it's the same object if it's within the block.

and the "proof of the pudding" is -

with currentdb
call .execute strSQL,dbFailOnError
msgbox .recordsaffected
end with

the above code works.

however if i used currentdb twice as a qualifier instead of the with block, then recordsaffected will always be 0 even if there really were records affected = )

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

developingprogrammer

ASKER

O M G
O M G
O M G
O M G
O M G
O M G

developingprogrammer

ASKER

Ron how did you know that??????!!!!!!!!!!!! you fixed it!!!!!!!!!!!!!!!!!! oh my goodness!!!!!!!!!!!!!!

guys we've found a living genius. (not that yall didn't know that already a long time ago!! haha = )) )

WHAO Ron you fixed it!!

but whats the difference between Me.subDetails.Requery and Me.subDetails.Form.Requery? why did the first one give me so much problem? i thought without the Form qualifier it would still work? the subform is hardcoded into the container's property and not set at runtime. the recordset is also hardcoded into the subform's property. so i thought that Me.subDetails.Requery will already automatically refer to it. why did i still need the Form qualifier? and why did it work randomly with the lack of Form qualifier?

THANKS SOOOOOOOOOOOOOO MUCH RON!!!!!!!!! i've learn something huge today!!

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

developingprogrammer

ASKER

ok thanks gustav! i went to re-read the help file about recordsetclone and this was the paragraph that stood out

You use the RecordsetClone property to navigate or operate on a form's records independent of the form itself. For example, you can use the RecordsetClone property when you want to use a method, such as the DAO Find methods, that can't be used with forms.

i guess the thing i don't understand is - why would you use a recordsetclone instead of the subform's recordset itself?

Because it is the records you view in the (sub)form including filtering and sorting.

/gustav

developingprogrammer

ASKER

Hrmm so so sorry gustav, still don't really understand! (Dumb dumb me!!)

Oh!! Ok I think I may get it!!

Subform recordset
Consists of the records that we visually see. So if I applied a filter and 50 out of 100 records and filtered out, the subform recordset will consist of 50 records.

Subform recordsetclone
Consists of ALL the 100 records, even though the user applied a filter and the filter removed 50 records visually.

Is that correct?

It's vice versa, I guess.

I never user Recordset; a few times - however, and just to add confusion - I use Recordset.Clone when I need a mirror of the form's records that can be browsed and read independently of RecordsetClone.

/gustav

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

> Use Recordsets only if you see really no other chance to achive something.
> Anything else about data belongs to SQL.

Sorry, but this is a false statement.

The original question was about deleting and creating a dozen records or so, and using anything else than the recordsetclone for this is neither clever nor smart.

We are not talking about thousands of records; those you would, of course, handle differently.

/gustav

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

You are thinking too narrow. There is more in this than communicating with the server.

Besides, I understand this scenario is Access only. Then the use of the recordsetclone outperforms anything else.

/gustav

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

But that's only a part of it.

Using the recordsetclone, creating and deleting 20 records is about 0.01 s and 0.06 s and that's the full operation from click button to updated and repainted form.

Also, and that's important, it is using minimal code, thus highly productive.

/gustav

Gustav, I don't want to convince you. If you want to use Recordsets, do it.

You don't have to. I know SQL very well and when to use it.

/gustav

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

developingprogrammer

ASKER

thanks everybody for sharing your comments here! let me read through them again! = ))

fyed you're actually right and this is one thing i came across when i read gustav's post on recordsetclone - but i thought it's important for me to understand why he would use recordsetclone generically so i thought the application to my situation was of secondary importance then ha = )

Christian thanks so much for answering the Me.subDetails.Requery and Me.subDetails.Form.Requery thingy! it was bugging me a lot!! and now i know where to take confidence from - testing instead of help files!! beginners always depend on help files as fixed reference because we don't know enough to understand if we're testing all the parameters!! = ))

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

developingprogrammer

ASKER

I see cool thanks IrogSinta!! I definitely will ALWAYS use the form qualifier from now on!! = ))

> .. the table the OP is importing from is not a part of the forms recordset

I missed that, sorry.

/gustav

developingprogrammer

ASKER

No no gustav, you're teaching me something else that I didn't know so your comments are really helping me! = ))

developingprogrammer

ASKER

whao guys! thanks so much for all your help guys!! ok can't type too much now if no i'll get scolded by big boss!! but Christian thanks so much for you comprehensive explanation and also! --> your real world tests; both local and network environment!! THANKS SO MUCH EVERYONE!! = ))