LIBNAME Statement and Pass-Through Facility for PC Files on Microsoft Windows

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:

JET::command

JET::: is required to distinguish special queries from regular queries.

These special commands are supported:

JET:: COMMIT: commits the transaction.
JET::ROLLBACK: causes a rollback in the transaction.
JET::AUTOCOMMIT: sets the COMMIT mode to AUTO and commits the transaction immediately.
JET::NOAUTOCOMMIT: sets 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

This example specifies the AUTOCOMMIT=NO connection option.

Note: Although these examples state indicate that they are for Microsoft Access, the syntax is the same for both Microsoft Access and Microsoft Excel. [cautionend]

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;

Top of Page