Error message when running Pass-Through query in Access 2002

Question

I have an Access 2002 database in which I'm trying to create a pass-through query to a SQL 2000 database. If I run the query without variables (insert the values I need in the various places I need them), the query runs fine. But if I try to use variables, I get an error message that reads, 'Pass-through query with ReturnsRecords property set to True did not return any records.' I couldn't find anything on this in the MSKB. The variables are declared as follows: Declare @YEAR as int Declare @PERIOD as int Set @YEAR = 2006 Set @PERIOD = 2 Again, the exact same query works if I don't use the variables.

abach · Answer

If you're using the SQL view of the query, copy and paste the entire code and I'll look at it. The reason I asked was there is no variable type int, only Integer in VBA

abach · Answer

Are the variables declared in VBA or in SQL?

jjkatz · Answer

I'm declaring them in the query itself, so I guess that's SQL.

jjkatz · Answer

The query calls a couple of functions, and the entire query is below:

Declare @YEAR as int
Declare @PERIOD as int
Set @YEAR = 2006
Set @PERIOD = 2

select zone_id, zone_name, GoLivePeriod = dbo.getperiod (go_live), GoLiveYear = dbo.GetPeriodYear (go_live)
from zone_lookup
where Go_Live >= dbo.GetPeriodBeginDate( @YEAR, @PERIOD ) AND Go_Live < DATEADD(day, 1, dbo.GetPeriodEndDate( @YEAR, @PERIOD ))

abach · Answer

Looks like you're using an SQL server query. I don't know anything about them, only Access.

jjkatz · Answer

I found a workaround.  If I create stored procedures in SQL and then use the Access pass-through queries to call the stored procedures, they work.  I'd still be curious to know why my first method failed though.

Productivity Software

Error message when running Pass-Through query in Access 2002

Was this post helpful?