Exporting to Access - Memory Leak?

[taigovinda]

Hi,

I have a file that will run a Bex query repeatedly, grabbing parts of a big date range (2/1 - 2/15; 2/16 - 2/28; 3/1 - 3/15, etc.) and each time add the results to a big Access file (after first deleting the date range in question, if it already exists in the Access file).

I'm having a problem where the query runs a few times (five or so) and adds to Access just fine, but then the 'add to Access' part stops taking 30 seconds and starts taking like 15 minutes. If I kill the process and pick up where I left off then it runs fast again for the first few times.

I thought the database needed to be compacted so I set it to compact every five queries, but that didn't seem to help at all.

Could it be that I am declaring the database in my code, and then it is being retained in memory over and over again? If I add a line close to the end of my macro that says

set db = nothing

...is there a good chance that would fix it?

Here is the code that adds the data to Access and below is the code that compacts the database:

Adds from Excel

Sub UpdateAccessData(Start_Date As Date, End_Date As Date, Access_Full_Path As String)
'adapted from http://www.ozgrid.com/forum/showthread.php?t=36431&page=1
Dim db As Database
Dim rs As Recordset
Dim Qd As QueryDef
Dim XLTable As TableDef
Dim strSQL As String
'Open the Microsoft Access database.
Set db = OpenDatabase(Access_Full_Path)
' Create temp table
Set XLTable = db.CreateTableDef("Temp")
' run delete query using parameters
Set Qd = db.QueryDefs("Delete Dates System Wide OT Data")
Qd.Parameters("start_del_date") = Start_Date
Qd.Parameters("end_del_date") = End_Date
Qd.Parameters("OT or Regular") = "Overtime"
Qd.Execute
' connect append range
XLTable.Connect = "Excel 5.0;DATABASE=" & ThisWorkbook.FullName
XLTable.SourceTableName = "SQL_Data_Range"
db.TableDefs.Append XLTable
' create the append query
strSQL = "Insert into System_Wide_OT_Data Select * from Temp"
'Execute the SQL statement.
db.Execute strSQL
'Remove the temp table.
db.TableDefs.Delete "Temp"
Qd.Close
db.Close
End Sub

Compacts:

Sub CompactRepair(Access_Orig_Path As String)
Dim BackupPath As String, TempPath As String
'define paths
BackupPath = Replace(Access_Orig_Path, ".mdb", " BACKUP.mdb")
TempPath = Replace(Access_Orig_Path, ".mdb", " TEMP.mdb")
'save backup copy
FileCopy Access_Orig_Path, BackupPath
'compact and repair
DBEngine.CompactDatabase Access_Orig_Path, TempPath
'delete the original
Kill Access_Orig_Path
'rename the temporary database - it is now the new original
Name TempPath As Access_Orig_Path
End Sub

Thanks for any suggestions.

Tai

 

[taigovinda]

As an update and a follow-up question (see the bottom of this post for the question please)...

I thought the problem was memory getting eaten up when Excel set a variable to a big (800MB or so) Access database repeatedly.

But now I feel that has been disproven, because I set my workbook to export very small amounts of data 50 times in a row (and therefore create that Access database variable 50 times in a row) and it worked with no problems at all.

I think what is causing the hang-up is actually 'trace files' that BW 3 (aka SAP Bex, I called it Bex in my first post) is creating. It seems that BW is holding these trace files in memory until the Excel application is exited completely. I can't figure out how to stop the trace files from being created - and neither can the 'BW team' in my company. They've had me try several things over the past couple of months, as all the trace files being created and popping up all over my computer were annoying even before they caused this batching process to fail...

So... I suppose now my question is, is there a way that I can create an Excel macro that will cycle a few times, then *close Excel completely* and then cycle a few more times, then *close Excel completely* and so on? Anyone know how to do that?

Thanks!
Tai