Run-time error 2486 -- possible cause?
David W. Fenton
i.e.:
"You can't carry out this action at the present time.@You
tried to run a macro or used the DoCmd object in Visual Basic
to carry out an action. However, Microsoft Access is
performing another activity that prevents this action from
being carried out now. For example, no actions on a form can
be carried out while Microsoft Access is repainting a control
or calculating an expression.@Carry out the action later."
Well, I searched Google
(http://groups.google.com/groups?as_q=error%202486&as_ugroup=comp.da
tabases.ms-access&hl=en) and a lot of people have had this problem
in Access 2000 in particular. I was pulling my hair out trying to
figure out why the problem would happen. It was *not* happening in
Access97 (where I'm developing the app), but was happening semi
-randomly, but fairly reliably, in A2K, the platform the client
uses.
Well, finally I figured it might be corruption in code, even though
I had done a decompile/recompile cycle before converting to A2K.
So, I went through it again more than once, and the second time got
an error, something like "unrecognized project version" (or
something like that -- I stupidly didn't write it down), and the
decompile was failing. Then I compacted again and re-attempted the
decompile, and this time it worked. When I recompiled, it told me
that one of my class modules was corrupt (when I opened it, it had
extra lines before the declarations section). This made sense
because I'd seen an error with a recordset declared in that class
module getting reset and not properly re-initialized when it never
should get closed during its own scope, and the class module was
being used in the OnCurrent event of the form where the problem
first emerged.
So, I recreated the class module, recompiled, decompiled, etc., and
then the problem went away.
Given that form modules are class modules of a sort, it seems
likely to me that most of the problems described in the articles
listed in the Google search above were probably due to corruption.
That would explain why the problem emerges so often inexplicably,
after no changes in code, and why it is so unpredictable.
The reason I didn't consider corruption earlier is simply because
I'm so religious about decompiling before distribution. And I
really didn't imagine that code corruption would survive a
decompile/compact sequence *and* a conversion to A2K. But, there it
is.
Maybe this will help someone else!
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
peter walker
peter walker
"David W. Fenton" <dXXXf...@bway.net> wrote in message
news:91A9E00F3df...@news-server.nyc.rr.com...
Gary Rockley
The subsystem has the ability to store crap? Perhaps this is why it
periodically 'dumps' records? ;-)
Warmest regards,
gary
Allen Browne
It certainly has been asked frequently, so if this identifies a cause, we
are grateful.
--
Allen Browne, Microsoft MVP (Most Valuable Professional)
Perth, Western Australia
Tips for MS Access users at:
http://users.bigpond.net.au/abrowne1
"David W. Fenton" <dXXXf...@bway.net> wrote in message
news:91A9E00F3df...@news-server.nyc.rr.com...
Steve Jorgensen
apps. I'm not sure what makes my apps different than what most others
are reporting, but if anyone does have these same issues, here's a
description and solution...
Every few weeks or months, my develompent front-end database will
develop some strange corruption and report some error that makes no
sense such as reporting an unknown method in a stand-alone classs
module when the code was working before, and neither the calling code
nor the class module has changed.
The standard advice is normally to run /DECOMPILE, and that's supposed
to fix everything, but in my case, it rarely does. After decompile,
either the error still occurs, or some new and equally inexplicable
one takes its place such as reporting an invalid data type for a
private defined type within the same module.
I find that, in order to fix this, I usually have to delete and
recreate the modules in question, compact, and recompile. I usually
don't have to /DECOMPILE. Sometimes, Access bombs when I try to
delete one of the objects, so I have to decompile first. That doesn't
fix the problem, but usually makes it possible to delete objects
without crashing Access.
Now, after going through whatever rounds of deleting, recreating,
decompiling, and compacting, there may still be problems, but I can
usually get the thing finally fixed by deleting and recreating a few
modules that most other modules depend upon such as my error handling
class module and general utility function modules. Actually, I've
learned to now do this in the first step along with deleting and
recreating the ones directly involed in the problem to save me steps
later on.
Note that to delete a form or report class module, the procedure is to
set Has Module to No and save the object. Just deleting all the text
fdrom the module won't fix the problem.
peter walker
etc.
However with VBA code (especially class modules ... APC_E_ZOMBIED???) there
is something fruity
going on. Experimentation and playing with the VBA SDK would *occasionally*
produced the same results...what arrives into storage is not what you sent
there, this can include the canonical source text as David mentioned,
especially if things are complicated with several complex streams. That
could of course be my hamfistedness in missed commits or partial saves. I am
not sure whether *Access* form / report objects ( as opposed to VBA Forms )
are stored in the project storage (with the code) or in a separate part of
the storage, i.e.: The host specific data section instead or the VBA project
section or access tables. They can certainly come unglued from their code
though. Since Access seems to store data OK, and the host application is
responsible for storage / streams, I suspect an Access problem in
manipulating the project streams / storage but it could still be a problem
with the VBA APC.
It would be interesting to find out if the Word and XL guys had project
corruption problems as well.
BTW
What's the current version of VBA shipping with XP?
peter walker
"Gary Rockley" <g...@mn.mediaone.net> wrote in message
news:3C5CD443...@mn.mediaone.net...
David W. Fenton
<vhcq5ucplg9icqcg5...@4ax.com>:
>Just out of curiosity, how are you doing the convert to A2K?
>Using A2K's built in conversion tool, or are you creating a new
>A2K DB and importing the objects?
I'm using the A2K conversion tool.
It's too much work to import into a new A2K DB, as then I'd have to
fix the references, recreate all the startup procedures and add my
custom properties.
One thing that wasn't clear from my post -- I found the corruption
in the A97 version.
It's interesting that this project, which has more standalone class
modules than any app I've ever developed before, has had more
corruption issues than any other I've ever done. I've twice before
completely recreated the MDB file, the last time because of a
corrupted form that just wouldn't go away. I forget why I did it
the first time. If I hadn't found the corrupted class module in
this case, I probably would have ended up recreating the db again.
In light of what Steve said, I think one must be more careful when
using class modules extensively, and simply expect more corruption
to emerge. They are way too useful to avoid entirely!
Gary Rockley
peter walker wrote:
> BTW
> What's the current version of VBA shipping with XP?
Hi Peter,
Per
http://www.microsoft.com/partner/products/officefamily/officexp/AtAGlance--VBA.asp
it is 6.3
Regards,
gary
David W. Fenton
>On Sun, 03 Feb 2002 22:17:16 GMT, dXXXf...@bway.net (David W.
>Fenton) wrote:
>>It's interesting that this project, which has more standalone
>>class modules than any app I've ever developed before, has had
>>more corruption issues than any other I've ever done. I've twice
>>before completely recreated the MDB file, the last time because
>>of a corrupted form that just wouldn't go away. I forget why I
>>did it the first time. If I hadn't found the corrupted class
>>module in this case, I probably would have ended up recreating
>>the db again. In light of what Steve said, I think one must be
>>more careful when using class modules extensively, and simply
>>expect more corruption to emerge. They are way too useful to
>>avoid entirely!
>
>Ah! Personally, I don't do this any more (all my clients are
>sticking with, or have gone back to) Access 97, but when I was I
>was using Application.LoadFromText to avoid corruption. I seem to
>recall quite a thread on this from back then, and that's when I
>started using it. Yes, setting all the references is something
>that needs to be done in the new database, but at least it gets
>around the corruption problem nicely....
Well, lots of new news today.
After about 6 hours of working for the client with no errors, the
problem came back this afternoon, and when I rechecked the original
A2K MDB that I gave them Friday afternoon (after I fixed the
corruption), it was back for me, too, even though it had *not*
occurred in my testing on Friday afternoon before I shipped it off
to the client as a permanent fix -- I only spent about 20 minutes
trying to get it to fail, but I went through all the steps that
today were producing the error (at least, so I thought!).
VERY FRUSTRATING!!!
Well, this afternoon I rebuilt the database from scratch (importing
into a new A97 MDB), and that didn't help.
Then it occurred to me that perhaps it was an Access2K-only error
(it does *not* show up in A97, only in A2K), so I thought maybe as
a workaround until it's solved, the client could run the A97
version in A2K, unconverted.
So, I made a copy of the development MDB and opened it in A2K
without conversion.
THE PROBLEM STILL OCCURS.
So, I figured I had a major corruption problem and put it aside
until I got home from rehearsal this evening.
Well, I wrote my code to do SaveAsText and LoadFromText and did
that and THE PROBLEM IS STILL THERE.
Then I noticed a nagging little thing that I'd been thinking I
needed to fix: one of the subforms was showing blank data in one of
its columns periodically, and tonight, I happened to notice that it
didn't seem to be happening until JUST BEFORE I GOT ERROR 2486.
Hmm.
So, I changed the controlsource for that field on that subform,
and:
(drumroll, please)
THE PROBLEM IS GONE!
And it's something that was implemented in the version in which
this problem first occurred, so I'm pretty sure this has something
to do with the problem (whether it's decisive or not, I don't
know).
Here's the control source:
=Left('*',Abs(([Parent]![CurrentJobID]=[EmploymentHistoryID]))) _
& [FirmName]
What's it's doing is testing if the subform's EmploymentHistoryID
is the same value as the parent form's CurrentJobID, which means,
of course, that the record for which it's true is the current job.
That returns 0 or -1, so I take the absolute value of that (0/1)
and then use Left("*", 0/1) to stick an asterisk at the beginning
of any FirmName that is the current job (otherwise, there's no
indication of which is the current job, other than that the top one
in the list is the latest job, but if the person left that job, the
top row is *not* their current job).
Well, very interesting, no? Here I thought I was being
extraordinarily clever with the controlsource (it's a very "Paradox
kind of thing to do," is the way I think of it, as Paradox didn't
have UDFs available in reports, so you had to be very clever with
using expressions to return parts of substrings to simulate
conditionals), and it turns out it's something that A2K doesn't
like. Interestingly, I'd almost decided to remove it, anyway, as
it's a little slow to populate (just like most calculated controls
on forms), and didn't look good.
There's a side issue that I haven't traced down yet that may or may
not be related that is a bug in the program that's causing a
certain persistent public recordset to get reset. I haven't yet
traced it down, but it seems independent of the other problem.
If it turns out to be the problem, I'll let you know.
The really bizarre thing about all of this is that how the form
with the problematic subform was being opened tended to effect the
results. There are two major ways the form gets opened, both of
which occur through a called function that sets the form's
recordsource. In one case (the most common case), the
recordsource's WHERE clause is a an IN() with the subquery
returning the PK of the main form based on whether items have been
checked off for loading into the main form from a "list view" form
(a temp table driven by a very complex Query By Form interface).
The other method is the old doubleclick-an-item-in-a-continuous
-form-list method, and this opens the form with one record, with
the WHERE clause set to a single value for the PK of the form.
And this latter one is the one that seems to produce the problem
more reliably!
Now, the problem seems to have something to do with problems with
the control referencing system in subforms, and this somehow messes
up all sorts of operations in the parent form. Once that goes
haywire, every other operation gets killed somehow, and can't be
completed.
I won't be able to work on this again until Wednesday, but I'll see
if I can do a demo database in which the error is reproducible. If
I can do that, then I can bring this problem to Microsoft's
attention, as it seems that it's a very frequent problem with a
very strange and elusive cause.
I'll keep you all posted as more unfolds.
===
25 minutes later:
OK, I've tested it, and I'm pretty sure I've identified what's
kicking off the problem.
I fixed the reset recordset (stupidity on my part for mis-using a
public database variable for the wrong reason, and setting it to
Nothing! Very strange this has popped up only now), and fixed
another small problem that was related, and then I was testing out
the updated app without the funky controlsource.
So, I had a bright idea: everything is working great with the plain
controlsource. What if, while the form is open, I set it to the
calculated controlsource?
Well, I went to the A2K immediate window, assigned the
controlsource, and BINGO! Up pops the error!
So, I can now reliably trigger the error in this application.
Unfortunately, setting the controlsource back to the original value
does not allow recovery from Error 2486.
This is so freaky it's beginning to scare me.
Well, I seem to have a fix for the client, so I'll leave it and go
to bed. I'll update the newsgroup as I figure out more about it.
Steve Jorgensen
What if you use an IIf rather than trying to use the numeric
interpretation of the boolean result directly. That's what I
sometimes have to do in queries of ODBC datasources where Access
thinks it can pass-on and ask the server to treat a boolean comparison
result as a numeric which it will not do.
I know this is an unrelated problem, but it may have the same
solution.
On Tue, 05 Feb 2002 05:00:03 GMT, dXXXf...@bway.net (David W.
Brendan Reynolds
may be, but I have found that sometimes in Access 2000 expressions in
control properties that refer to controls seem to work more reliably if I
include the 'Form' prefix. For example, in Access 97 I would often use the
following expression in the Default Value property of a combo box:
"=NameOfCombo.ItemData(0)". I found this frequently failed in Access 2000 -
no error, just a blank combo box. Changing the expression to
"=Form!NameOfCombo.ItemData(0)" seems to fix it - I say 'seems' because, as
I say, I haven't tested it systematically enough to say whether it *always*
fixes it or *permanently* fixes it.
I really should start keeping better notes! :-(
--
Brendan Reynolds
bren...@indigo.ie
If it's not plain text, without attachments,
I'm not reading it.
----------------------------------------
"David W. Fenton" <dXXXf...@bway.net> wrote in message
news:91AC8F7dfe...@news-server.nyc.rr.com...
David W. Fenton
<3c5f9647....@news.earthlink.net>:
>What if you use an IIf rather than trying to use the numeric
>interpretation of the boolean result directly. That's what I
>sometimes have to do in queries of ODBC datasources where Access
>thinks it can pass-on and ask the server to treat a boolean
>comparison result as a numeric which it will not do.
>
>I know this is an unrelated problem, but it may have the same
>solution.
Well, it occurred to me while lying in bed last night that I wasn't
allowing for Nulls, and maybe that was the problem, as ASC() can't
accept a null value. Except that I know for a fact that the data I
was working with was all records that had non-null CurrentJobIDs,
simply because I know what records I was looking at.
Very strange.
I'll be working on the project tomorrow and will test a Null
-handling solution and an IIf()-based solution and get back to
y'all.
And my evening's amusement, if I'm not burned out, will be to try
to produce an example database with the problem reliably occuring
in it. If I can do that, I'll contact Microsoft, too.
David W. Fenton
>One thing I want to mention however:
>
>>=Left('*',Abs(([Parent]![CurrentJobID]=[EmploymentHistoryID]))) _
>> & [FirmName]
>
>The Left function returns a variant. You appear to want a string.
>Thus, use Left$ instead which will force the return into a string.
Well, I think it may be that I didn't allow for CurrentJobID to be
Null (which it can be, but which it wasn't in any of my data).
>Also, ABS returns a number. Converting a number to a string
>without explicitly saying that you need a string can cause a
>leading space to appear without your asking for it. (The space is
>a place holder for the +/- sign, which is suppressed when the
>return is positive, leaving the space.)
But I'm *not* converting a number to a string. I'm using the number
as the numeric argument for Left().
>You might want to use:
>
>=Left('*',CStr(Abs(([Parent]![CurrentJobID]= _
> [EmploymentHistoryID])))) _
> & [FirmName]
>
>to ensure that you don't get out something like:
>
>* 34<whatever>
You've misread the formula:
=Left("*",1)
is what I get when the two are equal, and
=Left("*",0)
when the two are not, because:
[Parent]![CurrentJobID]=[EmploymentHistoryID]
(or, better still:
Val(Nz([Parent]![CurrentJobID],0))=[EmploymentHistoryID])
returns -1 or 0. All I'm doing is taking the absolute value of the
Boolean result to get the length of the string "*" that I want to
return.
>Unless, of course, that's what you want! (Well, in that case, I'd
>put the space in myself to ensure that what I want is what I get!)
I confused myself writing the damned thing in the first place.
As I said to Steve, I'll check if either IIf() or properly handling
the Null fixes it. Another thing I've considered is eliminating the
[Parent] reference, and creating a control in the header of the
subform that is an absolute reference to the parent form (in this
case, the subform is never going to be used anywhere else, but
that's pretty uncommon with my subforms), and then refer to that
control instead.
It wouldn't surprise me if the [Parent] reference were to blame.
Steve Jorgensen
Fenton) wrote:
...
>
>It wouldn't surprise me if the [Parent] reference were to blame.
That reminds me - I've had problems in Access 97 trying to use the
parent property from code because the subforms are initialized prior
to the parents. I've never had the problem with calculated controls,
but in A2K, perhaps this has become an issue.
David W. Fenton
I've never had that kind of problem.
But most of my references to parents are not to data controls, but
to formatting and positioning, and often Select Case statements
(based on which form the subform is running in). But most commonly
is color settings form headers and such.
So, the ongoing saga continues.
Today I tried a new approach, creating an invisible control in the
subform's header that refers to the parent form's CurrentJobID.
That control was called "fldCurrentJobID" and it's controlsource
was:
Val(nz([Forms]![frmContact]![CurrentJobID],0))
Then, I changed the controlsource of the Firm text box to:
IIf([fldCurrentJobID]=[EmploymentHistoryID],"*","") & [FirmName]
This worked for a very, very long time, but then at one point I
went back to the form, and all the rows of the subform had #ERROR
in them, and then when I tried to run anything, it would give me
Error 2486.
So, I took that out, too. Other than a DAO function, or joining the
subform's main table to the parent table (which would be very
expensive in terms of the join), I'm at a loss.
I'll experiment over the next couple of days to see if I can
abstract it into a reproducible form.
peter walker
the tests. It may be a recursive loop in the expression service and
Eventually a stack or stalled / blocked thread problem arises depending on
other process times.
peter walker
"David W. Fenton" <dXXXf...@bway.net> wrote in message
news:91ADDDA5Cdf...@news-server.nyc.rr.com...
David W. Fenton
<nAo88.1326$BE4....@newsfeeds.bigpond.com>:
>It may be interesting to observe the upx time being used by Access
>during the tests. It may be a recursive loop in the expression
>service and Eventually a stack or stalled / blocked thread problem
>arises depending on other process times.
You mean just looking at task manager? How do I do that?
In any event, new news:
Today the client started using the fixed app, and ran into the
problem again. I had eliminated the conditional controlsource, but
I forgot to delete the hiddent field in the subform header that was
referring to the parent form. Keep in mind that it was an absolute
reference, not relative (i.e., it was
"=Val(Nz(Forms!frmContact!CurrentJobID,0))"). And that alone,
unreferenced by any of the rows, triggered the problem.
I have one isolated report of 2486, but the user has a tendency to
mis-report bug numbers. We haven't been able to reproduce it,
either there or here.
Over the weekend, I'll try to see if this is reproducible outside
the application in question. If it's not, then I'll test the app
with the problem with certain complicated features disabled (the
app keeps a lot of big recordsets open all the time).
Whatever I find (or not), I'll follow up with it.
Gary Rockley
Just a thought (or daft suggestion), and I don't know if its too late to
be useful since you seem to maybe have it solved, but had you considered
outputting to file, relevant values of any variables or object
properties when the error occurs.
In a general sense I know that's impractical, but since you seem to have
the approximate cause of the problem, so it may be easier to do. I'm
prompted in this by recalling a situation where a db would always crash
with a gpf at a certain record. Although I believe that problem was
related to record level corruption that Repair did not catch, it
occurred
to me well after the event that had I tried to output either the
recordset values, variables etc. to a text file, it may have saved some
time.
Regards,
Gary
peter walker
Because its not consistent I'd still bet on the thread issue / the dreaded
WaitForSingleObject.
It will be a boon if you can actually create a DB that recreates the
problem. Then it can be tried across platforms.
peter walker
"David W. Fenton" <dXXXf...@bway.net> wrote in message
news:91AEBCE5Adf...@news-server.nyc.rr.com...
David W. Fenton
<3C6319EB...@mn.mediaone.net>:
>Just a thought (or daft suggestion), and I don't know if its too
>late to be useful since you seem to maybe have it solved, but had
>you considered outputting to file, relevant values of any
>variables or object properties when the error occurs.
>
>In a general sense I know that's impractical, but since you seem
>to have the approximate cause of the problem, so it may be easier
>to do. I'm prompted in this by recalling a situation where a db
>would always crash with a gpf at a certain record. Although I
>believe that problem was related to record level corruption that
>Repair did not catch, it occurred
>to me well after the event that had I tried to output either the
>recordset values, variables etc. to a text file, it may have saved
>some time.
I'm not sure how that would help, to be honest. I also don't see
how I could trigger it, except if I trapped for that specific
error.
David W. Fenton
>> You mean just looking at task manager? How do I do that?
>Guess I'm too used to working with NT were this is possible.
Well, NT is the environment in which it's running and in which I'm
testing it (I can't produce the problem on my Win95 development box
because I don't have A2K installed there).
I just don't recognize the term "upx."
>Because its not consistent I'd still bet on the thread issue / the
>dreaded WaitForSingleObject.
>It will be a boon if you can actually create a DB that recreates
>the problem. Then it can be tried across platforms.
Well, I'm actually not going to have time to get to it this weekend
-- too much other work to do!