Run an SqlServerr Job from MS Access

• Download ISD_ex1 - 31,000 KB

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