Run a SQL Server Agent Job using Microsoft Access or when I get an Email

Question

I have a process where the user goes into an Access database and processes data and when that is finished, the Access process sends an Outlook email to me every time web hosting. Is there a way that the Access database can kick off a SQL Server Agent Job or when I get that Outlook email, I can trigger the job?

Thank you!

***Moved from Outlook/Outlook.com/People and contacts***

Answer 1

You can do this, I believe, but it involves a stored procedure on the SQL Server and a passthru query in Access.

The Stored Procedure will look something like this:

USE [msdb]

GO

/****** Object:  StoredProcedure [dbo].[ExecuteJob]    Script Date: 4/23/2022 9:39:58 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[ExecuteJob]

    AS

	DECLARE @JobId binary(16) 

	SELECT @JobId = job_id FROM msdb.dbo.sysjobs WHERE (name = 'YourJobNameGoesHere')

IF (@JobId IS NOT NULL)

BEGIN

    EXEC msdb.dbo.sp_start_job @job_id = @JobId;

END

GO

Pay particular attention to the fact that this stored procedure needs to be in msdb, not the database where the job is.

Now, from Access you can run this stored procedure in a passthru query. Here is the SQL for that passthru:

USE [msdb] 

exec  [msdb].[dbo].[ExecuteJob]

You can invoke the Passthru in the VBA procedure, after the other work is done and the email is sent to you.

Answer 2

Dear Aqib,

Welcome to the forum here.

Based on the description, it seems that you may need a VBA solution. It seems that you have posted a new thread on the VBA specific support channel mentioned in Office VBA support and feedback and the enginners there have replied to you.

I suggest you follow up on the thread with them for the latest updates if you need further help on the question.

Thanks for your effort and time.

Cliff