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!! = ))

dashboardworktimeproblem.zip
working.zip
developingprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
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.
0
developingprogrammerAuthor Commented:
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
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24079974.html
i've made sure everywhere is set to no locks.

LSMConsulting and boag2000's comments on this issue
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Forms/Q_24045938.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?
0
Dale FyeCommented:
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.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

developingprogrammerAuthor Commented:
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
0
developingprogrammerAuthor Commented:
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

0
developingprogrammerAuthor Commented:
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.
0
Gustav BrockCIOCommented:
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
0
developingprogrammerAuthor Commented:
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 = (
0
Dale FyeCommented:
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.
0
developingprogrammerAuthor Commented:
ah fyed, i'm using vbwatchdog - the global error handler software. so ErrEx.CatchAll and there is no error i jumps to ErrEx.Finally
0
developingprogrammerAuthor Commented:
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 = )
0
Gustav BrockCIOCommented:
> 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
0
developingprogrammerAuthor Commented:
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!! = ))
0
developingprogrammerAuthor Commented:
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
0
Dale FyeCommented:
So, if you debug.print strSQL_Insert and then run that query from the query window, will it work?

I hate to ask the obvious, but is the [DayOfWeek] field in your [perfWorkTimeStandard] table a string or a number?  That is the only think I could think of that would cause the problem.  

Just so you know, I never use currentdb in a With/End with context.  I always declare another object (db) with:

Dim db as DAO.Database
set db = CurrentDb

Everytime you use CurrentDb, you get a new instance of the current database.  If you do this in a loop, it can significantly slow down the application.  Although you are not doing it in a loop, this may have some relation to the problem with the transaction.

Although Gustav has chimed in, none of the other experts have joined this conversation.  Let me alert them of the question.  Maybe one of them can provide more insight.
0
developingprogrammerAuthor Commented:
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! = ))
0
Dale FyeCommented:
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".
0
developingprogrammerAuthor Commented:
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 = )
0
IrogSintaCommented:
Change line#34 to:
Me.subDetails.Form.Requery

Ron
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
developingprogrammerAuthor Commented:
O  M  G
O  M  G
O  M  G
O  M  G
O  M  G
O  M  G
0
developingprogrammerAuthor Commented:
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!!
0
Gustav BrockCIOCommented:
> .. could you elaborate what you mean?

As said, this is too easy, nearly instantaneous, and doesn't require any requiring:

Create two buttons on the main form and add this code:
Private Sub btnCreate_Click()

    Dim rst         As DAO.Recordset
    Dim intCount    As Long
    
    Set rst = Me.<nameofyoursubformcontrol>.Form.RecordsetClone
    For intCount = 1 To 10
        rst.AddNew
            rst!SomeFieldName.Value = intCount
            rst!SomeOtherFieldName.Value = 2
        rst.Update
    Next
    
    Set rst = Nothing
    
End Sub

Private Sub btnDelete_Click()

    Dim rst         As DAO.Recordset
    
    Set rst = Me.<nameofyoursubformcontrol>.Form.RecordsetClone
    rst.MoveFirst
    While Not rst.EOF
        rst.Delete
        rst.MoveNext
    Wend
    
    Set rst = Nothing

End Sub

Open in new window

/gustav
0
developingprogrammerAuthor Commented:
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?
0
Gustav BrockCIOCommented:
Because it is the records you view in the (sub)form including filtering and sorting.

/gustav
0
developingprogrammerAuthor Commented:
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?
0
Gustav BrockCIOCommented:
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
0
BitsqueezerCommented:
Hi,

I personally would also never use the Recordset stuff to do such mass operations. If you delete and insert records using a Recordset loop you send one command for each operation, that means, one INSERT and one DELETE. With 50 records you will execute 100 commands on the database. If the database is on a network, that's no good idea, although it surely will work and maybe a while performant enough. If 2000 users do that it looks different. And the problem grows bigger when the number of records will be 5000 for example. Means 10,000 commands executed.... 3 users does that, means 30,000 commands will be executed.

The SQL way is the right one to use in a database: Execute ONE mass command to delete all and ONE mass command to insert all what you need.

In the end a requery is the only thing needed - and also only once.

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

Transactions: I've never worked with DAO transactions, only on SQL Server. The workspace concept of DbEngine I've never really tried to understand, so I'm not sure about the following: When you start a transaction using Workspace(0) and later inside the workspace transaction a CurrentDb (which, like fyed also said above creates a new instance of the workspace object) is used I would not be sure if that is really part of the transaction anymore. So maybe a "DBEngine(0)(0)" could be better here as exchange for CurrentDb.

Transactions are sometimes a little bit tricky, especially if you use DELETE and INSERT and try to read out the table before you submitted the transaction. SQL Server has the ability to save a transaction "partially" so that you can work with the data before the transaction is completely commited. What I want to say is: You must test your code very thoroughly in cases where you use transactions and additionally you must really simulate more than one user trying the same thing at the same time in such cases, otherwise it is possible that they lock each other in specific situations and other issues which you never see while you are working alone on your solution.

You can of course use CurrentDb within a With block, but as fyed also said, don't do that in a loop for the reasons he told. In other cases it is the same as setting a db variable but you would not need to declare the variable. Another possibility is to use a public function in a standard module which always holds one instance of the CurrentDb function.

