modules and public functions in multi user environment (1 Viewer)

[andyeastes]

Hello,

I have some modules that have public functions in them that I am calling from a few different forms. Works great until there are multi users and it starts messing up. By messing up I mean sometimes it wont go through, sometimes it goes through multiple times. What I think is happening is that another user is calling the function before the first instance is done running. This results in things getting marked processed or deleted before or after the actual time they were supposed to resulting in the skewed data.

If anyone has encountered this or has any suggestions they would be GREATLY appreciated. I have thought about trying to tell it to wait if it is already running like a queue or messaging service and have scoured the internet to find a solution to this problem to no avail.

Thanks in advance!

 

[bob fitz]

I the db split into Frontend and Backend.

 

[andyeastes]

Yes it is split, that and making public functions within modules (instead of using macros) reduced the number of errors happening but did not delete them.

 

[VilaRestal]

Are these functions making changes to tables? I assume they are.

Could you give us an example of a function that falls over sometimes?

(It's going to be a case of improving error handling and maybe adjusting record locks in dynamic record sets and/or using transactions.)

 

[VilaRestal]

An example of an approach:

Let's say there is a table called Settings with Fields (SettingName, SettingValue)

And there's a sub that reads a particular setting, does some processing based on it and then maybe changes the setting:

Sub ChangeSetting1()
    Dim SettingValue As String
    SettingValue = DLookup("SettingValue","Settings","SettingName = 'Setting1'")
    'Does some complex processing that takes time
    If SettingValue = "Not a good value" Then
        CurrentDb.Execute "UPDATE Settings SET SettingValue = 'A good value' WHERE SettingName = 'Setting1'"
    End If
End Sub

One way to cope with multiple users running the same Sub at the same time would be to add a YesNo BeingEdited field to the table and change the code to:

Sub ChangeSetting1()
    Dim LoopCounter As Integer
    'Wait for setting to be unlocked
    Do
        If Not DLookup("BeingEdited","Settings","SettingName = 'Setting1'") Then Exit Do
        If LoopCounter > 100 Then Exit Do 'Setting1 seems to be left locked so proceed anyway
        LoopCounter = LoopCounter + 1
    Loop
    'Lock the setting:
    CurrentDb.Execute "UPDATE Settings SET BeingEdited = -1 WHERE SettingName = 'Setting1'"
    Dim SettingValue As String
    SettingValue = DLookup("SettingValue","Settings","SettingName = 'Setting1'")
    'Does some complex processing that takes time
    If SettingValue = "Not a good value" Then
        CurrentDb.Execute "UPDATE Settings SET SettingValue = 'A good value' WHERE SettingName = 'Setting1'"
    End If
    'Unlock the setting:
    CurrentDb.Execute "UPDATE Settings SET BeingEdited = 0 WHERE SettingName = 'Setting1'"
End Sub

I hope that's of use.

 

[ChrisO]

The database may be split but does each user have their own front-end?

 

[Pat Hartman]

Although the FE should always be split and EVERY user should have his own copy, two users opening the same copy of the FE will not conflict with each other in this way. Each user opens the FE on his own computer and therefore in his OWN memory space. There is no way for PC1 to impact something in PC2's memory. The conflict may be caused by temp tables that are not properly segregated.

 

[andyeastes]

Thanks for all the responses!

So it is split but they are all opening the same copy from the network, it sounds like I should have a copy on each users local machine?

Also, fro your responses I am thinking I should not have the first temp table that the data is going to as a linked table but as a local table which would prevent user2 calling the function of duplicating that data?

Pat Hartman: I am not sure what you mean by having the temp tables properly segregated?

VilaRestal: Yes, the functions are making changes to multiple tables. I think locking the function is what I have been trying to do with no luck, I will try your code and let you know how it goes. So to make sure I understand I should have that being edited filed in each table that is being modified by the code? Below is an example of a function that is not always working properly, I think this is what you were asking for:

Public Function CompleteTransactionsMOD()
On Error GoTo CompleteTransactionsMOD_Err

DoCmd.SetWarnings False
DoCmd.OpenQuery "UpdateInOutTrantblQRY", acViewNormal, acEdit
DoCmd.OpenQuery "UpdateTranTypeQRY", acViewNormal, acEdit
DoCmd.OpenQuery "PullToShipTransHandlingQRY", acViewNormal, acEdit
DoCmd.OpenQuery "TransreadyInDupLocQRY", acViewNormal, acEdit
DoCmd.OpenQuery "TransreadyOutDupLocQRY", acViewNormal, acEdit
DoCmd.OpenQuery "AppendNewLocToInvInQry", acViewNormal, acEdit
DoCmd.OpenQuery "UpdateNewLocToInvNegQRY", acViewNormal, acEdit
DoCmd.OpenQuery "TrancomplastQRY", acViewNormal, acEdit
DoCmd.OpenQuery "InvLocFindNegQRY", acViewNormal, acEdit
DoCmd.OpenQuery "DelZeroQInvLocQRY", acViewNormal, acEdit
DoCmd.SetWarnings True


CompleteTransactionsMOD_Exit:
Exit Function

CompleteTransactionsMOD_Err:
MsgBox Error$
Resume CompleteTransactionsMOD_Exit

End Function

Thanks again everyone!

 

[andyeastes]

UPDATE:

So I made the table that the form connects to a local table and I also took the front end and put it on each local machine instead of opening it through the network. This seems to help but it is still happening some.

 

[VilaRestal]

Yeah and that looks like it's going to be tricky. You're going to need to know all the records those queries will affect before you run them to use a custom record locking system.

Definitely make temp tables local to the front end unless they need to be collaborative with other users.

If any of those queries aren't just affecting temp tables then you're going to have to look at:
a) locking them with your own system like my example
b) locking them with Access's system (pessimistic locks on edited records) but that may not be enough - you may need to actually be locking a whole table.
c) use transactions and rollback if it encounters an error and maybe try again
d) a combination of the above

