Replies

I finally make it. I don’t know exactly what I did, but after modifying my query and issuing a Me.Requery immedately after closing my recordset, the screen was updateed. When I stepped through my codes, it appeared that the AfterDelConfirm or AfterUpdate event wasn’t executed after deletion of a record. Thanks.

Surely “Out of Memory” alarm is scary. I checked the KB article suggested by Wendell (thanks) and it appeared that the problem is limited to Access 2K and VBA editor. It never occurred to me that I need to close the code windows each time I open one to save system resources. I was under the impression that only a code window was opened when I used the VBA editor. I closed my code windows and mentally calculated that I closed a total 63 code windows. After closing all the code windows, the database ran a little faster but there was no guarantee that the scary alarm message won’t reappear. In fact it did!

I checked my codes and all my recordsets, and workspaces were closed and set to nothing before exiting. There is no possibility of memory leakage, I think. But closing ZoneAlarm, Norton Auto-Protect and MemTurbo to reclaim memory rersources also helped to make “Out of Memory” disappear. I wonder if others have similar experience.

I disabled or closed the five resident launchers in my system – GetRight, ZoneAlarm, PGP, MemTurbo and Norton Antiirus Auto-Protect – and re-started my Access 2K. After loading my database, I managed to use VBA editor for a while. After I added more codes and objects to my database, again the warning “Out of Memory” appeared when I tried to access the VBA editor. I didn’t install any new programs for the past three months except Windows and Norton Virus pattern updates, which were periodically run when I was connected to the Internet.

I remembered reading about the maximum size of data Access can process, but I have yet to come across any mention of the limitation on codes and objects in an Access database. Is there a maximum size to the codes and objects which Access can manage before the dreadful “Out of Memory” alarm?

For example I developed both my front-end and back-end on drive C: in my desktop, and then moved both of them to drive D: in another PC. In the second PC, the back-end is still linked to the front-end according to its original location in C:. To run the Access database, I have first to re-link the back-end to drive D:. If the location is the same for both front-end and back-end in both my desktop and the second PC, re-linking isn’t necessary. If my back-end is in the same server all along, there is no need for re-linking either, and the front-end can find the tables in the back-end consistently. I hope I have made myself clear.

No, Patt, it doesn’t work. I used either or both AfterDelConfirm and AfterUpdate of the form, but there is no screen update and the “#deleted” on the deleted row stubbornly stayed. Is there any other way to force a screen update after deletion? BTW, I used DAO for my database objects.

Most likely that the location of your server or your second computer never change, or if it is on the same desktop, the location of the back-end linked tables never cahnes. I move my database from desktop to desktop of my colleagues, and I often need to relink ther back-end.

Thanks for your suggestion, Patt. I managed to solve the problem by using the fRefreshLinks() from http://www.mvps.org/access/tables/tbl0009.htm
I modify the codes slightly by adding searching for the back-end database in the same directory as the front-end.

Thanks a lot for your help. It works. It puzzled why I couldn’t find the answer in common Access books available locally.