Hi, I have a workbook that needs to do multiple reads and writes from/to the
same worksheet called "DataList" There's no external data- it's all contained
in the same workbook. Everything works fine except it's slow. There are
various functions and subs that need to open and close (queries of) that same
recordset. Pasted below is a stripped down example of how I'm opening and
closing the recordsets. The speed problem is being caused by the rstData.Open
operation and since that operation needs to be done repeatedly the overall
performance of the worksheet is a problem. Here are my questions:

1. Is there a way to open the entire recordset in Worksheets("DataList")
when the user opens the Workbook and then leave it open until the user closes
the Workbook?

2. If #1 above IS possible then is there a simple way to query or filter
that recordset to generate sub-recordsets that I can work with in the various
functions and subs?

3.If #1 above IS NOT possible then is there a way to improve the speed of my
frequent recordset opens and closes?

Thanks in advance for any help you can provide.
Dean

public Sub openrecordsetexample(varChangeValue as Variant)
Dim rstData As ADODB.Recordset
Dim strSQL As String
Dim strConnection As String

'build the SQL string
strSQL = "SELECT [DataList$].[Customer] FROM [DataList$] WHERE " & _
"((([DataList$].[Customer]) Like 'XYZ Co.') AND (([DataList$].[Program]) "
& _
"Like 'TV Converter'));"


'open the connection
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & _
ThisWorkbook.Name & ";" & "Extended Properties=Excel 8.0;"
Set rstData = New ADODB.Recordset
rstData.Open strSQL, strConnection, adOpenStatic, _
adLockOptimistic, adCmdText

'test to make sure records were returned
If rstData.RecordCount = 0 Then
GoTo Exit_applyChangeToDataList
End If

'make the changes to the database data
rstData.MoveFirst
Do While Not (rstData.EOF)
rstData.Fields.Item(0).Value = varChangeValue
applyChangeToDataList = 1
rstData.MoveNext
Loop

'Clean up object and control variables
rstData.Close
Set rstData = Nothing

End Sub