Why do I get "Out of memory" message? 3

[grnzbra]

I maintain a membership database for an organization. The process of adding a new member or updating a current member is as follows:
1. Use filter by form to look up member's last name (even for new applications)
2. If new member, assign member number by opening recordset of max(MemberNumber) for that type of membership. Add 1 to it and concatinate to alpha prifix
3. On sub form, enter transactions: If type of transactions contains the word membership, open recordset of table of membership dues for that type of membership. Add unit price to subform record. If doesn't contain "membership" look up price in transaction type table. Entering type of payment (MasterCard, Visa, AmEX, check) involves opening a recordset to determine which card is used (based on first digit).
4. "Membeship" in transaction type causes scanning of all fields (member info stuff) for creation of confirmation e-mail. This e-mail goes as a record in an e-mail table. At the end of a session, e-mails are sent.


After each recordset is used, it is closed and set equal to nothing.

After a few hours of entering information, I suddenly get an "out of memory" message. Does anyone have any ideas or suggestions about why it is happening and how to correct the problem?

 

[Koolaid99]

Had this before myself. It seems to be caused by having pictued backgrounds. I'm talking using the special color formats instead of just plain grey. When make a form and not use the plain grey background MS Acess has a memory leak. After long term use of going through diffrent forms you will run out of memory. Thats the problem I was having anyway.

 

[grnzbra]

The background is a custom blue. No picture. I'll see if going back to gray helps.

Would the same problem exist if I were trying to use VB front end and SQL Server back, or is this memory leak strictly an Access thing?

By the way, this is Access 2000 on Windows 98 with 95 meg of ram.

 

[thornmastr]

Make sure you have released and closed all references to your recordsets and database. If, for example, you use db as a reference to database, and rs as a reference to recordsets, be sure at the end of each function using them, you do the following:

rs.close
set rs = nothing
set db = nothing Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[grnzbra]

Actually, I think it uses connection rather than db. Does that matter?

 

[cmmrfrds]

Windows 98 does not use memory very well and 95 meg is not much memory, but even with more memory Win 98 may not use it.

Make sure you don't have any VBA code open. Check under Windows on the menubar and if there is VBA code open close it. Open VBA code can use up a lot of memory. It may be a good idea to compact the mdb in case the mdb is retaining code from previous development sessions.

 

[thornmastr]

Obviously, then, you are not using a database reference, but you are using a recorset reference.Make sure they are released. By personal choice, i prefer DAO to ADO. But, it would seem to me you would want to close a connection once you are finshed with it...I'm sure someone very familiar with ADO will pick up on this. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[grnzbra]

What is "open VBA code". Can it be closed programatically?

I used ADO because I thought it might be a good idea to learn it. Unfortunately, that database is the only one I used it. My job is still working with Access97. What are the advantages of DAO over ADO? (I find DAO easier, but thought it was a result of so little experience with ADO)

 

[thornmastr]

take a walk over to

http://www.google.com.

Go to comp>database and then do a search on DAO vs ADO. Draw your own conclusions. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[cmmrfrds]

Sometimes when you are editing code behind the Form or in Modules the code stays open it does not get closed for some reason. Sometimes you need to explicitly close the code. For example, if you do a search for a certain keyword while in vba code it will keep open until closed all the subroutines or functions it finds the keyword. Under occasions I have had to close these explicitly since they didn't close when I exited the search. In these cases the code will be using up memory.

Are you building sql statements etc.. in vba code? Win 98 may not be releasing the memory. The memory problem probably would not happen in one of the NT OS's like Win 2000 pro.

There is a lot of good information in this link on the differences between DAO and ADO.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp

If you are only going to use Access (never ASP or other client products and only an Access mdb) then DAO is fine since it is Access specific. ADO is more universal and is the same library whether in an Access app or an ASP app or other client - you can leverage your knowledge in these other client products.

If you are closing the recordset and setting to nothing that is good, but if you are creating multiple connections these may be held in memory - set these to nothing too.

It may be worthwhile pasting the code that you are working with so that it can be reviewed.

 

[grnzbra]

When I'm having this problem, I'm not usually looking at the code. However, each search does create an SQL statement so that could be a problem. Is there any way to force the release of these statements; the computer is my wife's and she will not hear of a change to W2000.

Also, I use a number of global variables in these searches. Do these have to be set to nothing also?

I won't have access to the code 'til this evening.

 

[cmmrfrds]

Global variables are going to exist by definition/declaration. I assume they are in a standard module. My guess is the strings will be set to nulls in the declaration and not use much memory until they are used. It may not hurt to set these back to null when not in use. The numeric variables are limited to their size of 2,4, or 8 bytes so should not be a problem.

I don't know of any way to force Win 98 to clean up memory, short of a reboot.

 

[thornmastr]

This is both to get some information from you and to give you some information. You are saying you have 95MB memory available. This leads me to believe you are running at best a Pentium 1 processor using 32MB memory with 3 memory blocks which gives you 96MB less 1 MB for system use for a total of 95.

