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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Pratima PharandeCommented:
r u using sql server or Access ?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Kelvin SparksCommented:
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
TextReportCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UniqueDataAuthor Commented:
I didn't even think of that!!!  Great suggestion.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.