ADO SQL Query, Possible to leave the Recordset open outside its Su

[SaeOngJeeMa]

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

 

[RB Smissaert]

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]

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

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

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

 

[RB Smissaert]

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

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

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

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

 

[SaeOngJeeMa]

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

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

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

 

[RB Smissaert]

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

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

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

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