I think c is probably the easiest and best way to go.

 

[andyeastes]

So I have the now local table that data from form goes too, then it goes into a transaction table and then is processed from there. That transactions table is not local, it is a linked table because that is the table I use for history. Is that what you are talking about transactions. So, I make a check that says Oh that created a duplicate, so remove them both and run the transaction again?

 

[VilaRestal]

In effect yes.

You'd need to execute QueryDefs with the dbFailOnError option (rather than DoCmd.OpenQueries) and capture errors (try again perhaps). As I understand it, if a DoCmd.OpenQuery encounters an error that means it can't update one or more records it won't raise a runtime error. Executing a QueryDef with dbFailOnError will both raise an error and rollback any changes it made if it finds it can't update all the records it wants to.

You can wrap the whole thing (multiple querydef executes and other executes and recordset changes) in a transaction by starting with DBEngine.BeginTrans

If an error occurs then DBEngine.Rollback (and maybe try again from the start). If no errors occur then DBEngine.CommitTrans

 

[Pat Hartman]

Pat Hartman: I am not sure what you mean by having the temp tables properly segregated?

By that I ment to either move them to the individual FEs or include a column that identifies each user so that you can work with UserA's set of records separately from UserB's.

 

[andyeastes]

Hello,
So I was reading up on commit trans, rollback, and begintrans and found this:
You can't nest transactions if you are accessing ODBC data sources through the Microsoft Access database engine.

Does this mean I cant use this method since some of my affected tables are linked to sql server through odbc connections?

Also, do you think I should use this method or the one described earlier with the being edited column and looping until it is done?

Thanks for all the help and advice!

 

[andyeastes]

Pat Hartman,

I do have the first table the data goes through as a local table now with the FE on their local machines, this accomplishes what you mean right?

Also, I do have the user specified and they have to login so I guess I could say for each instance of the db within this function only process where username is equal to who is logged in on that instance?

 

[VilaRestal]

Actually, I think you've misunderstood what a nested transaction is (and I followed suit).

A nested transaction is:

DBEngine.BeginTrans
DBEngine.BeginTrans

DBEngine.CommitTrans
DBEngine.CommitTrans

(one inside the other)

You won't need to be doing this. Just one transaction with many executes inside it.

 

[andyeastes]

