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

Special Jet and Ace Queries

SAS/ACCESS Interface to PC Files supports a number of special queries that return information such as available tables, columns, and procedures.

The general format of special queries:

ACE|JET : <schema-rowset><'parameter-1'>...<',parameter-n'>>

ACE|JET: required to distinguish special queries from regular queries.
schema-rowset: the specific schema rowset that is being called. The valid schema rowsets are listed below.
'parameter-n': a quoted string. Parameters are separated from one another by commas. All parameters are optional, but the quotation marks must be included. If you specify some, but not all, parameters within an argument, use commas to indicate the omitted parameters.

The following special queries are supported:

ACE|JET ::CHECK_CONSTRAINTS: returns the check constraints that are defined in the database file.
ACE|JET ::COLUMNS <"table-name", "column-name">: returns the columns of the tables that are defined in the database file.
ACE|JET ::CONSTRAINT_COLUMN_USAGE <"table-name", "column-name">: returns the columns that are used by referential constraints, unique constraints, check constraints, and assertions that are defined in the database file.
ACE|JET ::FOREIGN_KEYS <"primary-key-table-name", "foreign-key-table-name">: returns the foreign key columns that are defined in the database file.
ACE|JET ::INDEXES <"index-name", "table-name">: returns the indexes that are defined in the database file.
ACE|JET ::KEY_COLUMN_USAGE <"constraint-name", "table-name", "column-name">: returns the key columns that are defined in the database file.
ACE|JET ::PRIMARY_KEYS <"table-name"">: returns the primary key columns that are defined in the database file.
ACE|JET ::PROCEDURES <"procedure-name">: returns the procedures that are defined in the database file.
ACE|JET ::PROVIDER_TYPES: returns information about the base data types that are supported by the Jet data provider.
ACE|JET ::REFERENTIAL_CONSTRAINTS <"constraint-name">: returns the referential constraints that are defined in the database file.
ACE|JET ::STATISTICS <"table-name">: returns the statistics that are defined in the database file.
ACE|JET ::TABLE_CONSTRAINTS <"constraint-name", "table-name", "constraint-type">: returns the table constraints that are defined in the database file.
ACE|JET ::TABLES <"table-name", "table-type">: returns the tables that are defined in the database file.
ACE|JET ::VIEWS <"table-name">: returns the viewed tables that are defined in the database file.

Example 1

Retrieves a rowset that displays all tables in the NorthWind database:

PROC SQL;      
  * CONNECT TO access database;
  CONNECT TO Access as db (PATH='c:\NorthWind.mdb');
  * list all tables including system tables and pass-through;
  SELECT * FROM CONNECTION TO db(jet::tables);
  * list table name and type where table type is TABLE only;
  SELECT table_name, table_type from CONNECTION TO db(jet::tables ,"TABLE";
  DISCONNECT FROM db;`
QUIT;

Example 2

Retrieve all data types that the Ace or Jet provider for Microsoft Access supports:

PROC SQL;      
  CONNECT TO access (PATH='c:\NorthWind.mdb');
  SELECT * FROM CONNECTION TO access(jet::provider_types);
QUIT;

Top of Page