Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

Error when executing a job from MSAccess

Hello.

I have a customer who has an Access application.  

The last step of the Access application needs to start a job in SQL server.

This is what I have in my pass-through query:

SET NoCount ON ;


EXEC msdb.dbo.sp_start_job N'Upload_Book_Titles';

I get this error:

Pass-through query with ReturnsRecords property set to True did not return any records.

I have tried including a DoCmd.SetWarnings =False;- this does not work.

I do not see any default setting within my ODBC connection that I can update to resolve this error.

What command can I include in my pass-through query to resolve this error, execute this job, and complete my task?
0
programmher
Asked:
programmher
  • 10
  • 9
1 Solution
 
Gustav BrockCIOCommented:
You have to specify in the properties of the PT Query that it is not returning records.

/gustav
0
 
programmherAuthor Commented:
gustav,

Not sure what you mean.

I run this command from the SQL window and it executes.  It is just running it from Access that gives the error.
0
 
Gustav BrockCIOCommented:
I don't know. You have:

> .. ReturnsRecords property set to True

But:

 sp_start_job N'Upload_Book_Titles'

doesn't look like it is returning records.

/gustav
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
programmherAuthor Commented:
Ok- I understand your comment now.

The job that the Access app is calling inserts records from an Excel spreadsheet into the SQL table.  So, technically, the query is not returning any records back to the Access app - the customer requirement is to insert records from the spreadsheet into the SQL table.

Is there a command I can use to turn off this error message?
0
 
Gustav BrockCIOCommented:
OK, but "returns records" in this context is relative to Access.
So I would try running it with ReturnsRecords property set to False.

/gustav
0
 
programmherAuthor Commented:
How would I do that?

I tried DoCmd.SetWarnings False and that did not work.
0
 
Gustav BrockCIOCommented:
1. Open the query in designview.
2. Set property ReturnsRecords to False
3. Save and close the query.

/gustav
0
 
programmherAuthor Commented:
I now get an error - "Incorrect syntax near ReturnRecords"

My code:

SET NoCount ON ;

EXEC msdb.dbo.sp_start_job N'Upload_Book_Titles';

ReturnsRecords = False;
0
 
Gustav BrockCIOCommented:
Yes. It is not an SQL statement.

It is in the property sheet of the query in Access this setting exists.

/gustav
0
 
programmherAuthor Commented:
ok - thanks.

I will create a separate function for this.

I thought there was a way to test the pass-through query by running it from the access query list.
0
 
Gustav BrockCIOCommented:
There is. You just double-click it and it will run.

/gustav
0
 
programmherAuthor Commented:
When I double click the pass through query, I get the error I included in this incident.
0
 
Gustav BrockCIOCommented:
But did you change the property?

/gustav
0
 
programmherAuthor Commented:
I am creating a function to include the other logic you recommended and change the property.


Did I misunderstand your recommendation?
0
 
Gustav BrockCIOCommented:
I think so. No code involved, just setting the property of the query.

/gustav
0
 
programmherAuthor Commented:
Confused.

This is how my pass-through query looks from the SQL view on MSAccess:

SET NoCount ON ;

EXEC msdb.dbo.sp_start_job N'Upload_Book_Titles';

When I try to run this from MSAccess, it asks for the password (which I enter) then I get the error.

when I include a line to set the ReturnsRecords = False, I still get an error.

Do I need to include something other than "ReturnRecords = False"?
0
 
Gustav BrockCIOCommented:
There is no line or code to insert.

When in designview, go to the Properties' Sheet.
Locate property ReturnsRecords.
Set this to No.
Save the query.

/gustav
0
 
programmherAuthor Commented:
I understand.

Thank you for your patience and for your help.   That resolved the issue.   I was able to execute the SQL job from my Access application.

I am now working on my customer being able to execute this pass-through query using a button that will connect to the SQL database via ODBC connection, log in with the password, and complete the task.

Thank you again!!!
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now