I half agree with ChrisO.There is a specific issue I will take with ChrisO's earlier comments regarding the need to dereference objects and close openable objects.
Technical point but one does not Close an Office Application object. The method is Quit.I have personally seen cases in which failure to close a Word Application Object (and ALSO for the case of Excel Application Objects) where failure to close the external object leaves a dangling and unsaved file floating in memory.
I disagree with Chris on this. It is difficult for anyone to give an authoritative opinion because Microsoft doesn't document the exact processes. However it is possible to make a reasonable inference about what is going on.Chris is probably correct about objects internal to Access such as recordsets and instantiated structures.
On this I agree with ChrisO. Objects pointers are removed from memory when they go out of scope. This happens when the procedure ends, whether it is set to Nothing or not. ChrisO had disproved claims about memory filling unless object pointers are cleaned up by running a loop calling a procedure millions of times without setting its objects to Nothing. The memory did not fill up.As part of the closure process, you also hurt yourself if you fail to dereference object variables.
I believe that what happens is that setting the external app object to NOTHING will have the effect of a Close but its default closure behavior might not be what you wanted. You would lose data (since there is no connection to a session for the external application to ask the "Save" question) because you would probably get a Close/NoSave behavior. If that's what you wanted, then no harm, no foul. If you close all files before this moment, again no harm, no foul.Microsoft gives what I believe is incorrect advice, saying that Set to Nothing has the same effect as Close.
Galaxiom, you QUIT the program but CLOSE what the program opened.
It is not the code project but the Office application thathas the file open. So you tell the Office App to Close the file, then you tell the Application to Quit. That is the correct exiting sequence.
Probably neither. I don't know the details of the test but I do know that ChrisO was more than enough knowledgeable to design a test for his hypothesis, that objects did not need to be set to Nothing before they go out of scope because this happens automatically. I expect that he used variables declared inside a procedure and ran that procedure in a loop.When ChrisO did the experiment, do you know if he used local or global object variables?
It appears to me that you are suggesting that it is a better practice to reuse a module variable and pass it to the procedure each time. So once declared it sits around in memory and might never be used again. You see going out of scope as as a weakness? I can't agree with that. The if the variable is relevant only in the context of a procedure then it aught to to have the scope of the that procedure.I agree that objects should be cleaned up automatically when the pointer goes out of scope - but good programming practice suggests that you keep your code modular, passing in the data elements on which to operate, necessitating that the data elements you are going to use - the object pointers - DO NOT go out of scope so easily.
Yes. An object created needs to be closed and the application managing needs to Quit. What I took from Chris is that the object pointer itself is automatically removed when it goes out of scope so setting it to Nothing is not required.My concern was/is that if you keep on creating new objects without first dereferencing the old ones, they are still in scope until your code exits.
Really? How soon does the application disappear when it has no files open? It is important because in some of my applications I keep a hidden Word instance running at module level and pass files to it as required. It is a worry it it could spontaneously Quit if left to itself for too long.By the way, if you explicitly close the files opened by your app object, you really DON'T have to QUIT the app - you can just dereference it. See next discussion regarding "rundown."
I accept that if the application quits, any files it has open will be released from memory. But not simply by dereferencing the app form the code project.
I believe that what happens when the pointer is destroyed is that the codeI believe that what happens is that setting the external app object to NOTHING will have the effect of a Close but its default closure behavior might not be what you wanted. You would lose data (since there is no connection to a session for the external application to ask the "Save" question) because you would probably get a Close/NoSave behavior.
loses its ability to communicate with the object. The object itself remains in memory which is why it should be told to Quit, ideally after commanding it to empty its Documents (or Workbooks etc) Collection.
Yes, Close what you Open before dereferencing it or it will persist. I see it happen regularly when code breaks during development and I have to kill it in Task Manager. However I am not convinced that setting the pointer to Nothing will destroy the external object itself. Even exiting the code project entirely doesn't destroy the Office app instance because, one created, it has a life of its own.I will clarify that I have PERSONALLY SEEN that a copy of Word persisted in my task list if I didn't remember to explicitly close what I opened.
Weakness, no. A design option for a specific case? Yes. A design option for ALL possible cases? No.You see going out of scope as as a weakness?
Just as we learn to avoid relational integrity problems and unhandled trap conditions, we learn other tricks of the trade to prevent issues from becoming overwhelming.
So to the Newbie, what would your essential recommended reading be that best explains the rules ?I was just pointing out that there were pitfalls if you didn't abide by these rules in some cases.
Ouch !Oh, that such a book exists to be recommended!
Building Microsoft® Access Applications by John Viescas.Just as we learn to avoid relational integrity problems and unhandled trap conditions, we learn other tricks of the trade to prevent issues from becoming overwhelming.
Why would you believe that? Because someone at Microsoft said it? Clearly you have never performed the trivial test required to confirm the reality. Run the following code. It will leave a hidden instance WinWord.exe running until it is killed.I believe that what happens is that setting the external app object to NOTHING will have the effect of a Close but its default closure behavior might not be what you wanted.
Dim WordApp As Object Set WordApp = CreateObject("Word.Application") Set WordApp = Nothing
Dim WordApp As Object Set WordApp = CreateObject("Word.Application") WordApp.Quit
I am not surprised, especially since you believe the following hogwash.I will clarify that I have PERSONALLY SEEN that a copy of Word persisted in my task list if I didn't remember to explicitly close what I opened. I had to either (a) use task manager to kill it or (b) log out/shut down the computer.
By the way, if you explicitly close the files opened by your app object, you really DON'T have to QUIT the app - you can just dereference it.
So you applied EINTKILIK, rushed in and added Close, Quit and Set To Nothing to everything. Because the problem disappeared you presumed that Set to Nothing was important and you assert that here. In fact the only absolutely essential one is Quit.The incident that made me aware that I had somehow missed the EINTKILIK rules was when one of my users showed me six copies of Word open, NONE of which were opened manually by anything he was doing at the time. Yes, it was my bad, and yes, I found and fixed that problem by applying the EINTKILIK rules. Call them guidelines if you prefer, wouldn't bother me any. But I know I needed to honor those rules more closely than I had previously.
That is exactly what ChrisO did when he looped millions of times through setting an object variable then let it go out of scope without setting to Nothing. Unfortunately you have not followed you own advice.Keep reading articles and asking questions. Question stuff that doesn't seem to make sense. See if there is a way to sanely test something you just saw.
That last paragraph is not terrible advice for newbies, journeymen, professionals, wizards, and gurus alike.
I remember getting into a lengthy discussion about that a few years ago. The summary of arguments was that where a natural candidate key existed, it was preferable. However, if no viable candidates were found and yet a unique key was required, the use of a synthesized key (Autonumber Longword) was not so terrible as to cause immediate condemnation to the 8th layer of Hell.On page 27 he doesn't sound keen on AutoNumber Primary Keys
I always just thought of an automatically generated Incremental or Random AutoNumber as a unique Static Reference for each record, nothing else.Just don't expect the autonumber that you generate would have any meaning