Append to Microsoft Access database

Chip — Tue, 27 Mar 2007 15:37:49 GMT

How do I append to an access database? Do I need all variables in my SAS data set or I can append to the Access table with just some of them?

I tried this, but SAS didn't like my syntax.

proc sql noprint;
connect to odbc (dsn="CBR Tracker");
execute(insert * from to_tracking into Tbl_entry1) by odbc;
disconnect from odbc;
quit;

Thanks!

Re: Append to Microsoft Access database

Bill — Wed, 28 Mar 2007 13:10:33 GMT

Don't know if this is the most efficient method, but it works.

1. Import the existing mdb into SAS
2. Append the new data to the SAS dataset
3. Export the new SAS dataset to mdb

PROC IMPORT OUT= WORK.NewOrdFaults
DATATABLE= "NewOrderFaults"
DBMS=ACCESS REPLACE;
DATABASE="\\Moftp\MainFrameFtp\NewOrderFaults.MDB";
SCANMEMO=YES;
USEDATE=YES;
SCANTIME=NO;
RUN;

data UpDater;
set NewOrdFaults
CatalogWk2;
format Delivery DeliveryWk Generate GenerateWk date9.;
run;

PROC EXPORT DATA= WORK.UpDater
OUTTABLE= "NewOrderFaults"
DBMS=ACCESS REPLACE;
DATABASE="\\MOFtp\MainFrameFtp\NewOrderFaults.MDB";
RUN;

topic Append to Microsoft Access database in Microsoft Integration with SAS

Append to Microsoft Access database

Re: Append to Microsoft Access database