Correct approach for clearing memory with repeated Database queries

[simonboland]

I'm using a VBA macro to repeatedly pull data from a MySQL database
using an ODBC connector. This is the main subroutine with the query
(note I've removed various things from the macro such as server,
username, password and the exact query which aren't important)

Sub DataImport()
Dim rRng As Excel.Range
Sheets("Imported Data").Select
Set rRng = Range("A1:F1000")

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DATABASE= ;DRIVER={MySQL ODBC 3.51
Driver};OPTION=0;;PORT=0;SERVER= ;UID= ;PASSWORD= " _
, Destination:=rRng)
.CommandText = Array( _
"SELECT * FROM table WHERE " _
)
.Name = "Query from Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Set rRng = Nothing

End Sub


The data gets dumped in the worksheet Imported Data.

I have another macro with a loop to run this query with different data
each time. The loop runs through about 1000 cases and each time I do
my analysis in another worksheet. Before I do a new import, I call
Selection.ClearContents and Selection.Clear to remove the previous
data.

The problem is that when I click on the imported data worksheek, my
CPU usage goes to 100%. I noticed that in the Excel Name Box
the .Name field "Query from Database" shows up numerous times.

My questions are:
1) Why does the query name show up in the Excel Name Box?
2) Is there something I'm not doing to clear the previous data or
release the memory?
3) Is there some way to reduce the CPU usage when I click on the
imported data worksheet?

Thanks.

 

[Mike Fogleman]

There are 2 things you need to try. First get this add-in Find Links:
http://www.bmsltd.ie/MVP/Default.htm
Run it on your spreadsheet. Just have it make a list without message or
deletion. I'm guessing you have hundreds of old links associated with your
Imported Data worksheet. If true, then links from old queries are piling up
with each import. Run FindLinks again and have it delete them this time.
To keep it from happening again, don't just clear the cells in your imported
data sheet. Before you do a new import, delete the worksheet and then create
a new sheet with the same name, then run your query. Once you delete a
sheet, all the old query links are deleted with it so they won't accumulate.

Mike F

 

[Mike Fogleman]

Another thing you can try is one more querytable property after .Refresh:
..MaintainConnection = False

Mike Fogleman

 

[simonboland]

Thanks for both suggestions a couple of weeks back for this original
problem (See below). This involved the correct way to clear data when
repeatedly importing data from a database with a macro.

I've experimented with different options to remove worksheets before
the query is executed. The major problem with deleting a worksheet
before each import is that I have other cell formulas which depend on
the values in the worksheet being deleted. The result is I get !REF
errors.

I was wondering if there is a workaround for this problem?

 

[Mike Fogleman]

Name your new sheet the same as your deleted sheet.

Application.DisplayAlerts = False
Worksheets("Data").Delete
Application.DisplayAlerts = True
Worksheets.Add.Name = "Data"

Mike F

 

[simonboland]

I tried this and I still get #REF! error for the cell formulas which
refer to the worksheet I'm deleting.

Previously, I had tried copying and renaming the worksheet but this
didn't work from memory either.

Name your new sheet the same as your deleted sheet.

Application.DisplayAlerts = False
Worksheets("Data").Delete
Application.DisplayAlerts = True
Worksheets.Add.Name = "Data"

Thanks for both suggestions a couple of weeks back for this original
problem (See below). This involved the correct way to clear data when
repeatedly importing data from a database with a macro.

I've experimented with different options to remove worksheets before
the query is executed. The major problem with deleting a worksheet
before each import is that I have other cell formulas which depend on
the values in the worksheet being deleted. The result is I get !REF
errors.