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;