It may well be that your memory problem is quite normal. Regardless of what you may have read from MS in their attempt to sell A2K, you should have at least 128MB to run A2K. And that to me is the absolute bare minimum without asking for trouble. What kind of trouble. Memory failures, queries that take far too long to complete or that may never complete. Now is a good time to talk upgrade with your wife.

Now, having said that let me move into some of the replies you have received or the ideas behind the replies. Everything you read in a programming news group, view with skepticism and make up your mind to devote the time to prove either their validity or their fallaciousness.

Memory leaks in almost all cases are caused by one or more of the following causes.
References that have not been released.
Recursive functions.
A malfunctioning memory block or not enough memory.

Any variable other than those used with the set command cannot cause a memory leak.

Someone mentioned different coloring schemes on forms can cause leakage. I’ve never seen that but I would not say it is impossible. I simply do not know, and since I tend to use the standard defaults, I simply don’t care enough to find out.

Global variables exist for the life of your process. Makes no difference if you set them to zero, null, or 1050, they are there and they remain until you exit Access.

Variables declared in a function live only until the function completes.

Variables defined at the top of the form module will survive until the form is closed.

DAO and ADO are functioning dead libraries. Neither is being developed further, nor are they being enhanced. ADO was abandoned by Microsoft before it was completed. DAO was completed. The ADO connect statement is incredibly fast and nothing that DAO has can touch it for speed. In terms of speed, other than the connect statement, both libraries are roughly equivalent. The DAO instruction set is richer and fuller than ADO simply because ADO was never completed.

The statement that ADO works on other Microsoft applications such as Word and Excel, and DAO cannot manipulate those applications is simply not true. Both libraries do this equally well. You must use ADO if you are using the latest MDAC updates and you are using SQL server.

Compacting and repairing a database will not rectify a memory leak.

Before looking for programmatic reasons for your memory problem, my suggestion to you is to first upgrade your memory to at least 128MB. Access may simply be telling you that it is hungry. Try feeding it.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[grnzbra]

I believe it's at least a pentium 2

 

[thornmastr]

The reason i suspected Pentium 1 is that you are using memory blocks of 32 rather than 64 which became the general standard with the Pentium 2.

However, i think I'm beating a dead horse. My concern is you may not have sufficient memory to give you what you need. Perhaps you do. Supposing you have, then look at your code. In any function using a recordset be sure you both close the recorset and then set it to nothing, ie,
set rs = nothing, and then see if you still get the memory problem. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[grnzbra]

Thank you.

 

[cmmrfrds]

Thronmastr seems to have missed the point of one of my statements - there was no direct mention of Microsoft.

"The statement that ADO works on other Microsoft applications such as Word and Excel, and DAO cannot manipulate those applications is simply not true. Both libraries do this equally well. You must use ADO if you are using the latest MDAC updates and you are using SQL server."

You can use DAO for SQL Server if you Link the tables to an Access mdb with ODBC.

"If you are only going to use Access (never ASP or other client products and only an Access mdb) then DAO is fine since it is Access specific. ADO is more universal and is the same library whether in an Access app or an ASP app or other client - you can leverage your knowledge in these other client products."

This statement says nothing about Word or Excel but about Access and DAO. My statement was directed to both the client and database and other vendors products. You can read and manipulate many more data sources with ADO. There are ADO providers for Oracle, SQL Server, Excel, Internet Publishing, Active Directory, Pervasive, and others. As you can see many of these are not Microsoft and not all are databases but data sources. My implication was that once you learn ADO it will serve you for Microsoft Clients, Microsoft Databases, other vendor clients, and other vendor databases.

DAO is fine for Manipulating ACCESS data in Excel. If you want Oracle or Pervasive (many others) data then these data sources must be linked to an ACCESS mdb through ODBC or somehow use the Jet Engine. ADO not only works with a direct connection from Excel to Access, but also with a direct connection from other clients to other databases. An ASP application can connect directly to Access or other databases. The strength of an ADO recordset is that it can be connectionless among other things. This is especially important in ASP since you won't have a constant connection to the database. You can bring back an ADO recordset, disconnect, update the recordset, reconnect, and then update the database which can all be wrapped in a transaction. My point is that when you move from Access client to ASP (or other clients) you will already be familiar with the ADO library and be able to leverage your knowledge. The same is true about moving to other databases and data sources. What you have learned in ADO can still be applied. It is not unusual after developing in Access for a while the next step is developing in ASP.

I agree on the memory, best to increase it if possible. I will give an example of why it may not help. I had an access app that was running out of memory on my Win 98 PC with 128K. It would run fine on my NT PC with 64K. I increased the memory on the Win 98 PC to 256K and it still ran out of memory. I talked to Microsoft and it turned out that for some reason the app was not releasing the VBA code from memory - they helped me fix it. The tech told me that Win 98 will only use so much memory even if more is available. Just an FYI.

 

[Koolaid99]

I think this is the problem your having. I should have looked it back up yesterday.

link "

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q248910"

 

[Koolaid99]

Woops ment to preview not submit yet. Anyways this was the problem I was having with the predifined forms.

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q236977

Anyone one of them will help. There is also about 50 other ways that Access can make your computer run out of memory.