Not enough free memory

[ChopinFan]

I've learned that releasing object variables may or may not actually deallocate the memory used by them and I seem to be having a problem with too much memory being used up (even though I've closed recordsets and set things equal to nothing). When the segment of code below runs, the reports all open, but then I get a message "There isn't enough free memory to update the display. Close unneeded programs and try again." and the computer wigs out. How can I force the memory to be deallocated?? (assuming this really is the problem)

Case 9 'UPD letter spon standard
'GET 1,4,7 LETTERS
Set Com = New ADODB.Command
With Com
.ActiveConnection = Con
.CommandType = adCmdStoredProc
.CommandText = "dbo.Check147"
.Parameters.Append .CreateParameter("@StudentNumber", adVarChar, adParamInput, 500, Trim(StudentNumber.Value))
.Parameters.Append .CreateParameter("@Country", adVarChar, adParamInput, 2, Trim(Country.Value))
End With
Set rstUpdateNum = Com.Execute
Set Com = Nothing
If Not rstUpdateNum.EOF Then
DoCmd.OpenReport "ReportName147", acViewPreview
End If
rstUpdateNum.Close
Set rstUpdateNum = Nothing


'GET 2,5,8 LETTERS
Set Com = New ADODB.Command
With Com
.ActiveConnection = Con
.CommandType = adCmdStoredProc
.CommandText = "dbo.Check258"
.Parameters.Append .CreateParameter("@StudentNumber", adVarChar, adParamInput, 500, Trim(StudentNumber.Value))
.Parameters.Append .CreateParameter("@Country", adVarChar, adParamInput, 2, Trim(Country.Value))
End With
Set rstUpdateNum = Com.Execute
Set Com = Nothing
If Not rstUpdateNum.EOF Then
DoCmd.OpenReport " ReportName258", acViewPreview
End If
rstUpdateNum.Close
Set rstUpdateNum = Nothing


'GET 3,6,9 LETTERS
Set Com = New ADODB.Command
With Com
.ActiveConnection = Con
.CommandType = adCmdStoredProc
.CommandText = "dbo.Check369"
.Parameters.Append .CreateParameter("@StudentNumber", adVarChar, adParamInput, 500, Trim(StudentNumber.Value))
.Parameters.Append .CreateParameter("@Country", adVarChar, adParamInput, 2, Trim(Country.Value))
End With
Set rstUpdateNum = Com.Execute
Set Com = Nothing
If Not rstUpdateNum.EOF Then
DoCmd.OpenReport “ReportName369", acViewPreview
End If
rstUpdateNum.Close
Set rstUpdateNum = Nothing

 

[AlexCuse]

If that really is the problem, I think you may need to compact the DB. Do you have extraordinarily large recordsets though? I've worked with some pretty big ones and never had this problem, but I have a lot o memory.

Maybe you need to just ask the boss for more RAM

Ignorance of certain subjects is a great part of wisdom

 

[JoeAtWork]

Are you leaving a lot of VBA modules open in the code window? Each one chews up a little memory, and I've had you problem when I left dozens of them open.

Close them all, except on the last one do a very minor change (like add a space and then remove it) and then close it, and then press the Save button. If you don't make a small change and "Save" it, Access will just open all those VBA modules again next time you open the database.

 

[ChopinFan]

Thanks both for your thoughts.

There is no problem opening a multitude of other reports and having them open at the same time; just these three for some reason, which is what led me to suspect some memory in the code is not being deallocated and is clogging up the RAM. The recordset is tiny, perhaps 5-10 rows or so, and how much memory can a single command object variable take? Even if neither of these were deallocated, how much RAM could be used? Not much it would seem. Also, the DB is compacted daily.

I tried closing all the extra code windows, but the problem persists.

I just don't know what to think. I googled the error message but couldn't find anything that helps.

Hmm