Use Transaction Property

[JimP]

Can someone provide code to loop through the queries collection, and change
the property setting for "Use Transactions" from "Yes" to "No"?

...applies to action queries only.

 

[Piet Linden]

Can someone provide code to loop through the queries collection, and change
the property setting for "Use Transactions" from "Yes" to "No"?

..applies to action queries only.

something along these lines should work...

Public Sub SetActionQueriesToUseTransactions()
Dim qdf As DAO.QueryDef

For Each qdf In DBEngine(0)(0).QueryDefs
Select Case qdf.Type
Case dbQUpdate, dbQDelete, dbQAppend
Set qdf.Properties("UseTransaction") = True
Case Else
'do nothing
End Select
Next qdf

End Sub

 

[JimP]

Thanks. I'm getting an error on the following statement though, "Invalid Use
of Property". Does the property need to be appended to the properties
collection?

Set qdf.Properties("UseTransaction") = True

Can someone provide code to loop through the queries collection, and
change
the property setting for "Use Transactions" from "Yes" to "No"?

..applies to action queries only.

something along these lines should work...

Public Sub SetActionQueriesToUseTransactions()
Dim qdf As DAO.QueryDef

For Each qdf In DBEngine(0)(0).QueryDefs
Select Case qdf.Type
Case dbQUpdate, dbQDelete, dbQAppend
Set qdf.Properties("UseTransaction") = True
Case Else
'do nothing
End Select
Next qdf

End Sub

 

[vanderghast]

Many properties do not exist by default. You can always check with code, or,
if you are in a hurry, inside VBE, make a break point at the said line,
from the menu, under View, have the Locals window visible, then, run the
code. When it stops at the debug point, examine the Locals: even dynamically
created stuff is there and yours to browse. You should be able to find there
your qdf variable, and its Properties collection, with all what it contains
at the moment. (I don't really work with QueryDefs Properties, so I can't
say, in advance, without doing exactly what I described, about this
particular property, but I hope this technique can help you for this case,
if not for other cases).


Vanderghast, Access MVP

 

[Dirk Goldgar]

Thanks. I'm getting an error on the following statement though, "Invalid
Use of Property". Does the property need to be appended to the properties
collection?

Set qdf.Properties("UseTransaction") = True

I don't think you should have the keyword "Set" there, since you're not
setting an object reference. Try this:

qdf.Properties("UseTransaction") = True