OK great you are right,, so I am going to try this method, so basically, I do an if statement, If there is an error I use rollback else I commit?

 

[VilaRestal]

Yep but you need to change the way you execute the queries. DoCmd.OpenQuery won't work because it won't raise an error if it fails to update some records (I'm 99% sure). You'd have to make them into QueryDefs and Execute them with the dbFailOnError option.

Something like:

Public Function CompleteTransactionsMOD()
DBEngine.BeginTrans
On Error GoTo CompleteTransactionsMOD_Err

With CurrentDb
    .QueryDefs("UpdateInOutTrantblQRY").Execute dbFailOnError
    .QueryDefs("UpdateTranTypeQRY").Execute dbFailOnError
    .QueryDefs("PullToShipTransHandlingQRY").Execute dbFailOnError
    .QueryDefs("TransreadyInDupLocQRY").Execute dbFailOnError
    .QueryDefs("TransreadyOutDupLocQRY").Execute dbFailOnError
    .QueryDefs("AppendNewLocToInvInQry").Execute dbFailOnError
    .QueryDefs("UpdateNewLocToInvNegQRY").Execute dbFailOnError
    .QueryDefs("TrancomplastQRY").Execute dbFailOnError
    .QueryDefs("InvLocFindNegQRY").Execute dbFailOnError
    .QueryDefs("DelZeroQInvLocQRY").Execute dbFailOnError
End With
DBEngine.CommitTrans

CompleteTransactionsMOD_Exit:
Exit Function

CompleteTransactionsMOD_Err:
DBEngine.Rollback
MsgBox Error$
Resume CompleteTransactionsMOD_Exit

End Function

 

[VilaRestal]

Maybe you could put that in a loop and exit the loop after CommitTrans (if no error) or if a loopcounter goes too high (reporting an error message then):

Public Function CompleteTransactionsMOD()
    Dim bSuccess As Boolean
    Dim iCounter As Integer
    Do While bSuccess = False And iCounter < 10
        bSuccess = CompleteTransactionsMOD_Loop()
        iCounter = iCounter + 1
    Loop
    If bSuccess Then
        MsgBox "Transactions completed successfully"
    Else
        MsgBox "Transactions failed to complete after repeated attempts"
    End If
End Function

Private Function CompleteTransactionsMOD_Loop() As Boolean
    DBEngine.BeginTrans
    On Error GoTo CompleteTransactionsMOD_Loop_Err

    With CurrentDb
        .QueryDefs("UpdateInOutTrantblQRY").Execute dbFailOnError
        .QueryDefs("UpdateTranTypeQRY").Execute dbFailOnError
        .QueryDefs("PullToShipTransHandlingQRY").Execute dbFailOnError
        .QueryDefs("TransreadyInDupLocQRY").Execute dbFailOnError
        .QueryDefs("TransreadyOutDupLocQRY").Execute dbFailOnError
        .QueryDefs("AppendNewLocToInvInQry").Execute dbFailOnError
        .QueryDefs("UpdateNewLocToInvNegQRY").Execute dbFailOnError
        .QueryDefs("TrancomplastQRY").Execute dbFailOnError
        .QueryDefs("InvLocFindNegQRY").Execute dbFailOnError
        .QueryDefs("DelZeroQInvLocQRY").Execute dbFailOnError
    End With
    DBEngine.CommitTrans
    CompleteTransactionsMOD_Loop = True

CompleteTransactionsMOD_Loop_Exit:
    Exit Function

CompleteTransactionsMOD_Loop_Err:
    DBEngine.Rollback
    CompleteTransactionsMOD_Loop = False
    Resume CompleteTransactionsMOD_Loop_Exit

End Function

Whether that's appropriate only you can tell. It depends on what all those queries do.

 

[andyeastes]

Alright, tried this and for this part of code got error: compile error, user defined type not defined

Dim qdf As QueryDef

Any thoughts.

I looked it up and found that it says to fix chekc your references and make sure activex library is checked and it is on mine?

Also, you sort of lost me on your loop comment? You mean put that whole piece of code in a loop and end loop if it commits but if rollback then keep trying, if tries to many times it just fails?

Thanks again!