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

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" <(E-Mail Removed)> wrote in message
news:6B713879-E070-4DC6-BFCC-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:6B713879-E070-4DC6-BFCC-(E-Mail Removed)...
> > 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
>
>

> 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" <(E-Mail Removed)> wrote in message
news34B87D3-5AB9-4D2C-B103-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:6B713879-E070-4DC6-BFCC-(E-Mail Removed)...
>> > 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
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:6B713879-E070-4DC6-BFCC-(E-Mail Removed)...
> > 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
>
>

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" <(E-Mail Removed)> wrote in message
news:43C8C446-4C6A-4E9E-BE74-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
>> news:6B713879-E070-4DC6-BFCC-(E-Mail Removed)...
>> > 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
>>
>>