Using SAS to backup an Access 2013 BE Database

From sasCommunity
Jump to: navigation, search

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;*/