executing a job from Access Data Project

Posted on 2005-05-02
Last Modified: 2006-11-18
I would like to accomplish the following in Microsoft Access Project

Created a list box, which holds all the current jobs in SQL Server.

I want to  add a command button ,which on  clicking will execute the selected job by
executing the stored procedure sp_startjob .

Is this possible? Are there any examples that i can look at?

Question by:aswam1975
    LVL 9

    Expert Comment

    As long as the user has the appropriate permissions, you can use this vb code:

    currentproject.execute "EXEC sp_startjob ..."



    Author Comment

    This is what i have done so far:
    I have a list called displayJobs.Added a command button called Start Job.To the click  event i have the following.

    However i get an error that says " Run time error 3701. Parameter object is improperly defined.
    Inconsistent or incorrect information is provided".

    The error is at line  .Parameters.Append prm. Can someone point out what i am doing wrong.

    Private Sub startJob_Click()

          Dim rst As ADODB.Recordset
          Dim cmd As ADODB.Command
          Dim prm As ADODB.Parameter
          Dim job As String
          Dim numb As Integer

              Set cmd = New ADODB.Command
          numb = displayJobs.ListIndex
              job = displayJobs.ItemData(displayJobs.ListIndex)

          Set prm = New ADODB.Parameter
          prm.Name = "job"
          prm.Type = adVarChar
          prm.Direction = adParamInput
          prm.Value = job
          Set cmd = New ADODB.Command
          With cmd
              .ActiveConnection = CurrentProject.Connection
              .CommandText = "sp_start_job"
              .CommandType = adCmdStoredProc
              .Parameters.Append prm
          End With

             Set prm = Nothing
                    Set cmd = Nothing

    End Sub
    LVL 9

    Accepted Solution

    I believe the parameter name you're looking for is called job_name.  Or you can use job_id.


    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now