SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Trigger an MS Access macro in SAS

Reply
New Contributor
Posts: 3

Trigger an MS Access macro in SAS

Hello community,

 

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!

Super User
Posts: 23,291

Re: Trigger an MS Access macro in SAS

Posted in reply to PatricktLeon

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. 

Community Manager
Posts: 3,379

Re: Trigger an MS Access macro in SAS

Posted in reply to PatricktLeon

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;
Regular Contributor
Posts: 150

Re: Trigger an MS Access macro in SAS

Posted in reply to PatricktLeon

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:

 

https://www.amazon.com/Custom-Tasks-Enterprise-Guide-Microsoft/dp/1607646781

 

Ask a Question
Discussion stats
  • 3 replies
  • 257 views
  • 8 likes
  • 4 in conversation