topic Re: Start MS Access database Macro At The End Of SAS Program in SAS Programming

Start MS Access database Macro At The End Of SAS Program

dkstoneman — Wed, 09 Feb 2022 20:21:17 GMT

I'm new to SAS programming. I would like the syntax to start a MS Access database macro at the end of a SAS program. I've taken over some SAS programs that use the following syntax to kick off an Excel macro:

/*Opens the Excel macro prior to establishing the DDE to run the Excel VB macro***/
%let xlsMacro = \\wpmfiles\Intraday_Forecasting\30 Day Charts\Auto_Morning_Reports.xlsm;
options noxsync noxwait;
x "'&xlsMacro.'";

Is there syntax like this to start a macro in MS Access?

Re: Start MS Access database Macro At The End Of SAS Program

SASKiwi — Wed, 09 Feb 2022 21:31:12 GMT

If you are using a remote SAS server you can't start MS Access at all. This will only possibly work if you have SAS installed on your PC and you are using a local SAS session. You can write from SAS to an MS Access database without opening it though.

Re: Start MS Access database Macro At The End Of SAS Program

dkstoneman — Wed, 09 Feb 2022 22:00:22 GMT

The SAS application I'm using is on my local desktop computer. I know you can pass data to an Access database from SAS. I need the SAS program to open an Access database and then run a macro within that database. I have to do additional data manipulation in the Access database and then publish that new information to Excel workbooks via the macro. The additional manipulation can't be performed by a SAS program.

Re: Start MS Access database Macro At The End Of SAS Program

SASKiwi — Thu, 10 Feb 2022 02:00:31 GMT

So have you tried this outside of SAS on a Windows command line and it works?

According to Google this should work - MSACCESS.EXE appName.mdb /X=macroToBeExecuted

Re: Start MS Access database Macro At The End Of SAS Program

dkstoneman — Thu, 10 Feb 2022 15:42:17 GMT

I don't know how to kick off MS Access from a command line, but I can call Access and run the macro from Task Scheduler and other applications outside of SAS. Here is my process follow for what I'm trying to accomplish:

I kick off SAS via Windows Task Scheduler to import data from another source
Next a SAS program parses out the necessary data into individual tables and runs calculation processes
When the SAS program ends, I want to start the MS Access macro to complete additional work on the data before publishing it to other files.

Re: Start MS Access database Macro At The End Of SAS Program

SASKiwi — Thu, 10 Feb 2022 20:21:36 GMT

"I don't know how to kick off MS Access from a command line."

Right click on the Windows icon in the bottom left of your screen and select Run. Type in cmd to open a Command window. Type in the command I've already supplied you with and press the enter key.

Also if you don't know how to do something on your computer, have you tried using Google to search for the answer? I didn't know how to run MS Access from a command line but a quick search got me the answer.