Access 97 Won't Close

[gemma-the-husky]

I have pinned this down to the use of a dummy recordset (I thought it related to mousehook ,but it isnt that)

I was setting a permanent link to a dummy table in the backend, to keep the connection open, within my switchboard


dummy rst is a public, global variable

Set dummyrst = currentdb.OpenRecordset("tbldummy")

I dispose of this in the switchboard exit event with
dummyrst.Close


(if i try to add this code to the switchboard close, or unload event, it fails wiith an "object variable not set" error

BUT when I try to close Access, it hangs, as an empty app in the windows task bar, and shows as a process runnnig at 80% odd in task manager.

I have now commented out, both the open and close code, and it now closes down normally.

Any suggestions regarding the right way to close the app.




--------------
thought again, and sorted it

it just needed a

set dummyrst=nothing


funny, i've been a few months not getting to the bottom of this.

 

[Banana]

I'm just going by the memory and could be dead wrong, but I *think* Exit event (Deactive event? Or is it a control on the form?) is only fired if the the form had focus but the hidden form couldn't possibly have had focus.

I'm glad that the Set ... Nothing was what you needed, but I can't help and wonder if the Exit event actually fires consistently?

 

[gemma-the-husky]

not the exit event as such, i meant the exit case of the switchboards handlebuttonlcick event


i originally had the code in the switchboards close event. I tried moving it to the unload event, but both of these didnt fix the problem.

i tried putting it in the handlebuttonclick event for the exit app option, just to see, which also didnt fix the problem - and indeed wouldnt work 100% anyway, as the users could just X Access, rather than using the exit menu item - but it was just to see

finally, i decide to try the set to nothing, which did fix the problem!


memory leakage, eh?

 

[Banana]

Ah, that clarifies a bit. Should I then presume that the exit case in the button handler also get fired should the user "X" the Access?

and indeed would be a permanent fix

I think you meant 'wouldn't' here, yes?



Out of interest, where was the variable initialized? Is it declared in switchboard's module (and thus not truly public but rather is exposed as a property of the form?)

 

[gemma-the-husky]

Ah, that clarifies a bit. Should I then presume that the exit case in the button handler also get fired should the user "X" the Access?



I think you meant 'wouldn't' here, yes?



Out of interest, where was the variable initialized? Is it declared in switchboard's module (and thus not truly public but rather is exposed as a property of the form?)

1. i noticed the wording was wrong, and changed it while you were posting
2. it's declared in a public module (I know its sloppy, but I use dozens/hundreds of public variables - generally putting them in typed structures, to get some intellisense, and to keep those with similar functionality together)

i've took it out of the exit button handler now, and just left it in the forms unload event, as that fires every time.

 

[Banana]

Let's make sure I have understood the problem correctly. You see, I'm very interested in this because a while ago there was a intriguing discussion about whether it's really necessary to have the "Set ... = Nothing" since VBA already does garbage collection, and I suspect your case is a good example of where not having this could cause problems and does definitely appears to be a memory leak.

Anyway.

So it's declared in a public module. I'm not sure where it's actually initalized but I'll assume switchboard's OnOpen event.

You were getting an error if you try to close the recordset in the switchboard's unload & close event. So this code:

dummyrst.Close

fails. However, if you do this:

Set dummyrst = Nothing

in the unload event, it works fine?

 

[Simon_MT]

Dave,

I know this may sound pretty basic but I don't use a Switchboard by a oridinary Form (surprise to surprise) called Menu. This form has a record source of CompaniesCurrent. This holds all the "public information" the database needs and create the permanent link to the backend.

This has worked since Access 1997 and as it works, I've let it as is!

Forgive my ignorance but have never tried Switchboards so I don't know whether or not you can attach a record source to a switchboard?

Simon

 

[Banana]

Simon_MT--

FWIW- I never use switchboards... but to answer your question, they are just a forms. I'm not sure if Access 97 had them but there is a switchboard manager & wizard that creates a switchboard with desired buttons, so it's special kind of form in that it's something you can have Access create for you.

 

[RainLover]

This link may be of interest.


http://support.microsoft.com/?id=190074

 

[Simon_MT]

Thanks Banana

So if it is a Form then can you just leave the Form bound to the Back End through the record source. I know that it sounds rather crude but it was 1997 when I did it. Access 1997 was notorious for demanding memory.

Simon

 

[Banana]

To be honest, I don't know. As noted before, I never used switchboard (it just seems an unnecessary step to me- better to load a main form for the users to start a workflow and provide access to other functions via menu, IMHO. ) The brief time I toyed it, it created a Switchboard table as well which I then presume are to be distributed with the front-end since that any design changes would necessitate updating both table & form and require re-distribution of the front-end, but I could be wrong.

 

[RainLover]

Banana

If when you refer to Switchboard you are refering to MS Access's Switchboard then I totally agree.

(Some people refer to any Menu as a Switchboard)

I have used in my earlier days the MS Access Switchboard and it has caused grief, but this has nothing to do with the close problem.

 

[ChrisO]

To all.

It is a matter of long standing record that the situation described could happen in Access 97.

http://www.mvps.org/access/bugs/bugs0005.htm

Please read the entire link above and also follow the link to the Microsoft article.
Better still, try to implement the failure in Access 97 and see what you get. Many years ago I did reproduce the problem, as stated by Microsoft, but I was also able to find other ways to cause it and other ways to overcome it.

It would be too easy for people to read that link and assume that the failure to set the reference to nothing is the problem, it’s not. The fault that Microsoft has provided, in a reproducible way, has absolutely nothing to do with a failure to dereference DAO. Please read the Addendum in that link carefully.

A cut down version of the failure needs to be posted by Dave and assumptions as to why this particular failure occurred under this circumstance should not be made until that proof is provided.

 

[LPurvis]

Hi all.

I very much agree that (without coming over all Microsoft here) a repro is what's required. If we could get an example mdb which reproduces this then the game's afoot.

I also agree that we shouldn't make any assumptions of the cause.
That would, however, surely include making absolute statements that something is not the cause.

I also have encountered the issue (and cause) as described by the Access Web and the MS KB over the years. If this is different then it's certainly worth looking at, Access 97 or not.

Cheers.

 

[ChrisO]

Thanks Leigh and I gladly accept that critique because it was badly worded on my part.

Second try: It would be too easy for people to read the link to Microsoft and assume that the failure to set the reference to nothing is the problem, it’s not.

Third try: The failure, as stated by Microsoft in that link, has nothing to do with a failure to dereference DAO.

Anyway, I’ll use the name Arvin Meyer called it… “The Boolean bug”.


However…

The problem that Dave is having could still be the caused by what Michael Kaplan has described. All I really wanted to get across was that we should not jump to the conclusion that the cause, as stated in the link to Microsoft, in any way reinforces the cause as stated by Michael Kaplan, it does not.

But the bottom line, as far as I’m concerned, is that I would very much like to get my hands on a reproducible demo of a DAO failure causing this problem because I’ve been looking for one for many years.

Thanks again for the opportunity to try and clean up my poorly written reply.

Regards,
Chris.

 

[gemma-the-husky]

i scanned the link chriso provided, and i think it is describing what is happening

just to clarify

i have an empty linked table called tbldummy
i have a global variable in a module

public dummyrst as recordset



in the switchboard open event i do

set dummyrst = currentdb.openrecordset("tbldummy")

not exactly sure whether this DOES make a difference, but the intention was to maintain a connection to the backend


---------
now when i closed the dbs, the app stayed on the task bar, empty

i pinned it down to the way i released this recordset

i tried it in both the unload and close event and did the following, with error trapping, but suppressed an error report


on error goto fail
dummyrst.close

when i investigated closer i found that this statement was producing an rte, (the object variable not set error referred to in the link) - i think this may be because the recordset has already been disposed off by the time the unload or close events fire (in the same way that dirty records are already saved by that time - although I am not sure) maybe it's the currentdb, that has now gone out of scope

HOWEVER, adding a further


on error resume next
set dummyrst = nothing

in the exit block (so that it runs even if the dummyrst.close failed!), seems to destroy the variable allocation completely, and DOES allow the app to close normally.

hope this clarifies the position


I have tried to reproduce this exactly this with stripped down code, but havent' been able to, so i suspect there's something else happening within my app also

 

[gemma-the-husky]

Dave,

I know this may sound pretty basic but I don't use a Switchboard by a oridinary Form (surprise to surprise) called Menu. This form has a record source of CompaniesCurrent. This holds all the "public information" the database needs and create the permanent link to the backend.

This has worked since Access 1997 and as it works, I've let it as is!

Forgive my ignorance but have never tried Switchboards so I don't know whether or not you can attach a record source to a switchboard?

Simon

the access switchboard is quite slick i think - i actually use this in most apps, often supplemented by a menubar offering a different way of getting at all the same functionality

it takes a switchboard items table as its record source, divided into pages of menu items - each of which can open a form, report etc, or chain to a different menu page

this idea makes it easy to build very complex hierarchical menus. the basic version gives 8 items per page, but i have modified it to 21, which is much better for my apps

so the switchboard does not directly connect to any real data - just the switchboard items table - but each item is programmed to do something - say open a form - by using a standard docmd.openform action


-------
also, i provide the switchboard items as a local table, so i can update it by releasing a new front end - so the switchboard form of itself does not connect to the backend

 

[ChrisO]

Dave.

I can’t reproduce the problem in Access97 and it has no service packs installed.

Could you please post a demo of the problem?

Regards,
Chris.

 

[LPurvis]

I couldn't agree more Chris. We're a long way away from conclusions and that's the point we want to get across. It really doesn't matter where the final result falls - as long as it's arrived at logically and with evidence.
Thanks for clarifying your point.