Jim Lawrence
accessd at shaw.ca
Fri Feb 23 14:45:20 CST 2007
Hi Debbie:
I had these problems with Access 2000; maybe in later version this has been
resolved. At the time before migrating the clients from a pass-through model
it was taking over 30 minutes to produce. After the re-design month end
reports would start in less than a few minutes...
After that I have never looked back.
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Elam, Debbie
Sent: Friday, February 23, 2007 6:50 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Feeding parameters to SQL Server
Perhaps it is an environment issue. I have not had a performance hit off
even large queries for reports.
Debbie
-----Original Message-----
From: Jim Lawrence [mailto:accessd at shaw.ca]
Sent: Thursday, February 22, 2007 6:16 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Feeding parameters to SQL Server
Hi Debbie:
Have you not found that pass-through queries that do a single record update
or delete are not adversely affected but when you tried selecting 5000
records, for a report....that different story.... the pipe is just too
narrow. Add a heavy multi-user environment, month-end and suddenly things
start to unhinge.
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Elam, Debbie
Sent: Thursday, February 22, 2007 7:25 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Feeding parameters to SQL Server
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
--
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