not enough memory

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I wrote some code that contains a lot of variables. Once I finished I
clicked the button to activate the procedure and it seems to have run
coorectly. When I go to form design to do some more work, I get the message
that there is not enough memory to perform the operation and the Access
closes (Access 97). The procedure ran fine until I added a number of
variables. I suspect there is a way to get Access to release the memory used
to store variables, but I don't know what it is. Any help?
 
Hi, Scott.

If you use variables for objects, you need to close any objects opened and
then set those variables to Nothing to release the memory before these
variables lose scope. For example:

Public Sub OpenDatabaseX()

Dim wrkJet As Workspace
Dim dbsNorthwind As Database
Dim dbsPubs As Database
Dim dbsPubs2 As Database

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb", _
True)
Set dbsPubs = wrkJet.OpenDatabase("Publishers", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=pubs;DSN=Publishers")
Set dbsPubs2 = wrkJet.OpenDatabase("Publishers", _
dbDriverCompleteRequired, True, _
"ODBC;DATABASE=pubs;DSN=Publishers;")

End Sub

When the line "End Sub" is executed, the Workspace Object is still open and
three Database Objects are still open. Their memory isn't released when the
procedure ends. The proper way to release memory for these objects prior to
the end of the procedure would be like this:

dbsNorthwind.Close
dbsPubs.Close
dbsPubs2.Close
wrkJet.Close

Set dbsNorthwind = Nothing
Set dbsPubs= Nothing
Set dbsPubs2= Nothing
Set wrkJet= Nothing

End Sub

Standard data types are released from memory when Access sees fit. If you
have arrays, then use the following syntax prior to the end of the procedure:

Erase MyArray( )

You may also want to organize your procedures in standard modules. If even
one procedure is called, the entire module is loaded into memory. Separate
commonly called procedures from less commonly called procedures. Or separate
the procedures that get called only during certain operations. Or cut down
on the total number of lines of code per module, especially if you have like
3,000 lines or more in a module and only use a handful of these procedures a
majority of the time.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Gunny,

I have a question about your use of the close command on the objects in
your reply.

I think you're using those because - in your example - you're closing a
remote connection to a database. If you were working with a strictly
local database simply setting the object = NOTHING would work just as
well wouldn't it?

James--
 
If you were working with a strictly
local database simply setting the object = NOTHING would work just as
well wouldn't it?
Click to expand...

Not necessarily. If an object has a close method, use it, because that's
the most efficient way to release system resources. And then set the
variable to Nothing before the variable loses scope.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
 
Always CLOSE what you OPEN as in

set rs = CurrentDb.OpenRecordset

rs.close
set rs = Nothing

*ALWAYS* close things explicity. I've heard conflicting information
about Access doing an implicit close when the object variable is
destroyed (set rs = Nothing). But its always best to do it personally.
 
'69 Camaro said:
Not necessarily. If an object has a close method, use it, because that's
the most efficient way to release system resources. And then set the
variable to Nothing before the variable loses scope.
Click to expand...


To be pedantic, if an object has a close method and you used the open method
to instantiate it, use the close method.

For instance, you can instantiate a reference to the current database as

Set dbCurr = CurrentDb()

but it's not appropriate to use dbCurr.Close in that case (although I
believe Access has been fixed so that it doesn't cause the catastrophic
error it used to)
 
If you're using the .RecordSetClone method of a form/report object,
you'd still have SET the variable to NOTHING; but you don't have to
close it (since you didn't explicity open it).
 
Back
Top