Click here to Skip to main content
Click here to Skip to main content

Run an SqlServerr Job from MS Access

, 11 Dec 2007
Rate this:
Please Sign up or sign in to vote.
Run an SqlServerr Job from MS Access
Screenshot - ISD_ex1

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Seifedine kadry
Web Developer
Lebanon Lebanon
PhD in Applied Math and C.S.
IEEE member

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 12 Dec 2007
Article Copyright 2007 by Seifedine kadry
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid