Access 2000 mdb VBA and Concurrency

[Guest]

Hi - Please help
I use Access 2000 mdb with VBA learning from the help files, I started a few
months ago for office applications located on a server.
All went well until multiple users updated the same "production" tables.
The application does not hang, the form is fully operational,
but occasionally no update "INSERT" of the data takes place
and the event code for the save button does not complete.
This does not seem to be a network problem. I did change the code from the
DoCmd.RunSql and used connection.execute with .BeginTrans .CommitTrans and
had the same problem.

I would appreciate some advice in the interim, and for the long term can
someone recommend a intermediate to advanced book that I can purchase.

Here some of the code…

Dim vsSql As String 'Add Session header.
vsSql = " " & _
"INSERT INTO [Production Operation Session Header] " & _
"( " & _
" PrdSsHdDocRef, " & _
" RspCode , " & _
" ShiftCode , " & _
" PressCode , " & _
" PrdSCode , " & _
" OrdDItem , " & _
" PrdSsHdNotes , " & _
" PrdSsHdStart , " & _
" PrdSsHdEnd " & _
" ) " & _
"VALUES " & _
" ( " & _
" '" & txtDocNum.Value & _
"','" & cbbOpr.Value & _
"','" & cbbShift.Value & _
"','" & cbbWorkCenter.Value & _
"','" & cbbFormat.Value & _
"','" & cbbJob.Value & _
"','" & txtNotes.Value & _
"',#" & txtHdStart.Value & _
"#,#" & txtHdEnd.Value & _
"#);"
DoCmd.RunSQL (vsSql)



Thanking you

 

[Guest]

Have you split your application so the database is in one mdb and all the
code in another? The users each get their own copy of the code mdb with the
database tables accessed by linking. For books try Alison Balter's Mastering
Microsoft Office Access 2003 ISBN 0-672-32550-0 and Access 2003 Power
Programming with VBA by Taylor and Andersen ISBN0-7645-2588-3. The first has
both DAO and ADO in detail so it is useful just for the DAO reference. The
second has more on ADO.

 

[Guest]

I'm not sure what you mean by "the event code for the save button does not
complete" (i.e., Are the users getting any error messages when they click the
save button?)

 

[Guest]

Thank you for your reply... I did browse the newsgroup yesterday and Access
2000 Developer's handbook was a recommendation, I unfortunately already
sourced it this morning, I will consider your book recommendations as well.

No, the DB is not split, a max of 5 users update, users have Access on
desktop with a shortcut to a single BD on server.

Kind regards,
Robert

Have you split your application so the database is in one mdb and all the
code in another? The users each get their own copy of the code mdb with the
database tables accessed by linking. For books try Alison Balter's Mastering
Microsoft Office Access 2003 ISBN 0-672-32550-0 and Access 2003 Power
Programming with VBA by Taylor and Andersen ISBN0-7645-2588-3. The first has
both DAO and ADO in detail so it is useful just for the DAO reference. The
second has more on ADO.
--
Pictou

Hi - Please help
I use Access 2000 mdb with VBA learning from the help files, I started a few
months ago for office applications located on a server.
All went well until multiple users updated the same "production" tables.
The application does not hang, the form is fully operational,
but occasionally no update "INSERT" of the data takes place
and the event code for the save button does not complete.
This does not seem to be a network problem. I did change the code from the
DoCmd.RunSql and used connection.execute with .BeginTrans .CommitTrans and
had the same problem.

I would appreciate some advice in the interim, and for the long term can
someone recommend a intermediate to advanced book that I can purchase.

Here some of the code…

Dim vsSql As String 'Add Session header.
vsSql = " " & _
"INSERT INTO [Production Operation Session Header] " & _
"( " & _
" PrdSsHdDocRef, " & _
" RspCode , " & _
" ShiftCode , " & _
" PressCode , " & _
" PrdSCode , " & _
" OrdDItem , " & _
" PrdSsHdNotes , " & _
" PrdSsHdStart , " & _
" PrdSsHdEnd " & _
" ) " & _
"VALUES " & _
" ( " & _
" '" & txtDocNum.Value & _
"','" & cbbOpr.Value & _
"','" & cbbShift.Value & _
"','" & cbbWorkCenter.Value & _
"','" & cbbFormat.Value & _
"','" & cbbJob.Value & _
"','" & txtNotes.Value & _
"',#" & txtHdStart.Value & _
"#,#" & txtHdEnd.Value & _
"#);"
DoCmd.RunSQL (vsSql)



Thanking you

 

[Guest]

Thank you for your interest

No error messages generated.

The Event - cmdSaveLines_Click()

The form is unbound, combo boxes have SQL source, textboxes accept user data.
The cmdSaveLines button,
1) checks for user capture errors
2) Procedure call - Read and write a transaction number
3) Procedure call - Inserts 1 row of master data.
4) Loops a Procedure call - Updates Detailed data to 2 tables - max 16 Rows
5) MsgBox - Save complete
6) Close form
7) Open form initialized

The code occasionally stops at Step 3, it completes step 2.
No msgbox appears, the form is not closed or reopened.
I then print the form, and recapture the exact same data and it runs.

Kind Regards
Robert

I'm not sure what you mean by "the event code for the save button does not
complete" (i.e., Are the users getting any error messages when they click the
save button?)

Hi - Please help
I use Access 2000 mdb with VBA learning from the help files, I started a few
months ago for office applications located on a server.
All went well until multiple users updated the same "production" tables.
The application does not hang, the form is fully operational,
but occasionally no update "INSERT" of the data takes place
and the event code for the save button does not complete.
This does not seem to be a network problem. I did change the code from the
DoCmd.RunSql and used connection.execute with .BeginTrans .CommitTrans and
had the same problem.

I would appreciate some advice in the interim, and for the long term can
someone recommend a intermediate to advanced book that I can purchase.

Here some of the code…

Dim vsSql As String 'Add Session header.
vsSql = " " & _
"INSERT INTO [Production Operation Session Header] " & _
"( " & _
" PrdSsHdDocRef, " & _
" RspCode , " & _
" ShiftCode , " & _
" PressCode , " & _
" PrdSCode , " & _
" OrdDItem , " & _
" PrdSsHdNotes , " & _
" PrdSsHdStart , " & _
" PrdSsHdEnd " & _
" ) " & _
"VALUES " & _
" ( " & _
" '" & txtDocNum.Value & _
"','" & cbbOpr.Value & _
"','" & cbbShift.Value & _
"','" & cbbWorkCenter.Value & _
"','" & cbbFormat.Value & _
"','" & cbbJob.Value & _
"','" & txtNotes.Value & _
"',#" & txtHdStart.Value & _
"#,#" & txtHdEnd.Value & _
"#);"
DoCmd.RunSQL (vsSql)



Thanking you

 

[Guest]

I appologize, The problem is not concurrency, a user typed apostrophe in a
textbox, so an SQL update derived from data in the textbox corrupted. used
event Keypressed and then cancel. Thankyou