Hi,

I'm using a macro to query a database for some data which is then placed
onto the worksheet. I use an ADO connection to achieve this, and I do
remember to close the connections at the end of the function.

The problem is that as soon as opened this excel worksheet is eating 100% of
the CPU (or 50% of my dual core). Initially I thought it was my macro, but
disabling the macro (and even removing the macro code all together) did not
help at all.

In the end, via trial an error, I realised that deleting the actual results
obtained using this code causes the problem...and even if the macro code
itself is removed, the CPU is still used. Only deleting the actual data
retreived frees up the CPU.

Any idea what is behind this?

This is the actual ADO function

Private Sub doADO(ByVal refCell As Range, ByVal destCell As Range)

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String

Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;"
& _
"Trusted_Connection=True;" & _
"Initial Catalog=SQL_DATABASE;" & _
"Data Source=SQL_SERVER"

stSQL = "SELECT DISTINCT date1, date2 " & _
"FROM SQL_DB_TABLE AS GD " & _
"WHERE GD.ref='" & refCell.Value & "' ORDER BY GD.date1 DESC"

Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset

With cnt
.CursorLocation = adUseClient
.Open stADO
.CommandTimeout = 0
Set rst = .Execute(stSQL)

destCell.CopyFromRecordset rst, 1

End With

rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

End Sub

Thanks!

Awsome, this is the first time there is a solution to my question at this
forum... except for the fact that I am posting both... :-P

Anyway, problem was the background refreshing of the query, so an easy
fix... a code to remove any queries from the worksheet, leaving behind any
previously fetched data.

Dim qtb As QueryTable
For Each qtb In ActiveSheet.QueryTables
qtb.Delete
Next

"Strobo" wrote:

> Hi,
>
> I'm using a macro to query a database for some data which is then placed
> onto the worksheet. I use an ADO connection to achieve this, and I do
> remember to close the connections at the end of the function.
>
> The problem is that as soon as opened this excel worksheet is eating 100% of
> the CPU (or 50% of my dual core). Initially I thought it was my macro, but
> disabling the macro (and even removing the macro code all together) did not
> help at all.
>
> In the end, via trial an error, I realised that deleting the actual results
> obtained using this code causes the problem...and even if the macro code
> itself is removed, the CPU is still used. Only deleting the actual data
> retreived frees up the CPU.
>
> Any idea what is behind this?
>
> This is the actual ADO function
>
> Private Sub doADO(ByVal refCell As Range, ByVal destCell As Range)
>
> Dim cnt As ADODB.Connection
> Dim rst As ADODB.Recordset
> Dim stSQL As String
>
> Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;"
> & _
> "Trusted_Connection=True;" & _
> "Initial Catalog=SQL_DATABASE;" & _
> "Data Source=SQL_SERVER"
>
> stSQL = "SELECT DISTINCT date1, date2 " & _
> "FROM SQL_DB_TABLE AS GD " & _
> "WHERE GD.ref='" & refCell.Value & "' ORDER BY GD.date1 DESC"
>
> Set cnt = New ADODB.Connection
> Set rst = New ADODB.Recordset
>
> With cnt
> .CursorLocation = adUseClient
> .Open stADO
> .CommandTimeout = 0
> Set rst = .Execute(stSQL)
>
> destCell.CopyFromRecordset rst, 1
>
> End With
>
> rst.Close
> cnt.Close
> Set rst = Nothing
> Set cnt = Nothing
>
> End Sub
>
> Thanks!