Access 2K + SQL Server: Data Retrieval Q

First, if I'm posting this in the wrong newsgroup, please let me know
and I'll re-post elsewhere.
Trying to get SQL Server 2K data into an Access 2K front-end application
(we're upsizing our back-end DB's to SQL Server, want to keep mdb
front-ends). To limit the returned recordsets for both forms and
reports, we want to pass parameters (like customerid or orderid) to SQL
Server and return just the data needed.
What is the "best practice" way to do this? A stored procedure (that
accepts the parameter) linked to a view, or a user-defined function? Do
both allow data updates? Or, is one better for a form (data updates) and
the other for a report (view only)?
Sorry for the basic question. Been developing in Access for a few
years, but I'm REALLY new to SQL Server.
Thanks,
JLH
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Using linked mdb FE's can be very efficient if you code it right. You
need to create forms with restrictive WHERE clauses that load data for
editing, preferably one record at a time (tables are find as long as
you don't base forms on the entire table). You can also take advantage
of local storage for caching static data for use in listboxes, etc.
Use stored procedures with pass-through queries for reports. You can
base a report directly on a pass-through query that executes a stored
procedure.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Tue, 06 Jul 2004 06:25:39 -0700, Jeffery Hawkins
<jhawkins@.villagepress.com> wrote:

>First, if I'm posting this in the wrong newsgroup, please let me know
>and I'll re-post elsewhere.
>Trying to get SQL Server 2K data into an Access 2K front-end application
>(we're upsizing our back-end DB's to SQL Server, want to keep mdb
>front-ends). To limit the returned recordsets for both forms and
>reports, we want to pass parameters (like customerid or orderid) to SQL
>Server and return just the data needed.
>What is the "best practice" way to do this? A stored procedure (that
>accepts the parameter) linked to a view, or a user-defined function? Do
>both allow data updates? Or, is one better for a form (data updates) and
>the other for a report (view only)?
>Sorry for the basic question. Been developing in Access for a few
>years, but I'm REALLY new to SQL Server.
>Thanks,
>JLH
>
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!|||Thanks, Mary. By linked FE's are you meaning that we should link the
SQL Server tables directly into the Access FE, and not pass parameters
back to SQL server SP's for data retrieval? (Great book, by the way --
as I've told you before!).
Thanks,
JLH
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||No, not at all -- absolutely you *should* use parameterized stored
procedures wherever possible. You can supply parameter values in code
to the .SQL property of a pass-through query (using DAO) so that it
would look something like:
myPSQ.SQL = "exec myproc " & varVal1 & ", '" & strVal & "'"
--Mary
On Thu, 08 Jul 2004 10:35:11 -0700, Jeffery Hawkins
<jhawkins@.villagepress.com> wrote:

>Thanks, Mary. By linked FE's are you meaning that we should link the
>SQL Server tables directly into the Access FE, and not pass parameters
>back to SQL server SP's for data retrieval? (Great book, by the way --
>as I've told you before!).
>Thanks,
>JLH
>
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!