Trigger an MS Access macro in SAS

[PatricktLeon]

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!

[Reeza]

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. 

[ChrisHemedinger]

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;

[PatricktLeon]

Hi Chris,

Since I don't have X-CMD access in this system, I wanted to try to execute the stored procedure via the ODBC route you mentioned.  However, will this be possible to execute from EG on a SASApp server, put trigger the local Access database on a windows local connection?  Would you be willing to share the syntax of that, similar to the OLEDB code you so kindly shared?

[ChrisHemedinger]

@PatricktLeon Are you saying that you have SAS/ACCESS to ODBC on your SASApp system, and you want to connect back to a Microsoft Access database file on your local PC?  I don't have an example to support that.  

Since your Microsoft Access database is not a running database process, but simply a file that can be accessed using Microsoft data APIs (driven by ODBC in this case), I'm not sure that this approach is feasible.  SAS is running on your remote server, and assuming it's also a Windows machine (that has the software to drive MS Access actions), it would need to open/access your database file from where it's running, which might result in a large transfer of information to the server just to trigger these actions.

If you're looking to automate an MS Access action based on content from the SAS server, you might look into a different scripting method like PowerShell.  You can use PowerShell to connect to SAS, run code, get files, and then also to drive MS Access.  I have several PowerShell-SAS examples.  PowerShell-MS Access examples are easy to find with internet search.

[PatricktLeon]

Hi Chris,

Yes that is the correct scenario you are describing.  I have SAS/ACCESS to ODBC on my SASApp system, and I want to connect back to a Microsoft Access database file on my local PC.  I appreciate your suggestions and I'm a little frustrated that I'm working in a server multi-verse where they can't all talk to each other directly.  I downloaded your System Command(VB) add-in for EG; I can execute script to see the local directory (C: ) that contains an Access database.  The add-in log shows the file in the local directory correctly, but I haven't ironed out the script to trigger the Access macro.  Do you think it might be possible?  Thank you so much for your advanced insight!

[AlanC]

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