By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,451 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

Timing issues with Access & SQL Server again

P: 3
I'm using Access 2002 as a front end to a SQL Server 2000 database. I Update a status field in a SQL Server table using a parameter based stored proc. Works great. Then in the same sub, I open a popup form to view the record I have just updated.

If I open the Popup Form automatically in code, the status field is not updated. If I open it from a button on the form, it works great.

I have tried every trick I know to force the SQL Server table to be updated before I open the Popup Form, but nothing works. I have tried (1) Doevents, (2) Requerying the Popup Form, (3) Requerying the updated recordset (4) Opening the updated recordset in code.

Any other Suggestions?
Jan 3 '07 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I'm using Access 2002 as a front end to a SQL Server 2000 database. I Update a status field in a SQL Server table using a parameter based stored proc. Works great. Then in the same sub, I open a popup form to view the record I have just updated.

If I open the Popup Form automatically in code, the status field is not updated. If I open it from a button on the form, it works great.

I have tried every trick I know to force the SQL Server table to be updated before I open the Popup Form, but nothing works. I have tried (1) Doevents, (2) Requerying the Popup Form, (3) Requerying the updated recordset (4) Opening the updated recordset in code.

Any other Suggestions?
Don't know the solution offhand Rocky will point some experts in your direction who might.

Mary
Jan 4 '07 #2

NeoPa
Expert Mod 15k+
P: 31,186
As a last resort (because you've already considered most answers I could think of - especially the DoEvents one) can you try using the OnTimer routine.
Open the popup form in the Timer event and trigger the event after, say, a quarter of a second.
A bit of a clumsy work-around, but should reproduce the manual method which is working quite closely.
HTH.
Jan 4 '07 #3

100+
P: 1,646
I'm using Access 2002 as a front end to a SQL Server 2000 database. I Update a status field in a SQL Server table using a parameter based stored proc. Works great. Then in the same sub, I open a popup form to view the record I have just updated.

If I open the Popup Form automatically in code, the status field is not updated. If I open it from a button on the form, it works great.

I have tried every trick I know to force the SQL Server table to be updated before I open the Popup Form, but nothing works. I have tried (1) Doevents, (2) Requerying the Popup Form, (3) Requerying the updated recordset (4) Opening the updated recordset in code.

Any other Suggestions?
Is it possible to see the offending snippet of code?
Jan 4 '07 #4

missinglinq
Expert 2.5K+
P: 3,532
I know absolutely nothing about writing to a SQL Server backend, but by your description of the problem, it sounds like it's not writing the update to the backend until the focus moves off of wherever it is to the button to open the popup. You might try in VBA moving the focus elsewhere then opening the form from VBA. Won't take but a second to try!
Jan 4 '07 #5

Andrew Thackray
P: 76
The issue probably is with the type of cursor you opened in the recordset.

I've had this problem myself & found if I replace the recordset.update command with the Recordset.updatebatch coimmand the database is updated immediately. Otherwise the command is not committed unti lyou leave the procedure or close the connection
Jan 4 '07 #6

100+
P: 1,646
The issue probably is with the type of cursor you opened in the recordset.

I've had this problem myself & found if I replace the recordset.update command with the Recordset.updatebatch coimmand the database is updated immediately. Otherwise the command is not committed unti lyou leave the procedure or close the connection
Far too many answers without seeing code.
Don't know if it is dao, ado, odbc, recordset, command object, connection object, server side cursor blah blah blah.
Post the code
Jan 4 '07 #7

NeoPa
Expert Mod 15k+
P: 31,186
Long time no see Andrew.
This sounds like the proper answer to me.
You won't need the timer trick if this works.
Jan 4 '07 #8

P: 3
Far too many answers without seeing code.
Don't know if it is dao, ado, odbc, recordset, command object, connection object, server side cursor blah blah blah.
Post the code
Here's the code. Remarked out lines are some of the code I have tried to use to resolve this issue.

Expand|Select|Wrap|Line Numbers
  1.    '*******
  2.    ' StoredProc set PositionStatus = Previous and PositionEndDate = @PositionEndDate in EmployeePosition
  3.    ' WHERE PositionStatus = Current and EmployeeID = @EmployeeID
  4.  
  5.    cnn.BeginTrans
  6.    Set cmd = MakeStoredProc("sp_EmployeePosition_UpdatePositionEndDateNStatus_ForEmployeeID")
  7.    Set prmEmployeeID = cmd.CreateParameter("EmployeeID", adInteger, adParamInput, , lngEmployeeID)
  8.    Set prmPositionEndDate = cmd.CreateParameter("PositionEndDate", adDate, adParamInput, , datTermination)
  9.    Set prmModName = cmd.CreateParameter("ModName", adVarChar, adParamInput, 6, gbStrUserCode)
  10.    cmd.Parameters.Append prmEmployeeID
  11.    cmd.Parameters.Append prmPositionEndDate
  12.    cmd.Parameters.Append prmModName
  13.    cmd.Execute
  14.    cnn.CommitTrans
  15.    Set cmd = Nothing
  16.    DoEvents
  17.  
  18. '   Set qryd1 = gbDbsUser.QueryDefs("EmployeePosition_Update1PositionEndDate2Status_ForEmplID_UpdQry")
  19. '   qryd1.Parameters("prmEmployeeID") = lngEmployeeID
  20. '   qryd1.Parameters("prmPositionEndDate") = datTermination
  21. '   qryd1.Parameters("prmModName") = gbStrUserCode
  22. '   qryd1.Execute
  23. '   DoEvents
  24. '   qryd1.Close: Set qryd1 = Nothing
  25.  
  26. '   Set tdfEmployeePosition = gbDbsUser.TableDefs("EmployeePosition")
  27. '   var = RefreshMyLink(tdfEmployeePosition, tdfEmployeePosition.Connect)
  28.  
  29. '   Set cnn = gbCnnDataDB
  30. '   DoEvents
  31.  
  32.    '-- Open VacantPosition_Popup
  33.    MyW = "EmployeeID = " & lngEmployeeID
  34.    var = OpenMyForm("EmployeePosition_TerminateEmployee_Popup", "Popup", "Employee_Entry", MyW)
  35.  
  36.    '-- Confirm End
  37.    myM = "CURRENT EMPLOYEE POSITIONS HAVE BEEN SET TO PREVIOUS. Open the Employee Position Terminate " _
  38.          & "Popup to set the Position to Vacant. "
  39.    Alert
Jan 4 '07 #9

100+
P: 1,646
Here's the code. Remarked out lines are some of the code I have tried to use to resolve this issue.
As I suspected. There is no recordset object. Now would you please post the code for the declarations of the cnn and cmd objects. Thanks
Jan 4 '07 #10

P: 3
As I suspected. There is no recordset object. Now would you please post the code for the declarations of the cnn and cmd objects. Thanks
Thanks for your help. I found the problem. There was an error in my OpenMyForm Function.
Jan 5 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks for your help. I found the problem. There was an error in my OpenMyForm Function.
Glad you got it worked out.

Mary
Jan 5 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.