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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO SQL Query, Possible to leave the Recordset open outside its Su
I would probably move the recordset to an array that has been declared
Public or Private at module level. This is done with something like: arrMain = rstData.GetRows Then use this array for all further queries. It is easy to make sub arrays out of this main array, but not sure that is neccessary. RBS "SaeOngJeeMa" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO SQL Query, Possible to leave the Recordset open outside it
Great idea RBS! I actually don't know how to query a global array. The array
would be something like gvarDataList(1 to 1200, 1 to 90). Would you give the second dimension (columns) some sort of field name and then you can generate SQL queries in code? What would the query look like? Thanks. -- Best Regards, Dean "RB Smissaert" wrote: I would probably move the recordset to an array that has been declared Public or Private at module level. This is done with something like: arrMain = rstData.GetRows Then use this array for all further queries. It is easy to make sub arrays out of this main array, but not sure that is neccessary. RBS "SaeOngJeeMa" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO SQL Query, Possible to leave the Recordset open outside it
I actually don't know how to query a global array
You can't run SQL on an array if that is what you meant, but of course you can use the data in the array to build SQL queries. Would you give the second dimension (columns) some sort of field name You could do, but not sure it is useful. It will all depend on what exactly you need to do with that array. You will need to post some more code. RBS "SaeOngJeeMa" wrote in message ... Great idea RBS! I actually don't know how to query a global array. The array would be something like gvarDataList(1 to 1200, 1 to 90). Would you give the second dimension (columns) some sort of field name and then you can generate SQL queries in code? What would the query look like? Thanks. -- Best Regards, Dean "RB Smissaert" wrote: I would probably move the recordset to an array that has been declared Public or Private at module level. This is done with something like: arrMain = rstData.GetRows Then use this array for all further queries. It is easy to make sub arrays out of this main array, but not sure that is neccessary. RBS "SaeOngJeeMa" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO SQL Query, Possible to leave the Recordset open outside it
I took RBS' idea about using a Public array and ran with it a bit. I
discovered that I could declare global (public) Connection and RecordSet variables, open them up on the Workbook Open event and leave them open for use indefinitely. I've only been able to do a little testing but it's working so far .... Best Regards, Dean "RB Smissaert" wrote: I would probably move the recordset to an array that has been declared Public or Private at module level. This is done with something like: arrMain = rstData.GetRows Then use this array for all further queries. It is easy to make sub arrays out of this main array, but not sure that is neccessary. RBS "SaeOngJeeMa" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO SQL Query, Possible to leave the Recordset open outside it
That should work fine as well. I suggested a Public array as I thought
that that would be a bit faster as you have to go through it multiple times. RBS "SaeOngJeeMa" wrote in message ... I took RBS' idea about using a Public array and ran with it a bit. I discovered that I could declare global (public) Connection and RecordSet variables, open them up on the Workbook Open event and leave them open for use indefinitely. I've only been able to do a little testing but it's working so far .... Best Regards, Dean "RB Smissaert" wrote: I would probably move the recordset to an array that has been declared Public or Private at module level. This is done with something like: arrMain = rstData.GetRows Then use this array for all further queries. It is easy to make sub arrays out of this main array, but not sure that is neccessary. RBS "SaeOngJeeMa" wrote in message ... 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 |
Reply |
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 |