Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO query eating CPU power
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO query eating CPU power
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop baby eating in CHINA | Charts and Charting in Excel | |||
Stop baby eating in CHINA | New Users to Excel | |||
Stop baby eating in CHINA | Excel Worksheet Functions | |||
Stop baby eating in CHINA | Excel Programming | |||
Macro-eating monster | Excel Programming |