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); dbq=c:\saves.accdb;"); /* query the pass-through connection */ create table table1_copy as select * from connection to savesdb ( select * from table1 ); /* close the pass-through connection */ disconnect from savesdb; quit;
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); dbq=c:\saves.accdb;"; /* copy the table using a DATA step */ data table1_copy; set savesdb.table1; run; /* 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.