Solved

Access, SQL Server

Posted on 2010-08-26
11
536 Views
Last Modified: 2012-05-10
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
Comment
Question by:qnryat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 500 total points
ID: 33534793
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
 
LVL 2

Expert Comment

by:bgrandjean
ID: 33534797
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
 

Author Comment

by:qnryat
ID: 33534862
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 500 total points
ID: 33534893
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
 

Author Comment

by:qnryat
ID: 33535052
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33535446
And does the passthrough have the same Connect property as that linked table?
0
 

Author Comment

by:qnryat
ID: 33535575
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33535643
What is your Connect property currently??
0
 

Author Comment

by:qnryat
ID: 33535772
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33535828
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
 

Author Comment

by:qnryat
ID: 33535939
I got it. Thanks.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

696 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