Closing out a Form/Code - DoCmd.Close (1 Viewer)

[Daveyk01]

Is there anything else that should be done when a form is closed?

I ask this because I have observed with the Task Manager, Access 2000 taking 20,000K memory with my main form open. From there I link to another form, keeping main form open. With that second form open, Access is sucking up 29,000K memory. Close new form, Access is not using 25,000K memory, not the original 20K.

Open the code editor, Access sucks another 11,000L. or so. Close code editor, Access continues to suck that 11,000K memory. Every form that I open uses more memory and Access doesn't free it bacdk up, or it only frees up part of it.

This goes with another thread I have going, but is different. What happens to the variable arrays and other variables when a form closes. From my experiment, it looks like they remain in memory. Is there anything I can do besides "DoCmd.Close" when a form is due to close. Is there an memory flushing or garbage collection that can be done?

If you have enough forms, it looks like Access just keeps gobbling memory and not giving it back and then slows down. Creating an MDE version of the database seems to alieviate memory, but even then it keeps gobbling and not giving back, but at least has a lot further to go.

 

[georgedwilkinson]

I saw your other thread and didn't feel qualified to answer. But seeing as how nobody has answered you, I thought I'd take a stab at it. If I'm wrong, you're likely to get 10 people here saying I'm wrong with the correct answer (win/win).

Based on my knowledge of DBMSs in general: the memory you're seeing used probably has more to do with queries than it does with object memory. Like any good DBMS, once Access has some memory, it doesn't want to give it up until it is sure it doesn't need it anymore (though Access is a peculiar creature for a DBMS).

Some things you might do is reduce the memory required by your queries, optimizing, normalizing, etc. Full table scans (i.e. any form without a filter) take up as much memory as it takes to put the whole table in memory.

Depending on the size of your database, < 40k doesn't seem like that much to me (even if you meant < 40M). I'd worry more about cpu utilization if you're having performance problems (unless you just don't have much memory).

HTH.

 

[Daveyk01]

Hello there. When I reported the memory, it is from the Processes Tab of the task manager. So 40,000K is 40 megs. Not much in a machine with 1 gig ram, but the performance issue (in the other thread is/was horrible).

It is just interesting that when a form is opened, about 10 megs of ram is gobbled and when it closes, Access doesn't give it back and it get worse the number of forms opened. I tried it with a form that does have any tables behind it and no queries (it was a form that controled an oscilloscope and did a few external things). All of my forms, that one included, use UNBOUND datafields. If I choose to save the data, I create the sql string and open the database and records set and then the data in the unbound text boxes is stored and the recordset close and equated to nothing and the same with the db. So none of my forms have a query behind them. That was data is not committed until the "Save Data and Continue to Next Test" button is clicked.

I have tried, for the 1st time today, to make an MDE file. They start out at a much lower memory consumption but grow just like the mdb version does. I tried something intersting tonight. I decompiled the program, then compressed and then created the MDE version without compiling first. The size of the MDE is about 16 megs on the hard drive as apposed to 30megs and seems to run slower, but faster than the slow down condition in the other thread. I'll try it in the morning in the shop to see its speed.

Late this afternoon, I created an MDE after compiling and it ran very fast. An EN12668-1 test took 32 minutes in the MDB version and just 14.5 minutes in the MDE version. That is an incredible improvement and the collected data was as acurate and the same as with the MDB version. That was the routine killing the database performance after that test form was closed. Now of course, with the MDE version memory consumption was about 30Megs at that point verses 56megs (according to the TASK manager).

I think, 30 megs, 56Megs, so what. That is tiney compared to available memory, so I can't believe it is the issue. However, I started monitoring the task manager as I ran Access. I am stumped by that queryless form grabbing that memory and not releasing it when I do the docmd.close command to exit the form. I was wondering if I did dimension a variable, say DIM Results(4,40) as single, if I should not REDIM it to nothing before closing the form? If coarse that example should take so little memory it would not be measurable and none of my arrays get very big. The biggest one is probably (4,30). But what does someone have to do to release memory when a form closes?

