OleDB "BEGIN TRANSACTION"

[Edwin Knoppert]

Is it possible to use Execute with the "BEGIN TRANSACTION" statement?
I know there is a connection setting for this, i would like to use it this way.

If it exists, how is the exact syntax?

Using MDAC 2.8 JET40 creating an xls file and inserting records.

[Michael Mattias]

BEGIN TRANSACTION is generally a syntax used with ESQL (Embedded SQL), used by a precompiler.

I do not know about the ADO or COM interface, but just the past couple of weeks I've been working thru ODBC on a transaction-based processing application.

Thru ODBC, if you turn off the (default) autocommit mode, the Jet engine or maybe it's the ODBC driver automatically 'knows' when a transaction begins, and does not COMMIT until you issue a call to SqlEndTran. (it will rollback if you don't do this and otherwise end the processing of statements).

I do know that using ODBC you never 'execute' a "BEGIN TRANSACTION" or a "COMMIT" or "ROLLBACK" as you would in a 'command mode' or a PL/SQL script.

I don't know that this behavior applies to non-ODBC, but maybe it will give you an idea where to look if accessing your database via some other means.

MCM

[Knuth Konrad]

In theory, you can pass multiple SQL statements separated by a semicolon. So something like ...

sSQL = "BEGIN TRANSACTION;UPDATE/INSERT/DELETE ....;COMMIT TRANSACTION;GO"

ADODB.Connection.Execute sSQL, , adExecuteNoRecords Or adCmdText

... should work.