Using SAS to backup an Access 2013 BE Database
This process was created in order to have an automated process run from a server to backup an access database vs manually conducting a backup twice a month.
There are three macros of concern...
I included all of the code for my local process, you may need to edit or delete as necessary. You will need to edit the section where I have reviewed the contents of the Access hidden table and excluded tables based on my DB.
* The objective of this program is to be able to create a libname for the (insert your DB Name Here) Access Database. run a query to determine the tables of interest, place those table names into a macro and then call another macro which will upload the tables desires to the server. This is to serve as a backup process vs conducting a backup of the Access Back End..... At the end of the program, we delete files that are older than 5 days.... ; OPTIONS SYMBOLGEN MLOGIC; OPTIONS VALIDVARNAME=ANY; %global NUID; %let NUID = %LOWCASE(&SYSUSERID); * password file for network connections to oracle, clarity and db2.... This is an example, you will need to replace with your process... ; %include "/apps/sas/datasets/data59/HIRX/users/&NUID./PAS_DW_PASS_server.sas"; *My libname, change for your needs...; LIBNAME wellrxbu '/apps/sas/datasets/data59/HIRX/DatabaseBU/WellRx'; *call the macro from the password file...This is applicable for my site, edit/delete as necessary...; %_chrlibs(oracle); %_chrlibs(clarity); %_chrlibs(db2); * This code connects to the MS Acess Database for the WellRx project. It lists all of the table objects as well has hidden tables... It places a lock on the access database... Use this process to upload the database tables which serves as a backup process...; %MACRO importds(dsname); PROC IMPORT OUT= wellrxbu.&dsname._&sysdate /*For creating a series of tables older than 5 days...*/ /*PROC IMPORT OUT= wellrxbu.&dsname._14jun15*/ DATATABLE= "&dsname." DBMS=ACCESSCS REPLACE; DBPWD='YOUR DB PASSWORD'; DATABASE="\\INSERT YOUR ACCESS DB LOCATION-NAME HERE\ACCESSDB_be.accdb"; DBDSOPTS= 'READBUFF=5000'; SERVER='YOUR SERVER NAME'; PORT=9621; SERVERUSER='LOCAL USER ID'; SERVERPASS='PASSWORD TO ACCESS SERVER'; RUN; %MEND importds; %macro tblist; *connect to the ms access database......; LIBNAME test PCFILES PATH="\\YOUR PATH TO THE ACCESS DB BE\ACCESS_DB_be.accdb" Server='YOUR SERVER INFO' port=9621 SERVERUSER='USERID' SERVERPASS='LIGIN PW' DBPASSWORD='YOUR DB PASSWORD' readbuff=5000; *create a table from the hidden table that contains all of the db objects..edit for your purposes to exclude what you don't need...; Proc sql; create table temp as select name from test.MSysNavPaneObjectIDs /*This is a hidden table within the db which contains all of the objects....*/ where type=1 and ((name NOT LIKE 'MSy%') and (name not like 'f_%') and (name not like 'Web%') OR (name like 'final_%')) ; quit; *create a var to contain a numerical list of the records from our original query...; data temp2; set temp; j= _n_; run; *place all of the tables of concern into a single macro variable...; proc sql noprint; select name into :MyTbls separated by ' ' from temp2; quit; *place the max number into a macro var for looping purposes later...; proc sql noprint; select max(j) into :MyCount from temp2; quit; *loop through the tables we have identified; %do i=1 %to &MyCount.; *scan through the macro var MyTbls and place in a var called f...; %let f=%sysfunc(scan(&&MyTbls,&i)); %put &f; *%put &MyCount; *%put &MyTbls; *call the importds macro and use the tables listed in the var f...; %importds(&f); %END; %MEND tblist; *call the macro to loop through the tables identified and upload to the grid server...; %tblist; * delete any files that have a creation date older than 5 days; *do a proc contents of the wellrxbu libname...output a file keeping the libname memname (table names) and creation date for each...; /*%put &min_dt;*/ *from the unique list of tables that exist in the directory, identify those that have a creation date less than a date 5 days ago...; %macro execute; proc contents data=wellrxbu._all_ noprint out=buinfo(keep=libname memname crdate); run; *creat a file with unique tables...; proc sort nodupkey data=buinfo out=buinfo2; by libname memname crdate; run; *assign a date value from todays date minus five dats...as a reference date...any creation prior to this refdate will be deleted and after will be kept; %LET min_dt = %unquote(%str(%')%sysfunc(INTNX(day,%sysfunc(DATE()),-5), date9.)%str(%'))d; /*create a list of tables based on the creation date compared to the min_dt from above...*/ data to_be_deleted; set buinfo2; if datepart(crdate)< &min_dt; run; /*do a count of the tables identified...place into a var called Count...*/ proc sql; select count(*) into :Count from to_be_deleted; quit; * do some conditional processing: if the count is greater than 0, then proceed with the rest of the code... if not, then exit... ; %if &count>0 %then %do; %put execute further; proc sql noprint; select trim(memname) into :to_delete separated by ' ' from to_be_deleted; quit; proc datasets library = wellrxbu; delete &to_delete; run; quit; %end; %else %put exit; %mend; %execute; *this clears the lock for the access database....; /*Libname test clear;*/