Instance of Access hangs
Nick via AccessMonster.com
Hello all,
I've been working on a VBA application in Access for a few months now. This
morning, my Access application began to hang in memory, using 97-100% of the
CPU, and the only way to remove it is through the Task Manager. I'm using an
Application.Quit command, which worked fine yesterday, but doesn't close the
instance anymore.
I googled and wasn't really able to find anything that helped with this, so I
thought I would just ask a couple of general questions so I would know what
to look for in my code.
- What causes an application to hang in memory?
- Why is it using almost all of the processor, when I can find no
processes that are running from my program?
It might be worth noting that when the Application.Quit is called, my program
shuts down and Access minimizes. When I re-maximize, it shows an empty
screen, like after you close the database window.
Any help would be greatly appreciated - this is driving me up the wall.
Thanks in advance,
Nick
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200507/1
Br@dley
> Hello all,
>
> I've been working on a VBA application in Access for a few months
> now. This morning, my Access application began to hang in memory,
> using 97-100% of the CPU, and the only way to remove it is through
> the Task Manager. I'm using an Application.Quit command, which
> worked fine yesterday, but doesn't close the instance anymore.
>
> I googled and wasn't really able to find anything that helped with
> this, so I thought I would just ask a couple of general questions so
> I would know what to look for in my code.
>
> - What causes an application to hang in memory?
>
> - Why is it using almost all of the processor, when I can find no
> processes that are running from my program?
>
> It might be worth noting that when the Application.Quit is called, my
> program shuts down and Access minimizes. When I re-maximize, it
> shows an empty screen, like after you close the database window.
>
> Any help would be greatly appreciated - this is driving me up the
> wall.
>
> Thanks in advance,
> Nick
What are you doing in code before trying to quit? Are you setting things
like Set myRS = Nothing ?
Could be corrupted... try repair/compact, try importing all your objects
to a clean database...
Is your database set to compact on close?
--
regards,
Bradley
A Christian Response
http://www.pastornet.net.au/response
Terry Kreft
processing power as it can get, but readily yields it when other apps need
it.
The most likely reason why it hangs is because you haven't destroyed a
pointer somewhere.
The trick to finding why this happens is
a) Find the minimum steps required to cause the application to hang
b) Step through the code used by the minimum steps, ensuring that you
set all object variables to nothing and close all recordsets or connections
that you create.
--
Terry Kreft
MVP Microsoft Access
"Nick via AccessMonster.com" <fo...@AccessMonster.com> wrote in message
news:519245...@AccessMonster.com...
Nick via AccessMonster.com
First off, thanks for the responses.
I tried the suggestions - compact/repair and importing all objects to a clean
DB didn't help, and I have Access '97 so I can't compact on close.
Next, I thought that I must have missed closing an Object Variable. I found
the form that causes the instance to hang, but I've stepped through it about
20 times and even after setting all object variables to Nothing, I still
can't get the program to stop hanging.
Can you all think of anything else that could even remotely cause Access to
hang?
I'm really stuck on this one, it's driving me up the wall. Any further help
would be greatly appreciated.
Nick via AccessMonster.com
Well, it seems I may have found the cause:
I fixed this as a secondary issue, but as it turns out, I believe it was what
caused the program to hang.
When opening recordsets, I had a bad SQL statement; basically, for some
reason I used
"SELECT [Tbl - New Kits Progress].* FROM..."
instead of
"SELECT [Tbl - New Kits Progress].[Unicode] FROM..."
The second SQL doesn't cause the program to hang, but the first did. I don't
know if this was the main cause of more of an underlying cause, but thanks
for your all's help in narrowing down where the error was.
Thanks again,
David W. Fenton
>
> First off, thanks for the responses.
>
> I tried the suggestions - compact/repair and importing all objects
> to a clean DB didn't help, and I have Access '97 so I can't
> compact on close.
>
> Next, I thought that I must have missed closing an Object
> Variable. I found the form that causes the instance to hang, but
> I've stepped through it about 20 times and even after setting all
> object variables to Nothing, I still can't get the program to stop
> hanging.
It's not just a matter of setting to Nothing -- you have to close
the object beforehand, if it's appropriate. In the following code,
you need to close the recordset, but not the database:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDB()
Set rs = db.OpenRecordset([SQL])
[do your thing]
rs.Close
Set rs = Nothing
Set db = Nothing
Why don't you need to close the db variable? Because the CurrentDB
can't be closed -- it's the open one in the user interface.
If, on the other hand, you opened, say, the back end MDB, then you'd
need to close it before setting it's variable to nothing.
Now, why must you close the recordset before setting its variable to
nothing? Because there are two completely different (though related)
memory structures involved. The database variable is a pointer to a
location in memory. When you set it to Nothing, you've cleared the
pointer, but you haven't necessarily released the memory it pointed
to (theoretically, it *should* be released, but it often isn't).
Closing the recordset releases the memory it was using. Then setting
the variable to nothing cleans up the pointer.
Keep in mind that there are also situations where you could have
implicit references left open, rather than explicit ones. I can't
think of a clear example, but if you go through all your code and
implement .Close where appropriate before setting to Nothing and
still have the problem, I'll try to come up with a better
explanation.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
David W. Fenton
> Well, it seems I may have found the cause:
>
> I fixed this as a secondary issue, but as it turns out, I believe
> it was what caused the program to hang.
>
> When opening recordsets, I had a bad SQL statement; basically, for
> some reason I used
>
> "SELECT [Tbl - New Kits Progress].* FROM..."
>
> instead of
>
> "SELECT [Tbl - New Kits Progress].[Unicode] FROM..."
>
> The second SQL doesn't cause the program to hang, but the first
> did. I don't know if this was the main cause of more of an
> underlying cause, but thanks for your all's help in narrowing down
> where the error was.
Sounds fishy to me. The first is perfectly valid SQL. Yes, it
returns more columns, but that isn't relevant.
My guess is that by editing the module where that code existed, you
forced a recompile of something that was causing the problem.
You should read up on DECOMPILE. It's a very useful tool to prevent
VBA corruption from causing problems like this.