Connecting SAS to Access 2007 (.accdb)

Here is a working example of how to connect SAS 9.1.3 SP4 to a Microsoft Office Access 2007 .accdb data source. First, let’s use a pass through query. The file is called c:\saves.accdb and contains a table called table1.

proc sql;
/* create an ODBC pass-through connection using the Microsoft Office Access 2007 .accdb driver */
	connect to odbc as savesdb
		(required="driver=Microsoft Access Driver (*.mdb, *.accdb);

/* query the pass-through connection */			
	create table table1_copy as
	connection to savesdb
		select * from table1
/* close the pass-through connection */
	disconnect from savesdb;

You should see the exact same driver name above as here: Control Panel – Administrative Tools – Data Sources (ODBC) – Drivers.

Next, let’s do the same without a pass through.

/* Create a standard SAS library using the Microsoft Access 2007 .accdb driver */
libname savesdb odbc 
		required="driver=Microsoft Access Driver (*.mdb, *.accdb);

/* copy the table using a DATA step */
data table1_copy;
	set savesdb.table1;

/* close the library */
libname savesdb;

SAS can also connect to Microsoft Access 2007 using OLEDB, but I think (at least, for my installation) that requires digging out the SAS installation CDs, so I prefer ODBC over OLEDB.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s