Microsoft Access Database Files

Microsoft Access File Essentials

SAS/ACCESS Interface to PC Files works with Microsoft Access database 97, 2000, 2002, 2003, and 2007 files.
Microsoft Access is a desktop relational database system that uses the Microsoft Ace engine or the Microsoft Jet engine to store and retrieve data.
A database is a collection of information that is related to a particular subject or purpose, such as tracking customer orders or maintaining a music collection. If the size is greater than, or equal to MS Access database 2007, all objects in an MS Access database are stored in the Jet .mdb or Ace .accdb format.
The following table lists the maximum size limits for the methods of .mdb and .accdb files.
Microsoft Access Database (.mdb and .accdb) Maximum Size Limits per Method
File size
2GB /32,768 objects
Number of fields per table
255 units
Number of characters per text field
255 characters
Memo field size
65535 characters
Table name size
64 characters
Field name size
64 characters
Record size
For .mdb: 2000 characters (excluding memo and OLE object fields)
For .accdb: 4000 characters (excluding memo and OLE object fields)
While importing data from a table, SAS converts special characters in a table name to underscores in the corresponding data set name. If a field contains special characters, SAS converts special to underscores in the corresponding variable name.

Microsoft Access Data Types

Summary of field data types that are available in Microsoft Access; their uses; and their storage sizes.
ATTACHMENTS
specify attachment to images, spreadsheet files, documents, charts, and other types of supported files to rows in your database.
AUTONUMBER
use for unique sequential (incrementing by 1) or random numbers that are automatically inserted when a row is added. Stores 4 bytes; stores 16 bytes for Replication ID (GUID).
CURRENCY
use for currency values and to prevent rounding during calculations. Stores as 8-byte numbers with precision to four decimal places.
DATE/TIME
use for dates and times. Stores as 8-byte numbers.
HYPER LINK
use for hyperlinks. A hyperlink can be a UNC path (link) or a URL (link). Stores up to 64,000 characters.
MEMO
use for lengthy text and numbers, such as notes or descriptions. Stores up to 32767 characters. ACCESS 2007 stores up to 65535 characters.
NUMBER
use for data to be included in mathematical calculations, except for calculations involving money (use Currency type). Stores 1, 2, 4, or 8 bytes; stores 16- bytes for Replication ID (GUID). The FIELDSIZE property defines the specific Number type.
OLE OBJECT
use for OLE objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data) created in other programs using the OLE protocol (link). Stores up to 1 gigabyte (limited by disk space).
TEXT
use for text or combinations of text and numbers, such as addresses, numbers that do not require calculations, telephone numbers, part numbers, or postal codes. Stores up to 255 characters. The FIELDSIZE= option, controls the maximum number of characters that can be entered.
YES | NO
use for data that can be only one of two possible values, such as YES | NO, TRUE | FALSE, ON | OFF. Stores 1 bit.
For YES value use –1.
For NO value use 0.
Note: NULL values are not allowed.

The Conversion of Date and Time Values between SAS Data Sets and Microsoft Access Database

In Microsoft Access database software, the following date and time rules apply:
  • Date values are valid back to 30 December 1899 and are saved as the integer value: 0.
  • Date values are valid ahead to 31 December 9999, and are saved as the integer value: 2,958,465.
  • Years 4000 and 8000 are considered leap years.
  • Time value is the decimal portion of a number that represents time as a proportion of a day.
  • Numbers can have both a date and a time portion, the format is in date/time format.
  • Number display formats support date, time, or date/time formats.
In SAS software, the following date and time rules apply:
  • Dates are valid back to A.D.1582.
  • Date values before year 1582 are represented as missing values.
  • Dates are valid forward to A.D. 9,999.
  • Date values are represented by the number of days between January 01, 1960, and that date.
  • Years 4000 and 8000 are not considered leap years.
  • Time values are represented by the number of seconds between midnight and that time of day.
  • Date and time values are represented by the number of seconds between midnight January 01, 1960, and that date and time.
  • Time values can be imported as a date value 30 Dec 1899. Ensure that you assign the correct format.

IMPORT and EXPORT Procedure Statements for Access Files

Overview

Before you use the IMPORT and the EXPORT procedures for Access files, it is helpful to be familiar with Access file formats. For an existing .mdb file, you can specify DBMS=ACCESS on Windows platforms, and SAS identifies the version.
  • Access 2000, 2002, and 2003 share the same .mdb file formats. ACCESS2000, ACCESS2002, and ACCESS2003 are treated as aliases for Access in SAS.
  • Access 2007 supports .accdb file formats.
