Out of Memory Error in Access 2003

[Chris O'C via AccessMonster.com]

At the end, why are you setting db = nothing and then db = currentdb again?
Might be the source of your "out of memory" error with all those db variables
allocated and not released each time the proc goes out of scope.

Why are you not exiting the loop as soon as the right form is found? As it
is, you force the code to execute through the name of every open form, even
if the right form was requeried 20 forms ago. Why isn't your code using the
dbfailonerror argument for the execute method so the transaction is rolled
back if part of it fails?

Chris
Microsoft MVP

 

[Dave]

I am doing development in Access 2003 and am intermittently getting an "out
of memory error."

I am not an expert but I have done Access development for several years and
never encountered this problem before.

The information I find on Google does not apply (it is not a multi-user app
and there are no grouped controls).

I have compacted and repaired the database multiple times and I have
completely rebooted the Windows XP/SP2 development box several times.

Each time I think the problem has gone away and then suddenly I get another
out of memory error, usually when I am working in a VBA code module. At
that point I can no longer edit the code module or its associated form. All
other forms seem to work fine. But I must delete the locked form and
rebuilt it from scratch. Then everything will work fine for a while until
one of the forms will give another out of memory error.

Any ideas what the problem is?

The only thing unusal about the app is that it will perform a database
modification through a POP up form using DAO as shown below.

Other than that there is nothing unusual except this mdb file was previously
upgraded by Access 2000.

Thanks
Dave

Dim db As DAO.Database
Set db = CurrentDb

sSQL = "INSERT INTO word_definition (WordID, DefinitionID)"
sSQL = sSQL & " VALUES(" & iWordID & ", " & iDefinitionID & ");"

Debug.Print sSQL

'Perform INSERT
db.Execute (sSQL)

'Confirm INSERT
Dim sMsg As String
sMsg = "Record inserted into word_definition table."
sMsg = sMsg & " (Wordid = " & iWordID
sMsg = sMsg & " Definitionid = " & iDefinitionID & ")"
'MsgBox (sMsg)

'check to see if form is open
Dim i As Integer
For i = Forms.Count - 1 To 0 Step -1
If Forms(i).Name = "Word" Then
Forms!Word!Definition_SUB.Requery
End If
Next

'Forms!Word!Definition_SUB.Requery

'cleanup and close
Set db = Nothing
Set db = CurrentDb

 

[Dave]

My goodness, where did that come from!?!

Thank you for providing a second set of eyes. All works well now.

And I added the EXIT FOR and dbfailonerror as well.

Thanks again.