• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

Access, SQL Server

I'm trying to execute a store procedure (just simple like 'TRUNCATE TABLE tablename') through a pass through in Access, but it give me a run-time error '3325' saying 'Pass-through query with ReturnsRecords property set to True did not return any records', please let me know why
0
qnryat
Asked:
qnryat
  • 5
  • 5
2 Solutions
 
Leigh PurvisDatabase DeveloperCommented:
Because an action such as 'TRUNCATE TABLE' doesn't return any records.
You should set your Returns Records property as False in the query properties sheet (assuming it's a persisted querydef and not just created on the fly).
0
 
bgrandjeanCommented:
Open the query in design mode and open the properties window.  Set the Returns Records property to No.  Access generally expects a query to return rows and this setting property will tell it not to expect rows.
0
 
qnryatAuthor Commented:
I set the Returns REcords to no, now I get the error with server log in, here's how I call the sp with a
docmd.openquery passthroughqueryname
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.

 
Leigh PurvisDatabase DeveloperCommented:
You still get the error if you open the query by double clicking it in the DB window / Nav Pain?
A problem with the server login is just that.
You need to make sure you have valid credentials assigned.
What is your query's Connect property (obfuscating any sensitive details as required of course - i.e. don't give us all your password ;-).
0
 
qnryatAuthor Commented:
yes I still get the error by double clicking on it in the db window. No password require, I was able to open the Sql table in the db window
0
 
Leigh PurvisDatabase DeveloperCommented:
And does the passthrough have the same Connect property as that linked table?
0
 
qnryatAuthor Commented:
How do I set the connect property in the query so that it will point to the right datasource without having it popup to select the datasource?
0
 
Leigh PurvisDatabase DeveloperCommented:
What is your Connect property currently??
0
 
qnryatAuthor Commented:
It was just "ODBC;" then I added PSF_GEN so "ODBC;PSF_GEN" which is the name of the server, and it's working now. But if I don't want to create a passthrough query, but instead having a docmd, what would it be?
DoCmd.RunSQL ("Truncate Table....) or how do I specify the connnection in the docmd statement?
0
 
Leigh PurvisDatabase DeveloperCommented:
You're sending a command to the server.
You can't trivially just execute that locally - that's exactly what passthroughs are for.
If you enter a valid Connect property you won't be prompted for anything.

If you want to execute commands in a more ad-hoc manner then you can use a variation of the theme in this recent thread - but it'll take a bit of VBA understanding.
0
 
qnryatAuthor Commented:
I got it. Thanks.
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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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