I have another HUGE database I maintain that is a lot larger than this instrument test program and I have never noticed a problem like this. At least not a problem that I didn't blame on the network. I have use the "network must have glitched" excuse a lot when someone complains, or "Oh the network is slow today!" <grin>. I will have to monitor its memory useage.

Do newer versions of Access have better memory management or are they worse? We are stuck on Office 2000 and proably won't upgrade until Office 2020, or later.

I am running Access 2000 SP-3.

 

[georgedwilkinson]

I'm told the older versions of Access are more stable but don't have evidence. Obviously, Microsoft will say the opposite.

The database size can be deceiving. The database will get bigger as you run SQL statements. That is an oddity of Jet, so I'm told. I would think that if Jet optimized a database file (i.e. created the internal structures it needs to run the SQL you use on a regular basis), it would take more time AFTER you've compressed the database than it would before a compression (just a guess-you can't get something for nothing).

I'm curious that you would even use an Access front end for this application since everything is unbound. The beauty of Access as a front end is the tight integration of the forms/reports with the data.

I would think you would be better off using C++/C# or LabView/G since you have to write all the data handling routines yourself anyways. You have a lot more control over performance and memory management, at least with C++/C#. LabView is made for the type of application you're describing and, at least while I was programming with it, it was very efficient.

 

[gemma-the-husky]

memory management should be transparent really

the os should switch programs in and out of memory as required, based on some internal strategy. Memory usage wont just include programmes it will include data also. I dont think it is easy to control which program segments windows/access pages in/out, although I do know that you should keep commonly used, or related functions in separate modules to avoid memory requirements growing

For application sharing, Windows is not as efficient as say VMS (I am sure others can confirm this), which is why you get hourglasses, and slower performance in multi-tasking, and a slower release of resources back to the os.

But general speaking, I wouldnt have thought you need worry about behind the scenes activitiy - I would think you need a very expert skillset to deal with it - for a normal user (eg me) its hard to even tell what all the processes you see in task manager actually DO!

 

[Rich]

Are you opening recordsets in code?
if so then don't forget
rst.Close
Set rst = Nothing

 

[Daveyk01]

I'm curious that you would even use an Access front end for this application since everything is unbound.

Confort zone, reporting, knowing it well enough. I have VB6 and have done some cool instrument control utilities, but don't know it well enough to open data tables, change the structure and store the data. I am not familiar with its ability to do reporting. I also get fustrated with its control events and I am sure that is just lack of familiarity and time to become intimit with VB6. Making those EXE files are sure nice though.

I have seen a lot of people complain about Access reports. but I have a beutifully formatted 27 page data collection report that is very presentable to the customer. It also talks to our 50,000+ record service database that has more than ten users at any given time easily via linked tables.

Now Access is much harder getting this application set up on a new computer, such as in the UK where we have a service office. I have to get my buddy there to manually edit the registry to use the MSCOMM module in access (although I think he has VB6 installed so he shouldn't have to). It is so easy with Access to keep an off-line database, link to it, such as when working in the field, and come back in-house combine the newly collected data with a master database and re-link to that master database. Well, that may not be the easiest thing to do, but it is doable and I have perfected those routines.

I really thought I was doing hot shxt until that EN12668-1 Bandwidth/linearity data collection module slowed the entire database to a crawl. It is great again if Access is exited and restarted picking up after that module. It is also very good right now in MDE form, which I don't know why. The only clue, and I am grabbing at straws in when monitoring the Task Manager, seeing Access grab more memory each time a form is opened and not giving it back when that form closes.

56/60 megs of ram is nothing, but that is the only clue I have detected so far. It doesn not matter if I exit all other running programs to free up what is available or not. It just seems when Access hits that 56-60M barrier it starts to creep with I/O control.

 

[Daveyk01]

Are you opening recordsets in code?
if so then don't forget
rst.Close
Set rst = Nothing

Yes, thank you. Some time back I went through a number of report modules and had to go "oops" <g>.

Now when ever I open a record set and database, before any other code is written, then next lines are:
rs.close
set rs=nothing
db.close
set db=nothing.