Re: Running Query classes inside transactions

I'm assuming that 'thisquery' is a DAO QueryDef?

I don't have Access 97 installed, but I am reasonably certain that this
code, which I tested under Access 2003, would behave identically under
Access 97. I tested in a modified copy of Northwind, from which I'd removed
the relationship between Customers and Orders (otherwise you'd get an RI
error when you attempted to delete the Customer record) ...

Public Sub TestSub2()

Dim wsp As DAO.Workspace
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set wsp = DBEngine.Workspaces(0)
wsp.BeginTrans
Set db = wsp.Databases(0)
On Error Resume Next
db.QueryDefs.Delete "TestQuery"
On Error GoTo 0
Set qdf = db.CreateQueryDef("TestQuery", "DELETE * FROM Customers WHERE
CustomerID = 'ALFKI'")
qdf.Execute dbFailOnError
Set rst = db.OpenRecordset("SELECT Count(*) AS TheCount FROM Customers
WHERE CustomerID = 'ALFKI'")
Debug.Print "Within transaction ..."
Debug.Print rst.Fields("TheCount")
rst.Close
wsp.Rollback
Set rst = db.OpenRecordset("SELECT Count(*) AS TheCount FROM Customers
WHERE CustomerID = 'ALFKI'")
Debug.Print "After rollback ..."
Debug.Print rst.Fields("TheCount")
rst.Close

End Sub

Result in the Immediate window ...

testsub2
Within transaction ...
0
After rollback ...
1

--
Brendan Reynolds (MVP)


"Jonathan Scott via AccessMonster.com" <forum@xxxxxxxxxxxxxxxxxxxxxxxx>
wrote in message news:4FD38938C5400@xxxxxxxxxxxxxxxxxxxx
> Is it not possible in Access97? I've tried the following ways to execute
> queries:
>
> docmd.openquery thisquery
> dbengine(0)(0).execute thisquery
> dbengine(0)(0).execute thisquery.name
> dbengine(0)(0).execute thisquery.sql
> currentdb.execute <same as the three above>
> thisquery.execute
>
> The only one that successfully executes without complaining is the first.
> But
> this one ignores my rollback. Do I have to spell out my queries as strings
> to
> be able to execute them inside of a transaction?
>
> Also, I am curious, does Access 2003 allow DDL inside of transactions? I
> find
> it difficult to work with Access97 because it does not allow DDL inside of
> transactions.
>
> TIA,
> Jonathan Scott
>
> --
> Message posted via http://www.accessmonster.com


.

Relevant Pages

  • Re: Stamps.com Warning
    ... Customers using PayPal MasterCard Debit Cards are seeing what they ... authorization to bill but does not bill the actual transaction. ... Stamps.com Postage Group ... requested and the duplicate request stamps.com sends is withdrawn from ...
    (alt.marketing.online.ebay)
  • Re: Stamps.com Warning
    ... Customers using PayPal MasterCard Debit Cards are seeing what they ... authorization to bill but does not bill the actual transaction. ... Stamps.com Postage Group ... requested and the duplicate request stamps.com sends is withdrawn from ...
    (alt.marketing.online.ebay)
  • Re: Multithreaded Database access with C# on an Sql2005 and TransactionScope class (Bug or did I som
    ... der zuerst geschlossen werden muss.\r\n bei ... Console.WriteLine("About to complete the worker3 thread's transaction ... The real Programm should calculate some values for Customers. ... Threads with differend Customer-Ids and do data reading and calculating of ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Am I going to loose all my £480? Advice requested.
    ... We have customers use our service for years and then charge back everything ... Its commonly known as card holder fraud. ... We pursue the customers who do this via debt collectors. ... Just say you don't recognise the transaction and they cannot hold you to it ...
    (uk.people.consumers.ebay)
  • Re: Correct me if Im wrong ...
    ... "My plan will also ensure that Borland's customers and employees are made ... For that reason I would favor a transaction for Delphi that ... separation. ...
    (borland.public.delphi.non-technical)