Hello,

I have a set of four predefined queries I'm running in succession:

------------------------------------------------------
' Append and Delete Incoming
' Transaction Join on ID ?
' Lock remote db during transaction ?

Sub AppendAndDeleteIncoming()

CurrentDb.QueryDefs("AppendIncomingPurchaseRequest s").Execute
dbFailOnError
CurrentDb.QueryDefs("DeleteIncomingPurchaseRequest s").Execute
dbFailOnError
CurrentDb.QueryDefs("AppendIncomingLineItems").Exe cute dbFailOnError
CurrentDb.QueryDefs("DeleteIncomingLineItems").Exe cute dbFailOnError

End Sub
----------------------------------------------------

Having a look at this article:
http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

It seems I'd use 'wrkCurrent.BeginTrans' to lock the current
workspace, execute my transaction then 'wrkCurrent.CommitTrans' to
end.

So my code would be:

---------------------------------------------------

Set wrkCurrent = DBEngine.Workspaces(0)

wrkCurrent.BeginTrans

CurrentDb.QueryDefs("AppendIncomingPurchaseRequest s").Execute
dbFailOnError
CurrentDb.QueryDefs("DeleteIncomingPurchaseRequest s").Execute
dbFailOnError
CurrentDb.QueryDefs("AppendIncomingLineItems").Exe cute dbFailOnError
CurrentDb.QueryDefs("DeleteIncomingLineItems").Exe cute dbFailOnError

wrkCurrent.CommitTrans

---------------------------------------------------

However these queries are using linked tables from another 'Incoming'
database. When I BeginTrans does it also lock those tables?

Thanks!

I've decided not to use a transaction. I just append the new items to
the local tables then run a delete query with a join to the id in both
tables.

The incoming db is append only and the local db just receives these
new records and deletes these from the remote db.

I realize you said you weren't going to bother with the transaction, but for
the benefit of other readers, the issue is that you're still using
CurrentDb.

Try something like

Set wrkCurrent = DBEngine.Workspaces(0)

wrkCurrent.BeginTrans

With wrkCurrent(0)
.QueryDefs("AppendIncomingPurchaseRequests").Execu te dbFailOnError
.QueryDefs("DeleteIncomingPurchaseRequests").Execu te dbFailOnError
.QueryDefs("AppendIncomingLineItems").Execute dbFailOnError
.QueryDefs("DeleteIncomingLineItems").Execute dbFailOnError
End With

wrkCurrent.CommitTrans

wrote in message
news:2458dbfa-7784-442d-bc74-cec14f07c14f (AT) y31g2000prd (DOT) googlegroups.com...

Hello,

I have a set of four predefined queries I'm running in succession:

------------------------------------------------------
' Append and Delete Incoming
' Transaction Join on ID ?
' Lock remote db during transaction ?

Sub AppendAndDeleteIncoming()

CurrentDb.QueryDefs("AppendIncomingPurchaseRequest s").Execute
dbFailOnError
CurrentDb.QueryDefs("DeleteIncomingPurchaseRequest s").Execute
dbFailOnError
CurrentDb.QueryDefs("AppendIncomingLineItems").Exe cute dbFailOnError
CurrentDb.QueryDefs("DeleteIncomingLineItems").Exe cute dbFailOnError

End Sub
----------------------------------------------------

Having a look at this article:
http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

It seems I'd use 'wrkCurrent.BeginTrans' to lock the current
workspace, execute my transaction then 'wrkCurrent.CommitTrans' to
end.

So my code would be:

---------------------------------------------------

Set wrkCurrent = DBEngine.Workspaces(0)

wrkCurrent.BeginTrans

CurrentDb.QueryDefs("AppendIncomingPurchaseRequest s").Execute
dbFailOnError
CurrentDb.QueryDefs("DeleteIncomingPurchaseRequest s").Execute
dbFailOnError
CurrentDb.QueryDefs("AppendIncomingLineItems").Exe cute dbFailOnError
CurrentDb.QueryDefs("DeleteIncomingLineItems").Exe cute dbFailOnError

wrkCurrent.CommitTrans

---------------------------------------------------

However these queries are using linked tables from another 'Incoming'
database. When I BeginTrans does it also lock those tables?

Thanks!

"Douglas J Steele" <NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote in
news:ijf2gh$bps$1 (AT) news (DOT) eternal-september.org:

This is incomplete code (I know you were just editing what the
original poster gave you) in that it lacks an error handler. Any
time you use dbFailOnError, you MUST have an error handler.

I also don't understand why the code is not simply:

With wrkCurrent(0)
.Execute "AppendIncomingPurchaseRequests", dbFailOnError
.Execute "DeleteIncomingPurchaseRequests", dbFailOnError
.Execute "AppendIncomingLineItems", dbFailOnError
.Execute "DeleteIncomingLineItems", dbFailOnError
End With

I just don't get why people think they have to muck about with
QueryDefs to execute a saved DML query.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/