Solved

Access, SQL Server

Posted on 2010-08-26
11
535 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

860 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