Error 7 - Out of memory when exporting records 1

[Trevil]

Greetings,

I'm responsible for maintaining a VB6 program that is encountering "Error 7 - Out of memory" in one subroutine.

Although this application is deployed to 72 countries and runs on both XP and Win-2K servers, the problem happens only at one country, which is running Win-2K.

The subroutine loops through all tables in the master database (about 75), and exports any records that have been modified in the past 20 days into new tables in an empty database.

The failure always occurs on the same table, which is the 58th table and it is trying to export about 3,400 records. Only about 25 tables before that had any data that qualified (atotal of about 24,000 records; 8,000+ for one of those tables).

I have inserted code to check memory and disk availabilty when the subroutine starts (1.5GB disk free; % memory free = 26, total memory = 512).

One other weird item: the job runs at 7AM & 10PM. Previously the 7AM ALWAYS failed there, but I thought since the 7AM imported records we sent from HQ, maybe that was related. The 10PM ALWAYS ran with no problem (never imports) -- that is until now!

Any suggestions on changes to the code, or how to try and isolate this issue?

Thank You!

The following code has been edited to remove items not relevant to the problem.

Private Sub ExportData()
Const CURRENT_METHOD As String = CURRENT_MODULE + "ExportData"
Dim rsSrc As Recordset
Dim prmNew As Parameter
Dim SrcTbl As Table
Dim rsExport As Recordset
Dim SrcCol As Column

On Error GoTo ERROR_HANDLER

Set catLocal = New ADOX.Catalog
Set catLocal.ActiveConnection = cnLocalData

Call ADOFactory.GetConnection(DumpDatabaseFile(), cnExportData, _
adUseClient, False)
For Each SrcTbl In catLocal.Tables
If SrcTbl.Type = "TABLE" And Trim$(SrcTbl.Name) <> "ctrlKeyStorage" Then
Set rsSrc = New Recordset

rsSrc.CursorLocation = adUseClient
rsSrc.Open SrcTbl.Name, cnLocalData, adOpenForwardOnly, _
adLockReadOnly, adCmdTableDirect

If Not rsSrc.BOF And Not rsSrc.EOF Then
rsSrc.MoveFirst
sSQL = "select " & SrcTbl.Name & ".* into " & SrcTbl.Name & _
" IN '" & DumpDatabaseFile() & "' from " & SrcTbl.Name & _
" where ((([EnteredTime])>#" & vUseDate & "#));"
>>>FAILS HERE cnLocalData.Execute (sSQL) <<< FAILS HERE
DoEvents

Call ADOFactory.CloseConnection(cnExportData)

Call ADOFactory.GetConnection(DumpDatabaseFile(), cnExportData, _
adUseClient, False)

Set rsExport = New Recordset
rsExport.Open SrcTbl.Name, cnExportData, adOpenStatic, _
adLockReadOnly, adCmdTableDirect
iRecordsAffected = rsExport.RecordCount
lExport_Total = lExport_Total + iRecordsAffected

rsExport.Close
Set rsExport = Nothing

Else
' There is no data in this table! Need to provide structure!
End If
rsSrc.Close
Set rsSrc = Nothing
End If

Next SrcTbl

Write_To_Log " Total Records Exported: " & Format(lExport_Total, "###,##0")

Proc_Exit:
..
Exit Sub

ERROR_HANDLER:
‘ Journalize Error..
Resume Proc_Exit
End Sub


"Have a great day today and a better day tomorrow!

 

[gmmastros]

Is it possible that the user has added a table with a space in the name. I know in SQL Server, you can surround table names with square brackets [] to accomodate table names with spaces. It looks like you are using access, and access may be different, but look in to it.

It's as good a guess as any. Sorry if this doesn't help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Trevil]

Thank you George!

Sheepishly, in a quiet moment (called Saturday), I opened Task Manager to view memory usage, then ran the subroutine many times and noticed a large spike. After inserting breakpoints and stepping through the tables, I was able to pinpoint the cause.

Because of the error message on the line of code where the export takes place, I thought that was the place to look. However, it turns out the problem was much earlier on the line:
rsSrc.Open SrcTbl.Name, cnLocalData, adOpenForwardOnly, _
adLockReadOnly, adCmdTableDirect

That code was opening the table to check if a column named "EnteredTime" existed, but it sure chewed up the memory usage (50,000+ records).

I have no idea why the guy wrote the code to open the table just to check for the presence of a column, but after changing it to something *better*, the memory usage graph in Task Manager stays flat (and normal)!!

Thank you very much for your response!




"Have a great day today and a better day tomorrow!