Requery of a subform object: You are right with your assumption: Following the Access help a "sf.Requery" would be the same as "sf.Form.Requery". If that makes a difference here I would say that "sf.Requery" has a bug, but I cannot say that, never tested. The help looks like someone has made a copy/paste of the Combobox requery and only added the first sentence. So you cannot always trust the help, some things can only found out by trying that out.

Cheers,

Christian
0
Gustav BrockCIOCommented:
> 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
0
BitsqueezerCommented:
Hi gustav,

no, that's the wrong thinking. It's now a dozen, but what is it in future? Do you know that?

Do it in the database way all the time and you never need to think about that because you have ALWAYS the fastest performance instead of a performance which will get slower and slower.

12 records means 24 commands, I would need 2 only. If it are 100 it would mean 200 commands. I would need 2 only. Always.

Cheers,

Christian
0
Gustav BrockCIOCommented:
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
0
BitsqueezerCommented:
Hi gustav,

I would say: If the backend is Access it is even more important to use SQL as you have a "dead" backend which is slower than a database server.

Let's do a test (see attached file):
If I start the test locally on my desktop PC this is the result (the number in brackets is the number of records):

INSERT Recordset(10): 0
INSERT SQL(10): 0
DELETE Recordset(10): 0
DELETE SQL(10): 0
---------
INSERT Recordset(100): 0,015625
INSERT SQL(100): 0
DELETE Recordset(100): 0,015625
DELETE SQL(100): 0
---------
INSERT Recordset(1000): 0,078125
INSERT SQL(1000): 0,015625
DELETE Recordset(1000): 0,140625
DELETE SQL(1000): 0,015625
---------
INSERT Recordset(10000): 0,828125
INSERT SQL(10000): 0,09375
DELETE Recordset(10000): 1,34375
DELETE SQL(10000): 0,265625
---------
INSERT Recordset(100000): 8,4375
INSERT SQL(100000): 1,15625
DELETE Recordset(100000): 17,765625
DELETE SQL(100000): 1,1640625

Open in new window


As you can see, SQL is faster. Sometimes the DELETE SQL is a little bit slower, but only if there are a few records only. With growing size SQL always outperforms the Recordset.

The next test shows the problem very much more. This time I simply copied the database to a file server in a 100MBit network where currently I am the only one using it. Then I started the database from there and started the same test with these extreme results:

INSERT Recorset(10): 0,140625
INSERT SQL(10): 0,015625
DELETE Recordset(10): 0,09375
DELETE SQL(10): 0,015625
---------
INSERT Recorset(100): 0,828125
INSERT SQL(100): 0,015625
DELETE Recordset(100): 1,4765625
DELETE SQL(100): 0,03125
---------
INSERT Recorset(1000): 9,25
INSERT SQL(1000): 0,03125
DELETE Recordset(1000): 19,9375
DELETE SQL(1000): 0,09375
---------
INSERT Recorset(10000): 107,1640625
INSERT SQL(10000): 0,171875
DELETE Recordset(10000): 200,9765625
DELETE SQL(10000): 0,4921875
---------
INSERT Recorset(100000): 1040,265625
INSERT SQL(100000): 1,546875
DELETE Recordset(100000): 2399,6484375
DELETE SQL(100000): 4,9453125
---------

Open in new window


In the last test the difference is from around 40 minutes(!) to 4,9 seconds.

Still the opinion that recordsets are the better idea?

Cheers,

Christian
RecordsetDelete.zip
0
Gustav BrockCIOCommented:
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
0
BitsqueezerCommented:
Gustav, I don't want to convince you. If you want to use Recordsets, do it.
0
Gustav BrockCIOCommented:
You don't have to. I know SQL very well and when to use it.

/gustav
0
Dale FyeCommented:
Gustav,

The point I don't get is that the table the OP is importing from is not a part of the forms recordset, so using RecordsetClone is not going to work for writing the new records.  Unless, of course you either create a new recordset that points to table [perfWorkTimeStandard] and loops through that recordset.
0
developingprogrammerAuthor Commented:
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!! = ))
0
IrogSintaCommented:
whats the difference between Me.subDetails.Requery and Me.subDetails.Form.Requery?
I cannot give you any guidance on documented differences between the two but apparently there at least 2 undocumented ones, one of which you've already discovered.

I always use the Form qualifier when requerying a subform because of another quirk I uncovered awhile back.  After a requery we had certain subforms that showed that very last record at the top making it appear the many records had been removed.  In those instances, you would have to scroll up to reveal the other records.  Using Form.Requery was the fix for that as well.  

What I just described is in Access 97 so I can't say whether or not this is still an issue since I have not tested this it in newer versions.
0
developingprogrammerAuthor Commented:
I see cool thanks IrogSinta!! I definitely will ALWAYS use the form qualifier from now on!! = ))
0
Gustav BrockCIOCommented:
> .. the table the OP is importing from is not a part of the forms recordset

I missed that, sorry.

/gustav
0
developingprogrammerAuthor Commented:
No no gustav, you're teaching me something else that I didn't know so your comments are really helping me! = ))
0
developingprogrammerAuthor Commented:
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!! = ))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.