modules and public functions in multi user environment

Bizarre

But I edited the post to remove the need for the Dim qdf line

Try it again with what's there
 
It just runs forever. It does seem to make since, could it somehow be looping non stop?
 
Check the non-loop version works first.

If all those queries take time to execute then looping ten times could take a while but i presume it should succeed on the first loop.

There's no way it would loop forever.

I'd be stepping through it anyway first time through but definitely if it's behaving oddly.

Here's a version for debugging it (reduced loops and prints loop number and errors to immediate window):

Code:
Public Function CompleteTransactionsMOD()
    Dim bSuccess As Boolean
    Dim iCounter As Integer
    Do While bSuccess = False And iCounter < 3
        Debug.Print "Loop " & iCounter
        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
    Debug.Print Err.Description
    CompleteTransactionsMOD_Loop = False
    Resume CompleteTransactionsMOD_Loop_Exit

End Function
 
You are right, it was not the loop, tried the edited first one without the loop and same thing just went forever. It really is just calling the queries in a different manner if I understand correctly so why would it take any longer than the previous way I had it.
 
Ah, sorry andy, just discovered that:

Must be something about executing local queries that affect ODBC linked tables.

I think you'll have to do it by opening an ADO connection and running the SQL on that. We can possibly read the SQL from those queries but the syntax might need changing (especially if dates are used).

Sorry it's not as simple as I hoped.

Are you familiar with ADO and writing SQL for SQL Server proper?
 
I somewhat know SQL and am not real familiar with ADO but I looked it up and I would want to use the command execute method correct?

Can I not just call the queries regularly but still use the code for transactions?
 
yes or just plain connection.Execute

The problem is the DoCmd.OpenQuery won't raise a runtime error if a part of the update fails. It will only raise in error if it can't find the query. So you'll never know when to rollback and when to commit.

Even though this is going to be significantly more complicated I still think it's the simplest and most reliable way to make your function stable in a multiuser environment.

One thought that occurs is: could you build those queries into stored procedures? you should if you can.

If any of them are updating a server table according to what they find in a local table then they would have to be done with recordsets I think. The Server won't be aware of your local tables.

Any queries that only affect local tables should work fine with currentdb.querydefs().execute. You will be able to nest (run side by side) an ado transaction and those local transaction and only commit them both if no errors are found in either and rollback them both otherwise.
 
Sorry took so long to get back, so I have it almost working but it keeps giving object required error - is this because I need to go to tools and make sure the DAO library is checked? If I try that it just says that there is an error and it could not load dll.


Public Function CompleteTransactionsMOD()
Dim bSuccess As Boolean
Dim iCounter As Integer
Do While bSuccess = False And iCounter < 3
Debug.Print "Loop " & iCounter
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
Dim Execute As Object
On Error GoTo CompleteTransactionsMOD_Loop_Err

With CurrentDb
db.Execute "UpdateInOutTrantblQRY"
db.Execute "UpdateTranTypeQRY"
db.Execute "PullToShipTransHandlingQRY"
db.Execute "TransreadyInDupLocQRY"
db.Execute "TransreadyOutDupLocQRY"
db.Execute "AppendNewLocToInvInQry"
db.Execute "UpdateNewLocToInvNegQRY"
db.Execute "TrancomplastQRY"
db.Execute "InvLocFindNegQRY"
db.Execute "DelZeroQInvLocQRY"

End With
DBEngine.CommitTrans
CompleteTransactionsMOD_Loop = True

CompleteTransactionsMOD_Loop_Exit:
Exit Function

CompleteTransactionsMOD_Loop_Err:
DBEngine.Rollback
Debug.Print Err.Description
CompleteTransactionsMOD_Loop = False
Resume CompleteTransactionsMOD_Loop_Exit


End Function
 
Dim Execute As Object
?

And get rid of the db's within the With CurrentDb. They're not defined and not needed. (For some reason a lot of people seem to have an obsession with replacing one perfectly good object variable - CurrentDb, kindly provided for us ready to use by Access - with another - db. I've never understood that.) The whole point of the With statement is to say anything starting with a dot (.) or a bang (!) is applied to this (the object after the With - in this case CurrentDb).

I expect you've already got DAO in another form referenced so you can't reference another version of it.

However, if that works I'll be surprised because I expect Access will have problems creating transactions on odbc linked tables.
 
Yes you are right again it does not run the queries. So the only way for this to work is to make sql stored procedures. How do I call them from my VBA?
 
No, not the only way. You can do the queries in code through an ADODB connection.

It's going to be a bit complicated. Do you know ADODB? Opening connections with connection string?

Someone's bound to have written a function that can 'copy' the ODBC connection into an ADODB one (I'll have a look for that too). Most SQL will convert directly from the Access queries but some bits will need changing (date handling in particular).

So, for the queries that affect server tables: you'll be copying the SQL from the queries into code, changing it a bit (to T-SQL syntax rather than Access SQL), and executing that on the ADODB connection.

You won't be able to do queries that reference (select or update) both local tables and server tables in one. You'll have to split those into separate queries or use recordsets.

First things first: reference the Microsoft ActiveX Data Objects 2.7 (ADO)

Then you're going to want to open an ADO connection to the same server as the ODBC.

Then you'll start a transaction on that connection and a separate DBEngine transaction for the local tables.

Then execute all the SQL strings (copied from the queries) on that connection and maybe some of the queries that are only doing things locally can stay as they are.

Then commit both transactions unless there's an error in which case rollback them both.

Not too hard but not easy either.
 
ALL the code must be kept in the user's front end.

I threw that in just incase something different was happening.
 
I expect some of the queries could be moved to the back end but not all as some affect local tables. The important point (and whole purpose of this) is to create two transactions - one for the server and one for the local tables - in parallel that can be rolled-back together if either fails. The server transaction will be an ADO one, the front end transaction will be a DBEngine one.

Any query that affects both FE and BE tables will have to be done as recordset loops, also within the transactions and could/would be rolled back with everything else.

(And, I have to say, this is a good example of the downside of mixing front end tables with ODBC linked tables. Transactions become tricky.)
 
Last edited:

Users who are viewing this thread

Back
Top Bottom