Introduction
The goal of this article is to run an SQLSEVER Job from Microsoft Access.
Background
First of All you should crate the Job, in this example i have created a job to extract data from sqlserver table to an Excel Sheet then i have created an Access database with a small form and button to run this job which i called "trstp". the idea here to create an ODBC connection and run the predifined stored procedure(sp) "sp_start_job" then i passed the job name as parameter to this sp.
Using the code
Option Compare Database
Private Sub Command7_Click()
Dim sServer As String
Dim sUser As String
Dim sPWD As String
Dim sDatabase As String
Dim DBcon As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objparameter As New ADODB.Parameter
Dim objRs As New ADODB.Recordset
sServer = "(local)"
sDatabase = "msdb"
sUser = "sa"
sPWD = "123456"
DBcon.ConnectionString = "Provider=sqloledb;" & _
"server=" & sServer & ";uid=" & sUser & ";pwd=" & sPWD & ";database=" & sDatabase
DBcon.CursorLocation = adUseClient
DBcon.Open
objparameter.Direction = adParamInput
objparameter.Type = adVarChar
objparameter.Size = 5
objparameter.Value = "testp"
objCmd.Parameters.Append objparameter
objCmd.ActiveConnection = DBcon
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "sp_start_job"
Set objRs = objCmd.Execute ' objCmd.Execute for no resultset
' Set objRs.ActiveConnection = Nothing
Set objCmd = Nothing
DBcon.Close
End Sub
