Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO SQL Query, Possible to leave the Recordset open outside its Su
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I toggle between 2 open excel files and leave both open | Excel Discussion (Misc queries) | |||
query a recordset | Excel Discussion (Misc queries) | |||
How to open Access recordset via Query in Excel VBA??? | Excel Programming | |||
Web query results in a recordset? | Excel Programming | |||
How query a disconnected ADO recordset - possible? | Excel Programming |