Jim Moss
jim.moss at jlmoss.net
Thu Feb 22 10:02:01 CST 2007
I too have had good luck using pass thrus that aren't slow and haven't
exhibited any instability issues. Databases were converted from Access Jet
to SQL 2005 about 10 months ago, have grown from around 500 and 600 mb to
a little over 2 gb with appx 80 - 90 users.
My pass thrus are built in vba from an idea I got from AccessMonster.com
Jim
> I have had good luck using pass through queries and parameters by using a
> bit of code I got off of Microsoft's site to dynamically create pass
> through
> queries. Instead of writing parameters into the query itself, I create
> the
> query with the parameters I want. Saves a lot of headache and pushes more
> processing off on the server.
> http://support.microsoft.com/kb/112108
>
> Debbie
>
> -----Original Message-----
> From: Jim Lawrence [mailto:accessd at shaw.ca]
> Sent: Wednesday, February 21, 2007 4:48 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Feeding parameters to SQL Server
>
>
> Eric
>
> My recommendation is do not use pass-through queries. They are more
> problem
> than they are worth as they seem to be an easy quick fix to start with but
> end up being slow, awkward and unstable.
>
> I believe this set of processes was designed to punish newbie developers
> scaring them off weaning from a MDB BE. MS should have simply remove that
> whole gambit of functions from Access and never bring them up again in
> polite company.
>
> Now if you want my unvarnished opinion on the subject don't be afraid to
> ask. :-)
>
> Jim
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Eric Barro
> Sent: Wednesday, February 21, 2007 1:47 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Feeding parameters to SQL Server
>
> Unfortunately dynamic parameters and sql pass-thru queries don't play well
> in Access.
>
> http://www.utteraccess.com/forums/showflat.php?Board=82&Number=1361479
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
> Salakhetdinov
> Sent: Wednesday, February 21, 2007 1:00 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Feeding parameters to SQL Server
>
> Hello John,
>
> Let's suppose you use NorthWind sample database and you have to calculate
> total Freight for a Shipper given from/to dates.
>
> I assume you do not use ADPs - if you have used them then you could have
> created
>
> - a stored procedure:
> =====================
>
> CREATE PROCEDURE usp_TotalFreightStats (@fromDate datetime, @toDate
> datetime
> )
> AS
> BEGIN
> select ShipName, Sum(Freight) as FreightTotal
> from Orders
> where Orders.OrderDate between @fromDate and @toDate
> Group by ShipName
> END
>
> - or a UDF
> ===========
> CREATE FUNCTION TotalFreightStats (@fromDate datetime, @toDate datetime )
> RETURNS @TotalFreightStatsTable TABLE
> (
> ShipperName nvarchar(80),
> TotalFreight money
> )
> AS
> BEGIN
> declare @fromDate1 dateTime
> declare @toDate1 dateTime
> INSERT @TotalFreightStatsTable
> select ShipName, Sum(Freight) as FreightTotal
> from Orders
> where Orders.OrderDate between @fromDate and @toDate
> Group by ShipName
> RETURN
> END
>
> And you could have bound your report to this stored procedure or UDF - on
> open this report would have requested from/to dates in parameters input
> dialog.
>
> I assume you use mdb and then you can:
>
> - create table to keep parameters
> ==================================
>
> CREATE TABLE [dbo].[ParametersForUDFs] (
> [fromDate] [datetime] NULL ,
> [toDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
>
> - populate this table with parameters' values (clear in first)
> =============================================================
>
> delete from ParametersForUDFs
> insert into ParametersForUDFs (fromDate, toDate) values ('1996-07-04',
> '1996-07-12')
>
> - create UDF
> ============
>
> CREATE FUNCTION TotalFreightStats1 ( )
> RETURNS @TotalFreightStatsTable TABLE
> (
> ShipperName nvarchar(80),
> TotalFreight money
> )
> AS
> BEGIN
> INSERT @TotalFreightStatsTable
> select ShipName, Sum(Freight) as FreightTotal
> from Orders
> where Orders.OrderDate
> between (select top 1 fromDate from ParametersForUDFs)
> and (select top 1 toDate from ParametersForUDFs)
> Group by ShipName
> RETURN
> END
>
> - use UDF in view
> ==================
>
> CREATE VIEW dbo.vw_TotalFreightStats1
> AS
> SELECT ShipperName, TotalFreight
> FROM dbo.TotalFreightStats1() TotalFreightStats1
>
> ++++++++++++++++++++++++++++++++++++
> - link view to MS Access mdb
> ++++++++++++++++++++++++++++++++++++
>
> Of course that approach will not work well in multi-user environment if
> different users will need different from/to dates...
>
>
> --
> Shamil
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
> Sent: Wednesday, February 21, 2007 10:11 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Feeding parameters to SQL Server
>
> Well... I dunno.
>
> I have a view in SQL Server returning 8 fields, 6K records (currently),
> completely unfiltered, all records in the table. I pull that linked table
> into a query in order to do some preliminary filtering - check date
> between
> a pair of dates and check number not null.
>
> I want the view in sql server to do the "check number not null" for me,
> and
> I know how to do that.
>
> I want the SQL Server to take a pair of parameters - "From Date" and "To
> Date" and return only those records.
>
> If I could do this, then I could get rid of my "base query" in access
> which
> applies these filters, reduce traffic across the wire etc. However even
> if
> I do this, the view is not being requested by a form in XP (for example)
> where some magical thing happens in Access to pass the parameters off to
> SQL
> Server. The linked view will be used immediately in another query which
> joins this view to other tables (on one field - BEID - in the view) in
> order
> to build up a set of data for a report.
>
> So the "parent" query that is using the view will have to "feed" the
> parameters to the linked view. Being a nubee to SQL Server I do not even
> know if what I am attempting to do is possible. I cannot wrap my mind
> around how a query that is using a view can know that the view needs
> parameters, and if it did, what to feed it. If it is possible, I have
> never
> done so obviously, so I don't even have the basics of what to set in
> Access
> to do the parameter passing.
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> <<< tail skipped >>>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.441 / Virus Database: 268.18.3/694 - Release Date: 2/20/2007
> 1:44 PM
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> - JENKENS & GILCHRIST E-MAIL NOTICE - This transmission may be: (1)
> subject
> to the Attorney-Client Privilege, (2) an attorney work product, or (3)
> strictly confidential. If you are not the intended recipient of this
> message, you may not disclose, print, copy or disseminate this
> information.
> If you have received this in error, please reply and notify the sender
> (only) and delete the message. Unauthorized interception of this e-mail is
> a
> violation of federal criminal law.
> This communication does not reflect an intention by the sender or the
> sender's client or principal to conduct a transaction or make any
> agreement
> by electronic means. Nothing contained in this message or in any
> attachment
> shall satisfy the requirements for a writing, and nothing contained herein
> shall constitute a contract or electronic signature under the Electronic
> Signatures in Global and National Commerce Act, any version of the Uniform
> Electronic Transactions Act or any other statute governing electronic
> transactions.
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>