SQL Server to control MS Access? 1

[genomon]

Have come up empty in searches so far. Does anyone know if there is a way to instantiate and control an MS ACCDB from SQL Server (2016 in this case)? I was thinking maybe APIs, command shell, etc.? This is not my area of expertise. The request is to automate some end-user ACCDBs that have sophisticated reporting solutions that cannot be duplicated in SSRS to be ran by SQL Server - I am guessing as a SQL Agent job?
Thanks!

Beir bua agus beannacht!

 

[NoCoolHandle]

Assuming the access db is on the sql server system you could probably build a macro in Access that generated the report and sent it somewhere then call that macro externally as is shown here.. add to a vbs script and execute that via xpcmdshell.

e.g.

Sub RunAccessMacro()
    Dim strDatabasePath As String
    Dim PathToDB As String
    Dim accApp As Access.Application
    Set accApp = New Access.Application



    PathOfDatabase = ThisWorkbook.Worksheets("Updates").Range("PathToAccess")
    accApp.OpenCurrentDatabase PathOfDatabase
    accApp.DoCmd.RunMacro "Macro_Run_Key"
    accApp.Quit
    Set accApp = Nothing


    MsgBox "Done!  All processes complete!!"

End Sub