Special Jet Commands
Microsoft Access and Microsoft Excel engines support
several special commands in the pass-through facility. Here is the
general format of special commands.
Possible Values
JET::COMMAND |
general format |
JET:: |
required to distinguish special queries from regular
queries.
|
JET::COMMIT |
to commit a transaction |
JET::ROLLBACK |
to cause a rollback in the transaction |
JET::AUTOCOMMIT |
to set the COMMIT mode to AUTO and commit the transaction
immediately
|
JET::NOAUTOCOMMIT |
to set the COMMIT mode to MANUAL. When the COMMIT
mode is set to MANUAL, you must issue a COMMIT or ROLLBACK command
to commit or rollback the transaction.
|
Example: Syntax Examples
Although these examples
are for Microsoft Access, the syntax is the same for Microsoft Excel.
AUTOCOMMIT with the NO Connection Option.
PROC SQL;
CONNECT TO access( PATH='d:\dbms\access\test.mdb' AUTOCOMMIT= no );
EXECUTE(CREATE table x (c1 int) ) BY access;
EXECUTE(INSERT INTO x values( 1 ) ) BY access;
/* To commit the table CREATE and insert ; */
EXECUTE(jet::commit) BY access;
EXECUTE(INSERT INTO x values( 2 ) ) BY access;
/* To rollback the previous insert ; */
EXECUTE(jet::rollback) BY access;
EXECUTE(jet::AUTOCOMMIT) BY access;
/* the insert is automatically committed, you cannot rollback the insert. */
EXECUTE(INSERT INTO x values( 3 ) ) BY access;
/* you should have a table CREATEd with 2 rows. */
DISCONNECT FROM access;
QUIT;