See Example 1: Import a SAS Data Set from an Access 2007 Database Table and Example 2: Export a SAS Data Set to Create an Access Database File for additional information.
IMPORT and EXPORT Procedure Statements for Access Files on Windows
Data Source
Syntax
Valid Value
Default Value
PROC
IMPORT
PROC
EXPORT
ACCESS
ACCESS97
DATABASE
Complete path and filename for the Microsoft Access database file.
Yes
Yes
DBDSOPTS
Any valid data set options for Microsoft Access database engine.
Yes
Yes
DBPASSWORD
Database password.
Yes
Yes
DBSASLABEL
Yes | No
Yes
Yes
No
MEMOSIZE
1 to 32767
1024
Yes
No
PASSWORD
User password.
Yes
Yes
SCANMEMO
Yes | No
Yes
Yes
No
SCANTIME
Yes | No
Yes
Yes
No
USEDATE
Yes | No
No
Yes
No
USER
User ID.
Yes
Yes
DBSYSFILE
Complete path and filename for the Workgroup Administration file.
Yes
Yes
On Linux, UNIX, and Microsoft Windows operating platforms, you can use the client/server model to access data in .mdb files and .accdb files. For more information, see PC Files Server Administration.
For an existing .mdb you can specify DBMS=ACCESSCS when using the client/server model and SAS identifies the version of Access for you. You can specify VERSION = '97' when using the client/server model only when you want to export and create a new .mdb file with Access version 97 formats.
Note: If the client/server model is used, the SAS client cannot access an Access database file with both database password protection and user level security protection. In this case, you must choose only one security protection to be able to access your Access database file from a SAS client.
The following table lists the statements to import or export data to or from an Access database file using the client/server model. The statements are valid on Linux, UNIX, and Microsoft Windows operating platforms.
IMPORT and EXPORT Procedure Statements for Access Files When Using PC Files Server
Data Source
Syntax
Valid Value
Default Value
PROC
IMPORT
PROC
EXPORT
ACCESSCS
DATABASE
Complete path and filename for the Microsoft Access database file.
Yes
Yes
DBDSOPTS
Any valid data set options for Microsoft Access database engine.
Yes
Yes
DBPASSWORD
Database password.
Yes
Yes
DBSASLABEL
Yes | No
Yes
Yes
No
DBSYSFILE
Complete path and filename for the Workgroup Administration file.
Yes
Yes
MEMOSIZE
1 to 32767
1024
Yes
No
PASSWORD
User password.
Yes
Yes
PORT
1 to 65535
9621
Yes
Yes
SCANMEMO
Yes | No
Yes
Yes
No
SCANTIME
Yes | No
Yes
Yes
No
SERVER
Server name.
Yes
Yes
SERVERPASS
Server password.
Yes
Yes
SERVERUSER
Server user ID.
Yes
Yes
SERVICE
Service name.
Yes
Yes
SSPI
Yes | No
Yes
Yes
USEDATE
Yes | No
No
Yes
No
USER
User ID
Yes
Yes
VERSION
97 | 2000 | 2002 | 2003 | 2007 | 2010
2000
Yes
Yes
DATABASE= database
specifies the complete path and filename of the Access database file that contains the specified DBMS table. If the database name does not contain lowercase characters, special characters, or national characters ($, #, or @), you can omit the quotation marks.
Note: SAS does not generate a default value, but you can configure one in the DBMS client software.
DBDSOPTS= data set options that are valid for the Access database LIBNAME engine
Enables you to take advantage of useful data set options for the LIBNAME engine such as READBUFF= , INSERTBUFF= , DBTYPE=, DROP= , FIRSTOBS= , and OBS=. This option is for advanced users who are familiar with the PC files LIBNAME engine.
Requirements:You must surround the options in single quotation marks. 
   /* Example of correct use */
      DBDSOPTS=’FIRSTOBS=10 READBUFF=25’;

If the option string that you are specifying contains single quotation marks, you must use double quotation marks around it in your statement.

   /*Example of correct use */
     DBDSOPTS="DBTYPE=(BilledTo=’CHAR(8)’)";

Notes:For users who use the client/server model to access data in an Access database, the default value for READBUFF= is 1. To improve performance for reading data, you should set the READBUFF= option to 25 or higher.

For users who use the client/server model to access data in an Access database, the default value for INSERTBUFF= is 1. To improve performance for reading data, you should set the INSERTBUFF= option to 25 or higher.

DBPASSWORD= database-file-password
enables you to access a file if database-level security is set in the .mdb file. This option enables you to access .mdb and .accdb files with passwords, but it does not allow you to create .mdb and .accdb files with passwords included. A database password is case sensitive. You can define a database password instead of user-level security.
Alias: DBPWD | DBPW
DBSASLABEL= COMPAT | NONE | YES | NO
specifies the data source for column names.
COMPAT specifies that the data source column headings are saved as the corresponding SAS label names.
NONE specifies that the data source column headings are not saved as SAS label names. The SAS label names are then left as blanks.
Aliases: YES (for COMPAT)

NO (for NONE)

Restrictions: Due to a Microsoft Jet engine limitation, no more than 64 characters of column names are written to SAS variable labels.

Due to a limitation in the Microsoft Ace engine and the Microsoft Jet Excel engine, using MIXED=YES could result in improper text variable lengths.

DBSYSFILE= complete path and filename for the Workgroup Administration file
specifies the location of the Workgroup Administration file. You might have defined this file, which contains information about the users in a Workgroup, for your Microsoft Access database.
Alias: WGDB
Note: When you install Microsoft Access, the Setup program automatically creates a Microsoft Access Workgroup information file that is identified by the name and organization information that you specify. Because this information is often easy to determine, it is possible for unauthorized users to create another version of this Workgroup Information file and assume the irrevocable permissions of an administrator account (a member of the Admins group) in the Workgroup defined by that Workgroup Information file. To prevent this, create a new Workgroup Information file and specify a Workgroup ID (WID). Only someone who knows the WID can create a copy of the Workgroup Information file. Any user and group accounts or passwords that you create are saved in the new Workgroup Information file.
MEMOSIZE= 1 to 32767
Specifies the maximum variable length in SAS that is allowed while importing data from memo columns of an Access database table. Any memo data in an Access database tables whose length exceeds 32767 is truncated when it is imported into SAS.
Alias: DBMAX_TEXT
Restriction: If the maximum length that SCANMEMO = option is greater than the value of the MEMOSIZE= option, the smaller value in the MEMOSIZE = option is applied as the SAS variable width.
PORT= port-number
specifies the number of the port that is listening on the PC Files Server. The valid value is between 1 and 65535. This port or service name displays on the PC Files Server display when the application is started in server mode.
Alias: PORT_NUMBER
Default: 9621
Restrictions: Available only for the client/server model.

The PORT= statement option and the SERVICE= statement option should not be used in the same procedure.

SCANMEMO= YES | NO
specifies whether to scan the memo data to determine the column length for each memo-type source column.
YES scans the length of memo data for a data source column. Uses the length of the longest memo text of data that it finds, as the SAS variable width.
NO does not scan the length of memo data for a data source column. The column length returned from the Microsoft Ace engine or the Microsoft Jet engine is used as the SAS variable length.
Restrictions:When SCANMEMO=YES, if the maximum length that SCANMEMO = option is greater than the value of the MEMOSIZE= option, the smaller value in the MEMOSIZE = option is applied as the SAS variable width.

SCANMEMO = does not apply to text type columns. This option applies only to memo data type columns.

SCANTIME= YES | NO
specifies whether to scan the date/time data while importing data from a date/time column from an Access database.
YES scans the date/time column and assigns the TIME. format for a date/time column only if time values are found in the column.
NO specifies not to scan the date/time column.
Interactions: The DATE9. format is assigned for a date/time column if USEDATE= YES.

The DATETIME. format is assigned for a date/time column if USEDATE= NO.

SERVER= PC-Files-Server-name
specifies the name of the PC Files Server, where PC-Files-Server-name can be either the computer name or the associated IP address. You must bring up the listener on the PC Files Server before you can establish a connection to it. You can also configure these items:
  • the service name
  • the port number
  • the maximum number of concurrent connections
  • specifications to indicate whether data encryption will be used
Alias: SERVER_NAME
Restriction: Available only for the client/server model.
Note: You can omit this option if you are running SAS and the PC Files Server on the same machine. Omitting this option under this condition causes the PC Files Server to start automatically in the background.
SERVERPASS= server-user-password
specifies the password for the User ID given. If the account has no password, omit this option. Always enclose the value in quotes. This preserves the case of the password.
Alias: SERVERPASSWORD | SERVERPW | SERVERPWD
Notes: Passwords are generally case sensitive.

Use the PASSWORD= option for database passwords.

Example: LIBNAME example using explicit user name and password for PC Files Server:
   LIBNAME DB PCFILES PATH='C:\myfile.mdb'
		    SERVER=fileserv; 
		    SERVERUSER='mydomain\myusername'; 
   	 SERVERPASS='mypassword';
   RUN;
SERVERUSER= server-user-name
specifies a domain and user ID that is valid for the PC running PC Files Server. Always enclose the value in quotes. Otherwise, the backslash can be misinterpreted by the SAS parser.
Alias: SERVERUID
Notes: If you are not on a domain, omit the domain name and the backslash.

Use the USER= option for database User IDs.

Example: LIBNAME using explicit user name and password, for PC Files Server:
   LIBNAME DB PCFILES PATH='C:\myfile.mdb'
		    SERVER=fileserv;
		    SERVERUSER='mydomain\myusername';
		    SERVERPASS='mypassword';
   RUN;
SERVICE= service-name
specifies the service name that is defined on your service file for your client and server machines. This port number or service name is displayed on the PC Files Server control panel screen when it is started on the PC in server mode.
Alias: SERVICE_NAME
Restrictions:Available only for client/server model.

Do not use this statement and the PORT= statement option in the same procedure.

Note: This service name needs to be defined on both your UNIX machine and your PC Files Server.
SSPI= YES | NO
enables PC Files Server to allow Integrated Windows Authentication. This is a mechanism for Windows client and server to exchange credentials.
Default: NO
Restriction: Valid only on Windows 64-Bit.
Note: SSPI can also be enabled by specifying the –SSPI option on the SAS command line.
Example: LIBNAME using SSPI:
   LIBNAME DB PCFILES PATH='C:\myfile.mdb'
		    SERVER=localhost; 
		    SSPI = 'yes';
   RUN;
USEDATE= YES | NO
specifies whether to assign a DATE. or a DATETIME. format while importing a date/time column from a Microsoft ACCESS workbook.
YES assigns the DATE. format for the corresponding date/time column in the Microsoft ACCESS table. See the SCANTIME = option to assign the appropriate TIME format.
NO assigns the DATETIME. format for the corresponding date/time column in the Microsoft ACCESS table.assigns the DATETIME. format for the corresponding date/time column in the Microsoft ACCESS table.
See: For processing of date and time values between SAS and Microsoft Access, see Processing Date and Time Values between SAS and Microsoft Access.

The SCANTIME= statement option in order to assign the appropriate TIME format.

VERSION= file-version
specifies the version of the file that you want to create. Valid values are 2007, 2003, 2002, 2000, and 97. The default value depends on the extension of the file. Always surround the version value with single quotation marks.
Restriction:Available only for client/server model.
Interaction:If the file exists on the PC Files Server, then the statement is ignored.

Example 1: Import a SAS Data Set from an Access 2007 Database Table

This code imports a data set named CUSTOMER from the Customers table in a Microsoft Access database: demo.accdb. The Microsoft Access table was saved in version 2007 format. 
PROC IMPORT OUT=WORK.CUSTOMER
           DATATABLE='Customers'
           DBMS=ACCESS REPLACE;
    DATABASE="&demodir.demo.accdb";
    USEDATE=YES;
    SCANTIME=NO;
    DBSASLABEL=NONE;
RUN;

Example 2: Export a SAS Data Set to Create an Access Database File

This code exports a SAS data set named EMPLOYEE and creates a new Microsoft Access database file named test2000.mdb. Note that test2000.mdb does exist before the EXPORT procedure is submitted. SAS loads and names the table Employees. It then creates and saves it in the new file, test2000.mdb. 
X 'DEL c:\temp\test2000.mdb';
PROC EXPORT DATA=SDF.EMPLOYEE
        OUTTABLE='Employees'
        DBMS=ACCESS REPLACE;
    DATABASE='c:\temp\test2000.mdb';
RUN;

Example 3: Import a Data Set from an Access Database File Using a Read Buffer

This code imports a data set named INVOICE from the Invoice table in a Microsoft Access database named demo.mdb. The read buffer is set to 10 rows. 
PROC IMPORT OUT=SDF.INVOICE
            TABLE='Invoice'
            DBMS=ACCESSCS REPLACE;
   DATABASE="&pcfdir.demo.mdb";
   SERVER="&server";
   DBDSOPTS='READBUFF=10';
RUN;

Example 4: Export a SAS Data Set to an Access Database File on a PC Files Server

This code exports a SAS data set named ORDERS to a new Microsoft Access database file named testpcfs.mbd, located on the PC Files Server. The column, SPECINST, is dropped. The write buffer is set to 25 rows.
The code performs these tasks: 
LIBNAME SDF   "&sasdir";
PROC EXPORT DATA=SDF.ORDERS (DROP=SPECINST)
            OUTTABLE='Orders'
            DBMS=ACCESSCS REPLACE LABEL;
   DATABASE='c:\temp\testpcfs.mbd';
   SERVER="&server";
   VERSION='97';
   DBDSOPTS='INSERTBUFF=25';
RUN;
Copyright © SAS Institute Inc. All rights reserved.