<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2600.0" name=GENERATOR>
<STYLE>@font-face {
font-family: Tahoma;
}
@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.EmailStyle17 {
FONT-WEIGHT: normal; COLOR: windowtext; FONT-STYLE: normal; FONT-FAMILY: Tahoma; TEXT-DECORATION: none
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=346070919-04022003>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff
size=2>Mark,</FONT></SPAN></DIV>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff
size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff
size=2> 'Create command object and assign a valid connection to the
Command.<BR> Dim CmdHSACheck As
ADODB.Command<BR> Set CmdHSACheck = New
ADODB.Command<BR> Set CmdHSACheck.ActiveConnection =
CurrentProject.Connection<BR> <BR> 'Assign
properities of the command object.<BR> CmdHSACheck.CommandType
= adCmdStoredProc<BR> CmdHSACheck.CommandText =
"p_mysproc"<BR> CmdHSACheck.Parameters.Item("@HSA").Value =
Me!lst_HSA.Column(0) <BR> <BR>
'Create Recordset to store the results of the executed Command
object.<BR> Dim rsCheck As
ADODB.Recordset<BR> Set rsCheck =
CmdHSACheck.Execute</FONT></SPAN></DIV>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff size=2>The
sproc you call would be something like:</FONT></SPAN></DIV>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff
size=2> Create procedure
p_mysproc</FONT></SPAN></DIV>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff
size=2> as</FONT></SPAN></DIV>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff
size=2> set nocount on</FONT></SPAN></DIV>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff><FONT
size=2> <FONT face=Tahoma color=#000000>SELECT * FROM tblClaim
ORDER BY ClaimNo</FONT></FONT></FONT></SPAN></DIV>
<DIV><SPAN class=980255918-04022003><FONT face=Arial
color=#0000ff> <FONT face=Tahoma color=#000000
size=2>go</FONT></DIV>
<DIV><FONT size=2><BR></FONT></DIV></FONT></SPAN>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff size=2>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. </FONT></SPAN></DIV>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff
size=2>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.
</FONT></SPAN></DIV>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=980255918-04022003><FONT face=Arial color=#0000ff
size=2>--Susan</FONT></SPAN></DIV></SPAN></FONT></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Mark Boyd
[mailto:MarkBoyd@McBeeAssociates.com]<BR><B>Sent:</B> Monday, February 03,
2003 8:40 AM<BR><B>To:</B> AccessD@databaseadvisors.com<BR><B>Subject:</B>
[AccessD] Creating a stored procedure<BR><BR></FONT></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Cross-posted to SQLServer
list.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Let me start out by stating that
I am totally novice to creating stored procedures.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">I hope someone can help me to
create my first stored proc.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">I have the VBA code complete, but
am having trouble converting to the proc.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">The code is
below:</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">I’m not exactly sure which
sections to include in the proc. Should I include the whole thing?
</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">How do I create a Do Loop in the
proc?</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Any help/direction is greatly
appreciated.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Thanks,</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Mark Boyd</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">Sr. Systems
Analyst</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">McBee Associates,
Inc</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"></SPAN></FONT> </P></DIV></BLOCKQUOTE></BODY></HTML>