Pass through query with declare doesn't return records

I need a pass through query (to a SQL table) in where I have declared a variable.  I can run the query in Query Analyzer with a record returning, however as a pass through I get the message like 'Pass Through Query with ReturnRecords set to True did not return any records'.
I know I could do this as a StoredProcedure and then have the pass through 'exec sp_Name paramValue' but the sql statement is going to be generated in VBA depending on what is selected on a form.

The attached code is not the full statement I need, just testing if declare's worked
declare @ProjectID int
select  @ProjectID = 6233
 
Select * From Project Where ProjectID = @ProjectID

Open in new window

LVL 7
UniqueDataAsked:
Who is Participating?
 
TextReportConnect With a Mentor Commented:
If you have the @ProjectID in your SQL string you could always do a REPLACE() on it

strSQL = "Select * From Project Where ProjectID = @ProjectID"
strSQL = REPLACE(strSQL, "@ProjectID", "6233")

Cheers, Andrew

0
 
Pratima PharandeCommented:
declare @ProjectID int
set @ProjectID = 6233
 
Select * From Project Where ProjectID = @ProjectID
0
 
UniqueDataAuthor Commented:
Nope, set is getting the same result as Select
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Pratima PharandeCommented:
r u using sql server or Access ?
0
 
Kelvin SparksConnect With a Mentor Commented:
A pass through query is the SQL equivalent of an Access query. I cannot duplicate the functionality of a stored proc.

You could use VBA to set the value of ProjectID then use the pass through query to execure the select statement (but you can't have a parameter in it.) Set the ProjectID = to whatever. Then create a vba string being "Select * FROM Project Where ProjectID = " & ProjectID

Then use set the passthrough query SQL as being equal to the string created, then open the query
0
 
TextReportCommented:
It looks to me that in Access 2000 it is getting to the first SELECT (or SET if you assign it this way) and is expecting that as the records returned.
My exampl below returns OK EmployeeID = 1 but if I put the SET above the first SELECT then I get the same Error and if I remove the set completely then I get the records where EmployeeID = 1

This looks like you will need to use VBA to code round this limitation

CurrentDb.QueryDefs("MyQuery").SQL = "Select * From Project Where ProjectID = " & lngProjectID

Cheers, Andrew
DECLARE @ProjectID int
Select * From Employees Where EmployeeID = 1
SET  @ProjectID = 1
Select * From Employees Where EmployeeID = 2

Open in new window

0
 
UniqueDataAuthor Commented:
Thanks all.  Although I was going to use VBA to set the '@Project = 1' I didn't want to have to re-write the complete sql statement as it is lengthy.  But I guess there is no way as TextReport stated, Access is hitting the first select and going no further.
0
 
UniqueDataAuthor Commented:
I didn't even think of that!!!  Great suggestion.
0
All Courses

From novice to tech pro — start learning today.