Getting lock violations even though all forms have been closed and the new data is different from the exisitng data to be updated

Brian Anderson
Brian Anderson used Ask the Experts™
on
Hello,

 I am running the below code and getting lock violations even though I am closing all forms prior to the update statement.

 When I grab the code from the Immediate window and run it as a Query I do not get the lock violations.

 Not understanding the difference.

 Dim strSQL As String
 Dim temp_CRAS_CAR_Id As Double
 Dim Tmp_Testing_Start_Date As Date

 temp_CRAS_CAR_Id = Me.CRAS_Car_id
 Tmp_Testing_Start_Date = Me.Actual_Testing_Start_Date

 DoCmd.Close acForm, "Frm_Reviews", acSaveYes
 DoCmd.Close acForm, "Frm_User_Dashboard"

 strSQL = "Update tbl_Review_Main SET Actual_Testing_Start_Date = #" & Tmp_Testing_Start_Date & "# Where CRAS_CAR_Id = " & temp_CRAS_CAR_Id & ""
 Debug.Print strSQL
 DoCmd.RunSQL strSQL


 strSQL = "UPDATE tbl_Review_Plan INNER JOIN q_Review_Plan1 ON (tbl_Review_Plan.Step_Order = q_Review_Plan1.Step_Order) AND (tbl_Review_Plan.CRAS_Car_id = q_Review_Plan1.CRAS_Car_id) SET tbl_Review_Plan.Planned_Date_On_Actual = [q_Review_Plan1]![Planned_Date_On_Actual]" _
 & " WHERE tbl_Review_Plan.CRAS_Car_id= " & temp_CRAS_CAR_Id & ";"
 Debug.Print strSQL
 DoCmd.RunSQL strSQL

 DoCmd.OpenForm "Frm_User_Dashboard", acNormal
 DoCmd.OpenForm "Frm_Reviews", acNormal, WhereCondition:="[CRAS_Car_id]= " & temp_CRAS_CAR_Id
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
>>  When I grab the code from the Immediate window and run it as a Query I do not get the lock violations. <<

that is because you are using the keyboard to interact;  equivalent is DoEvents in VBA

instead of using  DoCmd.RunSQL, better to use db.Execute -- which means you should use a database object variable
   dim db as dao.database
   set db = currentdb 'this will need to be released

Open in new window

after the forms are closed, refresh the tables so all processes can see what is latest, and DoEvents to make that happen now (which may need to be repeated after SQL runs for newly opened forms to see the latest -- first check if that is necessary). then run the SQL statement
   db.tabledefs.refresh
   DoEvents
   db.Execute strSQL

Open in new window

in the exit code:
   set db = nothing

Open in new window

although I have not included it ... error handlers are good to add. Here is a short video on error handling. There are 2 other videos in this series if you wish to know more.
basic error handling code for VBA (3:48)
http://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html

Author

Commented:
Hey Crystal,  thank you so much for the suggestions.  The first update statement worked great however the second is not updating.

The second statement is updating a table based on a query that uses the data updated in the first update statement.  Again, when I do it via a query it works.

I have tried adding the  db.tabledefs.refresh and   DoEvents before executing the SQL code but that does not seem to do the trick.

Is the second update statement not seeing the updated table from the first statement?

Thank you again for your help.

-Brian-
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
you're welcome
put the refresh and DoEvents statements before EACH running SQL statement that is not seeing what it should.

if there are quueries with problems, do db.querydefs.refresh too

the tables and queries should be closed -- or other things also need to happen

Author

Commented:
Hey Crystal,

My second Execute statement does not seem to be picking up the correct data even though I "Refresh" just before it.

Is there anything else I may be missing.  Running the Update manually does work.

Below is the current code

Thanks

Dim db As dao.Database
Dim strSQL As String, strSQL1 As String
Dim temp_CRAS_CAR_Id As Double
Dim Tmp_Testing_Start_Date As Date

Set db = CurrentDb 'this will need to be released

temp_CRAS_CAR_Id = Me.CRAS_Car_id
Tmp_Testing_Start_Date = Me.Actual_Testing_Start_Date

DoCmd.Close acForm, "Frm_Reviews", acSaveYes
'DoCmd.Close acForm, "Frm_User_Dashboard"

db.TableDefs.Refresh
DoEvents

strSQL = "Update tbl_Review_Main SET Actual_Testing_Start_Date = #" & Tmp_Testing_Start_Date & "# Where CRAS_CAR_Id = " & temp_CRAS_CAR_Id & ""
db.Execute strSQL

db.TableDefs.Refresh
db.QueryDefs.Refresh
DoEvents

strSQL1 = "UPDATE tbl_Review_Plan INNER JOIN q_Review_Plan1 ON (tbl_Review_Plan.Step_Order = q_Review_Plan1.Step_Order) AND (tbl_Review_Plan.CRAS_Car_id = q_Review_Plan1.CRAS_Car_id) SET tbl_Review_Plan.Planned_Date_On_Actual = [q_Review_Plan1]![Planned_Date_On_Actual]" _
& " WHERE tbl_Review_Plan.CRAS_Car_id= " & temp_CRAS_CAR_Id & ";"
db.Execute strSQL1
db.QueryDefs.Refresh
DoEvents
Debug.Print strSQL1

'DoCmd.OpenForm "Frm_User_Dashboard", acNormal

DoCmd.OpenForm "Frm_Reviews", acNormal, WhereCondition:="[CRAS_Car_id]= " & temp_CRAS_CAR_Id

Set db = Nothing
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
wait till form is closed before setting db -- and if the form data is not appearing, use DoEvents before setting db.  Perhaps also release it before OpenForm.  
 
instead of:
db.QueryDefs.Refresh
do
db.TableDefs.Refresh

>> Running the Update manually does work. <<
Sometimes you need more than one DoEvents

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial