Access hangs on closing

[SQLSister]

Recently Access has been hanging on closing for me and the users of the program. Not every time but often enough to be annoying. The program was originally developed in Access 97 then converted to 2000, but the decision was made not use an ADP or ADO. (decisons I opposed but lost on.) Instead all the code is written in DAO 2.5. The code is extremely complex - the most complex I have seen in 20+ years of doing databsae work in lots of differnt databases. The database has a SQL Server 2000 backend and uses linked tables for data access. Some of the queries are written in Access, but I am gradually moving them to Stored proceudres to improve performance. Yes I know this monster needs to be redesigned and no management will not allow me to spend the time it will take. It took 4 years to get this thing to production, they won't let me spend another solid year redesigning it when they have other development they need me to do.

Cuurrently I do development in Access 2003 but the file is saves as an Access 2000 database because the users do not all have 2003. There is code that runs when one closes a proposal (which happens on Access closing if you didn't close it manually beforehand.) but this code is running and seems to be creating no problems as you go from one proposal to another and most of the time when you close as you leave the program. There is no custom code at all that specifically runs concerning closing the application. I can find no specific series of events that seem to precipate teh application hanging. When it hangs, it successfully closes the proposal and even the database itself (by all appearances on the screen). However the Access window will not close. It looks to me as if the .ldb file is not closing like it should in many of these cases. i coudln;t find anything in Knowldegebase about this problem nor did I see any applicable threads in my search here.

Anybody have any ideas? I don't even know where to start looking for what would cause this problem.

Questions about posting. See faq183-874

 

[hymn]

The obvious thing is that you still have code running in the back ground. Have you set everthing back to null "" or zero

Hope this helps
Hymn

 

[SQLSister]

I do close all recordsets and references to the database object. All the variables are local to the sub or function (except ouput variables of a function of course). Do they need to be set to null, etc at the end of a sub or function? None of them are and that has never been a problem before the last two weeks or so.

And it's an intermittent problem with no particular pattern to what happens. Sometimes it happen just if you open a proposal, look at it and make no changes and then close the app, sometimes it doesn't . Sometimes it happens after extensive changes or after not opening a proposal but doing some maintenance on the parts list, etc. But no pattern to why it happens.

The only code that I know of that runs when closing a database is that if a proposal is open, it checks to see if there are certain validation issues in the proposal (things which are allowed for various reasons but not the preferred method not data errors) and won't close it if you say yes to the message box that tells you about it, but if you say no, then it closes the form. I know this part is operating correctly and the form properly closes. If the last command in the code had not yet executed the form wouldn't be closed. Nothing else programmically happens after the form is closed.

Questions about posting. See faq183-874

 

[PHV]

before the last two weeks or so
No network change ?
Have you tried to compact/repair the database ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hymn]

for it to hang something is open or there is a leakage 9 times out ot 10. Or somebody has been tinkering with your SQL Server 2000 or your Sever

Hope this helps
Hymn

 

[SQLSister]

I have tried compact/repair. That did not clear the problem.
No network changes that I am aware of although I will go check. But our network admin has been out of the office for 90% of the last 3 weeks so I would tend to doubt it unless we got some automated update.

I'm the SQL Server dba and I know that there has been no change to the way the server is connecting. The SQL Databse itself is fine and is not hanging at all on anything. nothing sent to or from the SQL server is taking more than a second or two at max. There are no deadlocks or erroring out. I did change the schedule on the backups, but that happens at night.

If there is a leakage I'll be darned if I know how to go through several thousand pages of code to find it. Any suggestions for how to search for this?

Questions about posting. See faq183-874

 

[hymn]

Sorry but no
your best bet is to bump this occasionally and see if any one else can help you

Hope this helps
Hymn

 

[MoLaker]

I agree with those who've stated "something is left open". Since the problem seems intermittent, it may be data dependent - such as an unhandled NULL or something like that. If "on error goto ..." error handling is used and an error is not handled correctly (such as exiting a routine w/o cleaning up a connection), a connection may be left open even though you say

I do close all recordsets and references to the database object.

Try rem'ing out "on error" statements and see if an error pops up.

 

[SQLSister]

Well it's something to try. May take awhile to go through all the pages of code to do this. But at least it's a way to start looking. Thanks to all for your help.

Questions about posting. See faq183-874

 

[Frink]

Hallo,

I know this can happen if you use frame or controls in expressions without explicitly specifying the Value.

ie.
If Me!fraYesNo Then Msgbox "Yes"

Change this to:
If Me!fraYesNo.Value Then Msgbox "Yes"
or
If Me!fraYesNo <> 0 Then Msgbox "Yes"

This might be the case in your database.
Not sure if it is also a problem in Check boxes.

- Frink

 

[docdevore]

We experienced this problem. Our main Access 2000 switchboard launches other Access 2000 instances. We noticed that after our last application (Access 2000 switchboad) closed, that MSACCESS was still a running thread, and was consuming most of the CPU.

We made a simple change to the code. Rather than use Application.Exit, we used a macro to exit. The program no longer hangs after exit.