As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.
Here are some tips for How to share your SAS knowledge with your professional network.
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;*/