Trigger MS Access function with SAS

Question

I’m a newbie on here so please be gentle :)

My question is: can I get SAS (9.4 with PC Files Server) to trigger a function in MS ACCESS that imports .txt files that SAS spat out earlier?

Background: I have inherited an Access database (.accdb, using vers. 2010), which source data comes from SAS. Current method is that SAS spits out .txt files, which I (semi-manually) import into the database. Semi-manually in that there is a button/macro that imports the files but I have to open the database and press the button. I would prefer not to have to intervene at all.

I tried to skip the import of tables by using PROC EXPORT (and PC FILES SERVER) and later PROC SQL (with Access database assigned via libname) to push the data from SAS directly. However, compared to the old approach, after doing a compact and repair, the database is still about twice the size. I searched the net on how to avoid increase in size but found no answers.

I would therefore like to keep the method of SAS spitting out the .txt files but also have SAS trigger the Access function in the database that imports the files.

The button in Access activates this code:

Private Sub ImportSASDataButton_Click()
Dim Update As Byte
    Update = MsgBox("Have you ran the SAS program that creates the text files?" _
                    , vbYesNo, "Check files have been created")

If Update = vbYes Then ImportTables
Me.CloseFormButton.SetFocus
Me.ImportSASDataButton.Enabled = False
End Sub

Which if “YES” activates this code (which is the function I wish to trigger directly):

Function ImportTables()
Dim db As Database
Dim ITables As Recordset

Set db = CurrentDb
Set ITables = db.OpenRecordset("Tables", dbOpenDynaset)

Do Until ITables.EOF
    Call ImportData(ITables![TableName], _
                    ITables![SpecificationName], _
                    ITables![FileName], _
                ITables![DeleteExisting], _
                ITables![Use])
    ITables.MoveNext
Loop
ITables.Close
End Function

Can you trigger this differently, always write to the same file name and have a script that runs when you start the Access DB to update the files/records automatically? That's usually how I set it up. — Reeza
Potentially, or use the autoexec macro as Richard describes, but as noted in my comment to Richard, it can take up to 5 min to update and it is also used as a front end to extract reports by other colleagues, and I do not wish to slow down their usage. — ELVEE
Sounds like you’re reaching the limits of an Access DB and need an actual reporting and database tool. — Reeza

Richard Richard · Accepted Answer · 2018-05-23T11:00:11

There are several SAS statements that can start external programs, including

%sysexec
call system
filename pipe

Access has the command line switch /x mymacro that will run a specific macro at startup, or you can create or update the AutoExec macro that is also run automatically every time Access is started.

So your SAS code might look like

%Spit;
%sysexec msaccess /x ImportTablesMacro;

Trigger MS Access function with SAS

2 Answers