Run Access macro from SQL Server

[bobfin]

We need to run an Access macro at 5:30 AM every morning thru SQL Server 2000 Enterprise. We've created a linked server (ForecastDb) to the database (Forecast.mdb) that has the macro (mcrForecast). But what T-SQL code do we put in the nightly job to run the macro? The macro runs too many complicated queries to convert them to T-SQL.

 

[Banana]

I've never used macros, but I'm pretty sure that isn't possible because you need to make your server somehow "aware" of such macros, which isn't usually exposed to whatever interface you use to connect (e.g. ODBC, OLE DB for example)

Your best bet would be to allow your SQL server to link back to the Access database, using ODBC, then using a Command object to execute a query that executes the macro, but that's... um... complicated.

Why not just execute the macros from Access side instead of the server?

What exactly does your macros do anyway?

 

[georgedwilkinson]

I highly advise you to reconsider your approach. If you're going to write TSQL, why not just do all the stuff you want done in SQL Server? Skip the Access DB and create a new job in the SQL Server Agent.

If you're going to use Access (i.e. NOT TSQL), create a separate Access DB with an Autoexec macro that closes the (Access) db when done. Set it up to run each morning in your windows task folder.