SQL Connections

[Stephen]

I have an .ADP and have no difficulty creatin the SQL conenction

My quesiton is "How often do I need to do this?"

For example, I have the following code:

Function fctBreakDownOrder(OrderID As Integer)
Dim rsOrderItems As ADODB.Recordset

Set conn = ADO_Connect
Set rsOrderItems = conn.Execute("spGetDistributionItemList @OrderID=" &
OrderID)
Do While Not rsOrderItems.EOF
if rsOrderItems!CategoryID = 1 then
[run insert function]
end if
[run delete commad]
rsOrderItems.MoveNext
Loop

In the above example, I have created a "conn" conenction. In each funciton,
that is called, there are insert, and delete commands. Do I need to create
that connection before every line of code that queries the database, inserts
a record, deletes a record, or calls a stored procedure? If not, do I just
need to create the conn conenction at teh beginning of the module and will
it carry over to any other commands in the module?

-Stephen

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


If you are using the same connection thru-out your application, then you
probably can use the variable ADO_Connect in your functions/subs instead
of setting conn ADO_Connect. E.g.:

ADO_Connect.Execute strSQL ,, adCmdText

strSQL = "UPDATE TableName SET Flag = True WHERE ID = 2"
ADO_Connect.Execute strSQL,,adCmdText

strSQL = "SELECT * FROM TableName WHERE ID IN (2, 3)"
Set rs = ADO_Connect.Execute(strSQL, , adCmdText)

The above can be done in order w/o resetting the connection object
"ADO_Connect."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJW98IechKqOuFEgEQJRLgCg4GT8ZWRKVFHY3rooJ92S7sBrAskAnRpw
QRt51vR8i1tdHd10wCoCdfY8
=1sv4
-----END PGP SIGNATURE-----