Creating SQL Connection

[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

 

[Guest]

Stephen

You can create a Public connection object for the module and it will stay connected until you close the object

Place the declaration for the connection in the general declartions area

Option CompareDatabas
Option Explici

dim cn as adodb.connectio

Then in the OnLoad or OnOpen procedure you can create the object
Set cn=new adodb.connectio

or, if you do not want to set the object in the procedure, you could alway do the following in the general declaration area

dim cn as new adodb.connection

I would go with setting the object in the OnLoad procedure. Make sure to close the object and set it to nothing when in the OnClose event.