[AccessD] Creating a stored procedure

Mark,
 
Basically I have to agree with Arthur that you need to rethink your approach to maximize the benefits of sprocs.  That said, you can convert to sprocs without changing your thinking.  You can execute a stored procedure for each of your sql statements and open it in a record set.
 
 'Create command object and assign a valid connection to the Command.
    Dim CmdHSACheck As ADODB.Command
    Set CmdHSACheck = New ADODB.Command
    Set CmdHSACheck.ActiveConnection = CurrentProject.Connection
               
 'Assign properities of the command object.
    CmdHSACheck.CommandType = adCmdStoredProc
    CmdHSACheck.CommandText = "p_mysproc"
    CmdHSACheck.Parameters.Item("@HSA").Value = Me!lst_HSA.Column(0)    
    
  'Create Recordset to store the results of the executed Command object.
        Dim rsCheck As ADODB.Recordset
        Set rsCheck = CmdHSACheck.Execute
 
The sproc you call would be something like:
 
       Create procedure p_mysproc
    as
    set nocount on
    SELECT * FROM tblClaim ORDER BY ClaimNo
    go


That gives you the recordset to work with.  But, it's unbound.  In order to update a row in the recordset, you need to have a second sproc that is an update sproc.  You'll have to execute it for each row that you are updating passing the parameters needed.  
 
Given how much you have going on in the code, I think you can probably see how a change in approach is going to be better for you.  
 
--Susan

-----Original Message-----
From: Mark Boyd [mailto:MarkBoyd at McBeeAssociates.com]
Sent: Monday, February 03, 2003 8:40 AM
To: AccessD at databaseadvisors.com
Subject: [AccessD] Creating a stored procedure



Cross-posted to SQLServer list.

 

Let me start out by stating that I am totally novice to creating stored procedures.

I hope someone can help me to create my first stored proc.

 

I have the VBA code complete, but am having trouble converting to the proc.

The code is below:

 

I'm not exactly sure which sections to include in the proc.  Should I include the whole thing?  

How do I create a Do Loop in the proc?

 

Any help/direction is greatly appreciated.

Thanks,

Mark Boyd

Sr. Systems Analyst

McBee Associates, Inc

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030204/f037afd8/attachment-0002.html>