how to run sql job from ms access

karinos57
karinos57 used Ask the Experts™
on
Hi,
i could not find any usefull links on google about how to start a sql job from ms access either using vba or some other way.  can someone tell me specific ways of handling this issue i am having?  i have sql server 2008 and ms access 2010.  thnx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Define clearly what you mean by a "SQL job"...

Not sure, but I don't think you can do this directly.
SQL server has no VBA interaction (that MS Access can trigger) that I know of.

You may be able to start the SQL server executable form Access though...
Dim varApp As Variant
varApp = Shell("sqlservr.exe", vbNormalFocus)

(Yes, I believe that spelling is correct "sqlservr.exe"...)


Let's see what other Experts post...

JeffCoachman
Theo KouwenhovenApplication Consultant

Commented:
Hi Jeff, karinos57,

Beside the fact that there is no useful information for MS-Access because it's not a very useful application :-)

Jeff is right, AFAIK only the Shell function can initiate the SQL server job.
But is that the question, or do you mean to execute a job on the sqlserver?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can create a new empty query in Access.
Pick from menu/band the SQL specifics and set the query to a Pass-Through query.
Open properties of the query to specify your connection string to the SQL Server database.

Write in the SQL pane the code to run your stored procedure.
Save the query.

You can now run it from code: CurrentDb.QueryDefs("NameOfYourQuery").Execute

/gustav

Author

Commented:
thanks guys, yes i am trying to run a sql job that is on a server from ms access db.  I basically want the users to be able to start the sql job by clicking one of the buttons on the form.  Gustav, can you post a sample of your method as i am having some issues?  thanks
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
It may be ReturnsRecords you have to set.
You can even create the query on the fly:

  Set qd = db.CreateQueryDef()
  qd.Name = "test"
  qd.Connect = "ODBC;DSN=TestDataset_Name;SRVR=Server_Name;DB=Database_Name"
  qd.SQL = Name_of_stored_procedure
  qd.ReturnsRecords = False
 
  db.QueryDefs.Append qd
  qd.Execute

/gustav
ramromconsultant

Commented:
Could someone explain "sql job". I am not familiar with the term.

Author

Commented:
Gustav,
thank you so much for your time.  i have never created this kind of query before so i am having all kind of issue when i try to implement this method.  so can you please post a sample database with your method so i can see exactly what you did?  thanks again
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Not really, sorry. I don't use Access for this.

/gustav
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
karinos57

One more time...

Can you please clearly define what you mean by an *SQL JOB*?

In other words, ...in plain English, what are you trying to do here?
Run an access query from SQL server?
Run a SQL Server Stored procedure from Access?
Run a Pass through query?
Link a table from SQL Server to Access?
...etc

Just for clarity...

Author

Commented:
all what i am trying to do is run a sql job that i created in sql server from  my Access DB.  sql job is a job that i scheduled it to run some queries in my sql server.  So i have Access DB that i would like to trigger the job from the access db using stored procedure in my sql server or vba or trigger function.  whatever is easier and works fine.  so again, i have ms access db 2010 and want to run a sql job from my access db, once the user clicks some kind of button in my access db then i want the sql job in my sql server to run it.  i hope i explained little bit better.  thanks for your time.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Thanks for the explanation, that is a lot clearer.

Perhaps gustav's suggestions will get you what you are after...


;-)

JeffCoachman
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
By second thought, unless your job just runs a stored procedure (which can be executed from Access as described above), and your job is a "SQL Server Agent job", you need something like Visual Studio and the SQL Server management extension to control the job from the outside.

Here is how using SQL Server Management Studio:

http://msdn.microsoft.com/en-us/library/ms190774(v=sql.105).aspx

/gustav
Top Expert 2012
Commented:
Actually it is quite simple using ADO and the Command object to execute the system Stored Procedure to start the SQL Server Agent job.  In other words your CommandText should have something like this:
EXEC sp_start_job N'Your SQL Server Agent job name goes here'

Author

Commented:
tx
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial