[AccessD] Feeding parameters to SQL Server

Shamil,
Your earlier example of using a parameter table is interesting but you
mention it doesn't work well in a multi-user environment, how would you get
around the multi-user issue?

Jim/Eric,
I dynamically update the pass-through query as the link Eric gave suggests
but I haven't run into any stability issues using this method (that I'm
aware of).  Have you seen any specific issues that you can point me to that
I need to look out for?  I'm using this in an app for our company that
gathers data from other databases to produce invoices to our clients.  This
app is used by most of our more than 400 in-house users.  Granted they won't
all be running the reports that use the pass-through.

Thanks,

Rusty



-----Original Message-----
From: Shamil Salakhetdinov [mailto:shamil at users.mns.ru]
Sent: Wednesday, February 21, 2007 5:41 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Feeding parameters to SQL Server


No problem, Eric, I anyway planned to write a short note here on ADODB and
parameterized stored procedures...

BTW, for parameterized UDFs ADODB can be also used but the issue with access
permissions usually forces to use stored procedures - I mean UDF can't be
used with a table if a user does not have access permissions for this table.
As for stored procedure - it can be used with a table even if a user does
not have access permission to this table - user should only have a
permission to execute this stored procedure.

Here is an example with parameterized UDF and ADODB - parameters's values in
this sample are hardcoded in select expression:

Dim cnn As ADODB.Connection
Dim strCnn As String
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rst As ADODB.Recordset
    
    strCnn = "Provider=sqloledb;Data Source=(local);Initial
Catalog=Northwind;Integrated Security=SSPI;"
    Set cnn = New ADODB.Connection
    cnn.Open strCnn
    cnn.CursorLocation = adUseClient
    
    Set cmd = New ADODB.Command
    cmd.CommandType = adCmdText
    cmd.CommandText = "select * from TotalFreightStats( _
       '1996-07-02', '1996-07-12')"
    cmd.ActiveConnection = cnn.ConnectionString
    
    Set rst = cmd.Execute
    While Not rst.EOF
       Debug.Print rst(0).Value & " - " & rst(1).Value
       rst.MoveNext
    Wend
    
    cnn.Close

--
Shamil
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Eric Barro
Sent: Thursday, February 22, 2007 1:51 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Feeding parameters to SQL Server

Uh..hmmm...Jim and Shamil...I was replying to Colby's question on passing
parameters to SQL which was the original thread.

Someone suggested pass-thru queries somewhere in the thread. I just happened
to reply to the last message in the thread that came from Shamil.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Wednesday, February 21, 2007 2: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
 

**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************