How to Run a SQL Server Agent Job from Microsoft Access
Question details
The user needs to automate workflow by triggering a SQL Server Agent Job directly from Microsoft Access after processing local data.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Automating database workflows where Microsoft Access processes data and subsequently needs to initiate a backend SQL Server Agent job.
- Observed behavior
- Requires a method to execute the sp_start_job procedure on the SQL Server using an Access pass-through query and VBA script.
Ensure your Access database has an active ODBC connection to the SQL Server and that your SQL Server login has the required permissions in the msdb database to execute the sp_start_job stored procedure.
Use a Pass-Through Query and VBA to Execute the Job
Create a stored procedure on your SQL server and trigger it using a Microsoft Access pass-through query executed via VBA.
By utilizing a pass-through query, Microsoft Access can send commands directly to the SQL Server without processing them locally. This is the most efficient way to start a SQL Server Agent Job using the built-in sp_start_job procedure.
Verify that you know the exact name of the job you want to run. The execution script will look like this: USE msdb; EXEC msdb.dbo.sp_start_job @job_name = 'YourJobName';
Open your Microsoft Access database. Go to the 'Create' tab on the ribbon, select 'Query Design', close the 'Show Table' dialog, and click 'Pass-Through' in the Query Type group.
In the pass-through query editor window, type the SQL command: EXEC msdb.dbo.sp_start_job @job_name = 'YourJobName'; (Replace 'YourJobName' with the actual name of your SQL Agent job).
Open the Property Sheet for the query. Click on the 'ODBC Connect Str' property and build the connection string that points to your SQL Server database, ensuring you include the necessary credentials.
Open the VBA editor (Alt + F11). In your data processing module, add a line of code to run the query after your local Access processing finishes, for example: CurrentDb.Execute "YourPassThroughQueryName".
Looking for a Lightweight Alternative for Everyday Office Tasks?
While complex database management with Microsoft Access and SQL Server requires specialized software, your everyday documentation, data analysis, and presentations can be handled flawlessly with WPS Office. It is a free, lightweight suite that serves as an excellent alternative to Microsoft Word, Excel, and PowerPoint.
- 1. Download the installer: Visit the official WPS Office website and click the free download button to get the setup file.
- 2. Install WPS Office: Run the downloaded installer and follow the simple on-screen instructions to complete the setup.
- 3. Open your documents seamlessly: Launch WPS Office and directly open any of your existing Microsoft Word, Excel, or PowerPoint files with complete format compatibility.
Frequently Asked Questions
Why do I get a permission denied error when running the pass-through query?
The SQL Server user credentials provided in your Access ODBC connection string must have the necessary execution rights in the msdb database. Specifically, the user needs to be a member of the SQLAgentOperatorRole or have explicit permissions to run the sp_start_job procedure.
Can I pass parameters to the SQL Server Agent Job directly from Microsoft Access?
SQL Server Agent Jobs do not accept dynamic parameters directly through the sp_start_job command. Instead, you can have Access write the parameters to a staging table in SQL Server first, and then configure the SQL Agent Job to read from that staging table when it executes.
Is it possible to check if the SQL Agent job succeeded from Microsoft Access?
Yes, but since sp_start_job runs asynchronously, it only confirms the job has started. To check for success, you must write a separate pass-through query that polls the sysjobhistory table in the SQL Server msdb database to retrieve the job's final status.
