Help! Access won't close - have to end task

[brian.naille]

Hi all,

I am a newbie to this forum and am hoping any of you can help. I have a MS Access 2003 mdb which is giving me fits. The issue I am getting is that when you try to close the db, it will exit the database but NOT MS Access. You are left with an Access empty window which will not exit and has to be end tasked.

I've looked at a bunch of forums and found suggestions about objects that need to be closed before closing the db. I tried that to no avail - I wrote a function that cycles through and closes all objects (forms, modules, workspaces, databases, recordsets, etc.). The first time I tracked this error down, I finally found the problem to be with a certain global variable. Once it got assigned, the app wouldn't close. So, I wrote a work-around and voila! But, now it's back again.

I have now added a section that nullifies all of my global variables (actually they are "public" not "global") but it doesn't seem to help either.

Is there any way I can figure out what resource is hanging on and keeping Access from properly closing? I don't think you can trigger an end task from VBA but it's sure tempting since my head is getting sore from banging against my desk. ANY suggestions would be appreciated (aside from quitting my job and taking up crochet).

Thanks!

[OTWarrior]

What code are you using to close Access? is it not just?

Quit

if you are using

Me.Close
or
[Close]

Then these will only close the (active) form.

Can you show us how you are trying this?

[brian.naille]

Hi, thanks for your response.

I have a form that stays open the entire time the database is open. When the form is closed, I want to close the app. In the Form_Close sub I have:

GlobalModules.CloseObjects
Application.Quit acQuitSaveAll

The CloseObjects function is one I wrote to cycle through and close out all objects. It also sets all references (worksets, databases, recordsets) to nothing.

The strange thing is that I seem to have isolated the subroutine which is causing it to exhibit this problem. It is part of a button on the form that always stays open. However, I've gone through that sub to make sure that all references are closed and set to nothing. At one point, I thought I'd found the problem - if I commented out one particular line where I was assigning a value to a global variable, the problem went away. Sound weird? But now, I don't use that variable anymore but the problem has come back. It's still in that same script.

Any ideas?

Thanks!

[CreganTur]

Can you please share the code that involves this global variable? If that's what's causing the problem, then seeing it might help us identify it.

[brian.naille]

Since I work for a federal agency, I have to be extremely careful what I post in terms of my code. If it has even the slightest thing proprietary I would be in big trouble. :-)

However, the place I've isolated at this point is in a function that I'd actually copied from someone on another forum. So, it is not proprietary.

I thought previously that the problem was in a variable assignment, but I was a bit off. I've isolated it now to this statement:

---------------------------------------------------
SetDBProperty "StartupShowStatusBar", db, True
---------------------------------------------------


Here is the SetDBProperty function (which again, I borrowed from someone's internet post):

Public Sub SetDBProperty(propname As String, propdb As Database, prop As Boolean)
'20090106 Brian Naille
'Added module to set startup properties for security
'Set passed startup property.
Dim dbs As Database
Dim prp As Object
Set dbs = CurrentDb()
On Error Resume Next
dbs.Properties(propname) = prop
If err.Number = 3270 Then
Set prp = dbs.CreateProperty(propname, propdb, prop)
dbs.Properties.Append prp
End If
Set dbs = Nothing
Set prp = Nothing
Exit Sub
End Sub


Now, if I comment out the offending statement, I no longer have the problem. Maybe I can find an alternative way to set that property. Beyond that, any ideas what is happening?

Thanks!

[brian.naille]

Addendum: This is what comes before the offending statement (so you can tell what "db" is referencing)

Dim db As Database
Set db = CurrentDb()

[OTWarrior]

I would remove the On Error Resume Next and instead use a full error handler (in order to see what falls over). Having exit sub and end sub next to each other is kind of redundant, remove the exit sub.

I thought the issue was trying to close the database. This piece of code you have posted is to set the properties. Since you work for a federal agency, could you post your code, but rename anything that you don't want public. (I work for local government, so often have to do this).

[Imdabaum]

I think OTWarrior has it though. There is probably some error occurring and because the error handling is saying ignore it and go to the next line it sticks because you are trying to do something that just isn't possible so it just cycles error->resume skip back to the error-->resume skip back to the error... etc.

Once you trap the error and can identify it, you'll be a lot closer to solving the problem.

And you'll probably figure it out before you even have to copy that proprietary code. :)

[CreganTur]

Personally, On Error Resume Next should never be used. Resume should only be used for very specific instances and should be part of a more robust error trapping routine that includes a catch all that shows you the error number at least, if not that and the description. Otherwise errors will cause these sorts of problems and go undiagnosed.

[OBP]

See this Thread for other reasons why Access doesn't close.
http://forums.techguy.org/business-applications/907953-solved-access-will-not-close-2.html