[dba-SQLServer]A2K-SQL Stored Procedures

Karen,

The short answer is that yes you can do this through Access.  You need
to execute the sproc from Access passing the parameter.  Here's sample
code:

'Create command object and assign a valid connection to the Command.
    Dim CmdCreate As ADODB.Command
    Set CmdCreate = New ADODB.Command
    Set CmdCreate.ActiveConnection = CurrentProject.Connection
    CmdCreate.CommandType = adCmdStoredProc

'Assign properities of the command object.
            CmdCreate.CommandText = "p_AddProgramRecord"  --put the name
of your sproc here
            CmdCreate.Parameters.Item("@AcadPlan").Value =
cboAcadPlan.Column(0)   -- put the paramter here and what to pass to it
from your form
            
' Execute stored procedure 
            CmdCreate.Execute
'Clean up
            Set CmdCreate = Nothing

You'll have to change your sproc so that it accepts an input parmater.
The thing I'm not sure about is if you can keep your sproc as is with
just replacing the '21%' with the input paramter @myParameter.  I think
with the like syntax you are going to have to build the whole sql as a
string and execute the string at the end.  That would be much less
efficient in terms of sprocs.  

--Susan


-----Original Message-----
From: Nicholson, Karen [mailto:knicholson at gpsx.net] 
Sent: Wednesday, July 30, 2003 9:10 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]A2K-SQL Stored Procedures


What we are doing is switching systems that record incoming signals from
alarms from the B32 environment to a new receiver.  We will be doing
testing in batches with the phone company, taking blocks of our
customers by CS_no (which is the little box that hangs on your wall for
our security systems). The phone company will call, we will change the
"special" field for the specified block of customers to the modified
"newspecial" field.  I have another routine to reverse if all goes
wrong, but this is the stored procedure, with the place that needs to be
changed clearly marked.

I would like my user to be able to run this from Access specifying his
block as needed so I don't have to sit with him at 2:00 in the morning
just to change a piece of code.  
Here is the sproc:

CREATE procedure gps_SpecialSystem as

SET CONCAT_NULL_YIELDS_NULL OFF

truncate table gps_specialsystemdata
insert gps_specialsystemdata(
          system_no,
          cs_no,
          special,
          newspecial,
          length)
   
SELECT system_no, 
               cs_no, 
               special,
               special+'RSI' ,
               len(special) as length
FROM system

WHERE (
/********************************************************************/
/***THIS IS THE CS NUMBER GROUPING TO CHANGE***/
(cs_no LIKE '21%') 
/********************************************************************/
and
(len(special) in (1,2,3) or
special is null))

update  gps_specialsystemdata
set exception=1
where special <> 'SUB'

update system
set system.special=gps_specialsystemdata.newspecial

FROM gps_SpecialSystemData
where 
    gps_SpecialSystemData.system_no = system.system_no AND 
    gps_SpecialSystemData.cs_no = system.cs_no






-----Original Message-----
From: Susan Geller [mailto:sgeller at cce.umn.edu]
Sent: Wednesday, July 30, 2003 8:58 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]A2K-SQL Stored Procedures


Karen,

A few ways to do this depending on what you want to do with the sproc
when it gets the parameter.  Does it just do something on it's own or
does it return a recordset and if so what do you want to do with the
recordset?

--Susan


-----Original Message-----
From: Nicholson, Karen [mailto:knicholson at gpsx.net] 
Sent: Tuesday, July 29, 2003 4:59 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer]A2K-SQL Stored Procedures


Is it possible to build a front-end in A2K to execute stored procedures
in SQL version 7?  I need to allow the user to enter in a parameter that
is in the stored procedure, if it is possible to create a parameter in
SQL that is determined by a combo box in Access.  ???? Thanks. 
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com