Re: Opening Stored Procedure...copying to local table...any faster way to do this?

Here's what I did. I used the ADO Getrows() method. I got the three
recordsets in to three local arrays and populated the tables from
there. I also realized a querydef I was using really didn't need to be
used, as I could cull the data from one of the tables populated from
the stored procedure. When the connection was closed and I started to
fill the tables from the arrays, it was almost instantaneous (mind you,
it's only about 150 rows at most, so YMMV). This is the code I used:

Sub testingarraypull()
Dim Agency As String
Dim rs As DAO.Recordset
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs2 As New ADODB.Recordset
Dim rs3 As DAO.Recordset
Dim rowcounter As Long
Dim style As String
Dim i As Long
Dim filterstring As String
Dim xlObj As Object
Dim xl*** As Excel.Work***
Dim xlbk As Excel.Workbook
Dim agencybook As Variant
Dim agencybook2 As Variant
Dim agencybook3 As Variant
Dim salesmanbook As Variant
Dim j As Long
CurrentDb.Execute "DELETE FROM SalesSummaryTable"
'Set qdf = CurrentDb.QueryDefs("PullAgencySummaries")
'qdf.Parameters(0) = Me.cboAgency
'qdf.Execute
'CurrentDb.QueryDefs("PullAgencySummaries").Execute
CurrentDb.Execute "DELETE FROM tblForWork***"
CurrentDb.Execute "DELETE FROM tblSalesmanBooked"
Set rs3 = CurrentDb.OpenRecordset("tblForWork***")
'Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=SQLOLEDB.1;Password=brent;Persist
Security Info=True;User ID=sa;Initial Catalog=ee8idbbd;Data
Source=JOMAR"
conn.Open
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "bdg_proc_BookingsByStyleAgency"
cmd.Parameters(1) = "DHS"
Set rs2 = cmd.Execute
agencybook = rs2.GetRows
conn.Close
Debug.Print "BY AGENCY DHS", UBound(agencybook), UBound(agencybook,
2)
conn.ConnectionString = "Provider=SQLOLEDB.1;Password=brent;Persist
Security Info=True;User ID=sa;Initial Catalog=ee8idbbd;Data
Source=JOMAR"
conn.Open
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "bdg_proc_BookingsByStyleAgency"
cmd.Parameters(1) = "KY"
Set rs2 = cmd.Execute
agencybook2 = rs2.GetRows
conn.Close


Set conn = Nothing
Debug.Print "BY AGENCY KY", UBound(agencybook2),
UBound(agencybook2, 2)

conn.ConnectionString = "Provider=SQLOLEDB.1;Password=brent;Persist
Security Info=True;User ID=sa;Initial Catalog=ee8idbbd;Data
Source=JOMAR"
conn.Open
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "bdg_proc_BookingsBySalesmanSelect"
cmd.Parameters(1) = "DHS"
Set rs2 = cmd.Execute
salesmanbook = rs2.GetRows
conn.Close


Set conn = Nothing
Debug.Print "BY SALESMAN DHS", UBound(salesmanbook),
UBound(salesmanbook, 2)
For i = 0 To UBound(agencybook, 2)
rs3.AddNew
rs3!style = agencybook(0, i)
rs3!Agency = agencybook(4, i)
rs3![04booked] = agencybook(1, i)
rs3![05booked] = agencybook(2, i)
rs3![06booked] = agencybook(3, i)
rs3.Update
Next
rs3.Index = "PrimaryKey"
For i = 0 To UBound(agencybook2, 2)
rs3.Seek "=", "DHS", agencybook2(0, i)
If rs3.NoMatch Then
rs3.AddNew
rs3!style = agencybook2(0, i)
rs3!Agency = "DHS"
rs3![04booked] = agencybook2(1, i)
rs3![05booked] = agencybook2(2, i)
rs3![06booked] = agencybook2(3, i)
rs3.Update
Else
rs3.Edit
rs3![04booked] = rs3![04booked] + agencybook2(1, i)
rs3![05booked] = rs3![05booked] + agencybook2(2, i)
rs3![06booked] = rs3![06booked] + agencybook2(3, i)
rs3.Update
End If
Next
End Sub

.