[AccessD] Upsize?

Jim,

It sounds like what you're saying is that a SP can be changed the same way a
QueryDef can be changed in Access.  Essentially, you could have an SP that's
simply used as a 'blank' to be changed whenever needed.

Is that correct or are there any caveats that should be remembered?

Thanks!
Dan Waters

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Tuesday, December 20, 2005 6:58 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Upsize?

Hi Michael:

Quicker: When just doing a simple query like an update or delete there is
little difference. When in single user mode there is little to be gained but
when the SQL DB is being hit with multiple requests using a complex set of
queries the gain become obvious. The SP is compiled and cached before use
and every subsequent access is lightning fast.

Flexible: A SP can be a few simple lines or a total mini-application in
itself, with a hundred lines of code calling a number of built-in and
created functions and can even call external executables. It can create
temporary tables (cursors) and views and use them as components to the
calculations. Complex functions like UNIONS, GROUP and ROLL-UP can be added
where required to the mix with little loss in performance.

Easier: If a set of SPs are designed correctly with appropriate support
functions it should be easier to extend functionality with little impact on
your Access FE.

80 Variables from one combo box could be handled this:

<code sample>
...
strComboString = ""

If MyComboBox.ListIndex > -1 Then
 For i = 0 To MyComboBox.ListCount - 1
  If MyComboBox.Selected(i) = True Then
    If Len(Trim(strComboString)) > 0 Then
      strComboString = strComboString & ", " &
Str(MyComboBox.Column(MyComboBox.BoundColumn - 1, i))
    Else
      strComboString = strComboString &
Str(MyComboBox.Column(MyComboBox.BoundColumn - 1, i))
    End If
  End If
 Next i
End If

ProcessComboBoxStrings strComboString
...

Function ProcessComboBoxStrings(strComboString As String)

...

Set objCmd = New ADODB.Command

With objCmd
 .ActiveConnection = gstrConnection 'My server connection string
 .CommandText = "MyCombohandlerSP" 'The appropriate Stored Procedure name 
 .CommandType = adCmdStoredProc 'Type of process ...SP
 .Parameters.Append .CreateParameter("@chvComboString", adVarChar,
dParamInput, len(strComboString), strComboString) 'Parameter string

 .Execute
End With 

....

End Function
</code sample>

Though the code sample is incomplete and mostly from memory you get the
idea. It first routine loops through a combo box list accumulating all the
selected items into a string. Then it passes the string to a SP and it can
be handled from there.

HTH
Jim        

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Michael Maddison
Sent: December 20, 2005 3:21 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Upsize?

Hi Jim,

I think Jürgen and my response is no.
Or at least how?

BTW I disagree with 'They tent(d) to be quicker, have greater flexibility,
easier to work with'
they may be safer but...
quicker - not necessarily, especially if the execution plan needs to change
from execution to execution.
Flexible - whats more flexible then building a string and running that?
Easier - 80 variable cols, 50 potential where statements...  gonna be some
sproc...  what is the char limit for a sproc?

cheers

Michael M


Hi Jürgen:

Can you not use Stored Procedures and just pass parameters? They tent to be
quicker, have greater flexibility, easier to work with and safer.

Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jürgen Welz
Sent: December 19, 2005 9:42 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Upsize?

Michael:

With variable joins, do you point somthing like a list source of search
'hits' to different queries, one query for each join, or how do you handle
variable combinations of joins?  Lets say there is 1 table that may be
joined to 0 to 5 other tables in various combinations, being 32 possible
querydefs.  I've always constructed the SQL in code and was very satisfied
with the performance.  Add another table and you're up to 64 querydefs.  
That's ugly.



Ciao
J|rgen Welz
Edmonton, Alberta
jwelz at hotmail.com





>From: "Michael Maddison" <michael at ddisolutions.com.au>
>
>  Hi J|rgen,
>
>If you go with variable parameters check out the 'With Recompile' option.
>It forces a new execution plan each time the procedure is run and 
>overcomes SQL's 'parameter sniffing' problem.
>
>cheers
>
>Michael Maddison
>
>DDI Solutions Pty Ltd
>michael at ddisolutions.com.au
>Bus: 0260400620
>Mob: 0412620497
>www.ddisolutions.com.au



--
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

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com