Excel VBA - Executing a job within SQL Server via a macro

Question

I have a job stored on a database, scheduled to run every day. But its sometimes necessary to want to execute this job at any given time to view up to date data (I'm using SQL Server Management Studio 2008).

The job itself simply takes data from a view which contains live data and puts it into a table which will then be used as a data source for an excel file. Executing the job drops and re-creates the table with fresh data.

In excel (2010), i wish to have a 'button' which which pressed will execute the job and then hitting refresh on the data tab in excel will then update the data on the sheet with the fresh data.

My question is: How do i execute this job from an excel macro?

Answer 1

Private Sub CmdRunJob_Click()
   Dim con As Object
   Set con = CreateObject("ADODB.Connection")
   con.Open = "DRIVER={SQL Server};SERVER=YourServer;" & _
       "USER=YourUser;PASSWORD=YourPassword;"
   con.Execute "exec msdb.dbo.sp_start_job 'YourJob'"
End Sub