Hi,

I am using excel (ADO Connection) to call a stored procedure on a DSN server mutliple times. Right now I am connecting/disconnecting several times in a row. So i have two questions. 1) Is there a way to keep a connection open or reuse it and feed it multiple sets of parameters using only one connection. 2) How can I optimize this procedure to reduce server impact but increase speed. The code I am currently using is below.

Thanks in advance.

Sam


Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
Set WSP = Worksheets("Sheet1")
ProjectionName = Workbooks("UtilityNew.xla").Worksheets("Sheet1").Cells(1, 3).Value
ScenarioName = Workbooks("UtilityNew.xla").Worksheets("Sheet1").Cells(1, 4).Value
WSP.Cells.Clear
con.Open "DSN=BOOPSPROD05;SRVR=BOOPSPROD05;UID=FAB1PLAN;password=FAB1PLAN" '< --Connection String
cmd.ActiveConnection = con
cmd.CommandText = "sel_reqd_moves_by_WS_grp '" & WSGroup & "','Capacity','FAB 3','" & ProjectionName & "','Capacity','none'" & Timeline & ",'" & ScenarioName & "','Capacity','" & Weekly & "'"
Set rs = cmd
Execute(, , adCmdStoredProc)
WSP.Activate
If rs.EOF = False Then WSP.Cells(1, 1).CopyFromRecordset rs
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing