05-04-2018 01:14 PM
I work in EG on a SASapp Server, large data queries off a data warehouse. I also house additional data sources on servers that are not connected to the SASapp environment. I have an MS Access database that I export data from SAS into, and the Access database has a macro in it that I want to automate triggering in SAS. Does anyone have a good resource on triggering an Access macro in SAS code? I would love to automate this step of a process without going in to Access and clicking run macro, but I just can't figure it out. Thanks in advance!
05-04-2018 03:03 PM
Unfortunately I think either a DDE or VBS script is your best bet.
First check if you have XCMD option enabled so that you can pass commands to your system via an X/%SYSEXEC/CALL SYSTEM command.
I would probably find a VBS script that could do it and then execute the script via SAS.
05-04-2018 03:17 PM
You said that SASApp isn't connect to your MS Access environment, so this probably won't help you -- @Reeza's suggestion might be best.
But if you did have SAS/ACCESS to OLEDB or ODBC, you could use SAS to talk to MS Access, and then you could use a stored procedure in the database to trigger this work. You can run a database stored procedure via the EXECUTE statement in PROC SQL with an explicit passthrough connection.
proc sql; connect to oledb ( provider="Microsoft.Jet.OLEDB.4.0" properties=('data source'='C:\SAS\Data\youdatabase.mdb') ) ; execute (execute sasdata) by oledb; disconnect from oledb; quit;
05-04-2018 03:53 PM
I would avoid DDE/VBS (both are deprecated even though they continue to work) and use PowerShell instead. You may or may not be able to use PowerShell but is worth trying.
EG can also take add-ins using a .NET interface (I use C#). The add-ins are easy to do. See Chris' book on them at: