Pass Thru queries

[Guest]

I have a situation where we are needing to migrate a legacy application with
reports (written in Access) into a SQL Server environment. We do not yet
want to convert all the reports yet into Reporting Services so we still will
be using the current reports that have been written in Access (2003). One of
the reports contains about 250 fields where each needs it's own SQL query.

1) Can a stored procedure be written for each field and can Access use these
stored procs
2) Can I use Pass-Thru queries and is the database limited to how many it
can have?
3) Our other solution is to re-build all of these reports in Reporting
Services and then deloy that solution with our next build, which we may not
have time for.

Thanks for the information

 

[Douglas J. Steele]

A stored procedure for each field doesn't really make sense. What you
probably want is a stored procedure that returns a recordset, and then use
that recordset as the source for your report.

There's no limit per se as the number of pass-through queries you can have
in a database. Access doesn't treat pass-through queries any differently
than any other object. They simply contribute to the object count limit of
32,768.

 

[Guest]

I am not too familiar with using stored procs (just simple selects). How
would I set up a stored proc to read in 250 different values and save it to a
recordset? Sorry for my ignorace, but your solution does seem the best.

Thanks

 

[Aaron Kempf]

I'd reccomend using Access Data Projects

using MDB for SQL Server is just worthless and pointless and much more
complex than ADP

-Aaron
Certified DBA

 

[Douglas J. Steele]

Are you saying you have 250 different controls on your form, you provide a
value for all (or some) of those controls, and you want the stored procedure
to use those values for its execution? To be honest, I'm not sure whether
there's a limit for how many parameters can be passed to an SP.

 

[Aaron Kempf]

limits in MDB-- like the column limit specifically?

that is one of the reasons I gave up on MDB a decade ago

 

[Douglas J. Steele]

I specifically said "To be honest, I'm not sure whether there's a limit for
how many parameters can be passed to an SP".

SP means SQL Server, not MDB.