Strange corruption problem in A2K

Hi all,
I have a fairly complex form in Access 2000. In particular, it has two
subforms on separate tabs of a tab control. For the last two weeks,
I've encountered the dreaded :
"You can't carry out this action at the present time." error, runtime 2486.
Subsequently Access will not exit the form, and shuts down uncleanly.

Looking back on the messages here, this seems to be an indication of a
corrupt object in the .mdb. So I imported all the db objects into a
blank mdb and rebuilt the subform from the ground up as well as all the
modules. This worked fine: the message did not appear
....... until I changed the subform to continuous view.

Can anyone explain this to me? Can anyone suggest an efficient way of
determining which object(s) might be corrupt, and eliminating that
corruption? General advice on handling this situation would be appreciated.

Along those lines, does anyone know if VS.NET has a continuous form
control like Access?

Many Thanks,

Eric

re: Strange corruption problem in A2K

Hi Eric

First thing to check is that you have the latest service pack for Office
2000 and also for JET 4. Locate msaccess.exe (typically under Program
Files\Microsoft Office\Office). Right-click and choose Version. You should
see 9.0.0.6620. Locate msjet40.dll (typically under windows\system32). Its
minor version should start with an 8, i.e. 4.0.8xxx.0. If not, get the
updates from the Downloads section at support.microsoft.com.

It is possible to import the corruption into a new mdb. Decompiling first
might help. To decompile a copy of the database by enter something like this
at the command prompt while Access is not running. It is all one line, and
include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
Then open the database and compact it:
Tools | Database Utilities | Copact

Then create the new mdb, and uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://members.iinet.net.au/~allenbrowne/bug-03.html
You can then import the objects into this new mdb.

Now you want to check for any code that could be causing concurrency issues.
This can be a big task, but you can start with code in the modules of the
problem form, its 2 subforms, or any procedure called by those 3.

In particular:
1. Explicitly save the record in the form before doing anything that may
need that done, such as moving record, closing the form, viewing or altering
the same data in a recordset (including the RecordsetClone of the form),
opening another form/report that accesses the same data, applying/removing a
filter, changing the sort order, reassigning the RecordSource, executing an
action query on the same table(s), etc.

2. If the RecordSource of the form or subforms includes any memo fields,
hyperlinks, or OLE objects (i.e. fields accessed via a pointer), be
super-cautious about offering 2 places where the user can modify these. It
is very easy to run into concurrency issues with these.

3. Make certain your code cleans up after itself, i.e. explicitly close what
you open (and only what you open). In the error recovery/exit section of
your module, set all objects to Nothing. Objects that are not dereferenced
can cause concurrency issues.

Of course, if the database is used by more than 1 person at a time, make
sure it is split, and each user has their own copy of the front end.

For other tips on avoiding corruption, see:
http://members.iinet.net.au/~allenbrowne/ser-25.html
Particularly, #5 can help you avoid this particular problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Eric E" <whalesuit@bonbon.net> wrote in message
news:jqmdndVz-_b5jfrcRVn-ig@speakeasy.net...[color=blue]
> Hi all,
> I have a fairly complex form in Access 2000. In particular, it has two
> subforms on separate tabs of a tab control. For the last two weeks, I've
> encountered the dreaded :
> "You can't carry out this action at the present time." error, runtime
> 2486.
> Subsequently Access will not exit the form, and shuts down uncleanly.
>
> Looking back on the messages here, this seems to be an indication of a
> corrupt object in the .mdb. So I imported all the db objects into a blank
> mdb and rebuilt the subform from the ground up as well as all the modules.
> This worked fine: the message did not appear
> ...... until I changed the subform to continuous view.
>
> Can anyone explain this to me? Can anyone suggest an efficient way of
> determining which object(s) might be corrupt, and eliminating that
> corruption? General advice on handling this situation would be
> appreciated.
>
> Along those lines, does anyone know if VS.NET has a continuous form
> control like Access?
>
> Many Thanks,
>
> Eric[/color]

re: Strange corruption problem in A2K

Wow Allen, that's a very thorough answer. Thanks for all the advice -
I'm working through the points now. In the meantime, can I ask a
background question:
What's the source of the concurrency issues you've described? Is this
Access locking tables and rows that have to do with these objects, or
its it something else? Is there somewhere I can read a little more
about this?

Many thanks,

Eric

Allen Browne wrote:[color=blue]
> Hi Eric
>
> First thing to check is that you have the latest service pack for Office
> 2000 and also for JET 4. Locate msaccess.exe (typically under Program
> Files\Microsoft Office\Office). Right-click and choose Version. You should
> see 9.0.0.6620. Locate msjet40.dll (typically under windows\system32). Its
> minor version should start with an 8, i.e. 4.0.8xxx.0. If not, get the
> updates from the Downloads section at support.microsoft.com.
>
> It is possible to import the corruption into a new mdb. Decompiling first
> might help. To decompile a copy of the database by enter something like this
> at the command prompt while Access is not running. It is all one line, and
> include the quotes:
> "c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
> "c:\MyPath\MyDatabase.mdb"
> Then open the database and compact it:
> Tools | Database Utilities | Copact
>
> Then create the new mdb, and uncheck the boxes under:
> Tools | Options | General | Name AutoCorrect
> Explanation of why:
> http://members.iinet.net.au/~allenbrowne/bug-03.html
> You can then import the objects into this new mdb.
>
> Now you want to check for any code that could be causing concurrency issues.
> This can be a big task, but you can start with code in the modules of the
> problem form, its 2 subforms, or any procedure called by those 3.
>
> In particular:
> 1. Explicitly save the record in the form before doing anything that may
> need that done, such as moving record, closing the form, viewing or altering
> the same data in a recordset (including the RecordsetClone of the form),
> opening another form/report that accesses the same data, applying/removing a
> filter, changing the sort order, reassigning the RecordSource, executing an
> action query on the same table(s), etc.
>
> 2. If the RecordSource of the form or subforms includes any memo fields,
> hyperlinks, or OLE objects (i.e. fields accessed via a pointer), be
> super-cautious about offering 2 places where the user can modify these. It
> is very easy to run into concurrency issues with these.
>
> 3. Make certain your code cleans up after itself, i.e. explicitly close what
> you open (and only what you open). In the error recovery/exit section of
> your module, set all objects to Nothing. Objects that are not dereferenced
> can cause concurrency issues.
>
> Of course, if the database is used by more than 1 person at a time, make
> sure it is split, and each user has their own copy of the front end.
>
> For other tips on avoiding corruption, see:
> http://members.iinet.net.au/~allenbrowne/ser-25.html
> Particularly, #5 can help you avoid this particular problem.
>[/color]

re: Strange corruption problem in A2K

When you begin editing a record, Access copies it into the buffer, and then
writes the buffer back when you save.

If you then edit another instance of the same data in another form or in
another instance of Access and save that, the original buffer copy you were
working with previously is now out of date, so when you go to save it you
receive the conflict dialog.

"The same data" means the same record, or records in the same buffer page
(2k text/4k unicode), and data referenced by the same pointers (for BLOB
fields).

It is always worth a little extra thought/code to avoid these issues and
keep things up to date, even though Access is very good at sorting these out
in general.

My personal recommendation is to use "No Locks" (optimistic locking, the
default), but not "Row-level locking" (performance loss, and possibily some
issues.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Eric E" <whalesuit@bonbon.net> wrote in message
news:2NCdncapIfN62fHcRVn-oA@speakeasy.net...[color=blue]
> Wow Allen, that's a very thorough answer. Thanks for all the advice - I'm
> working through the points now. In the meantime, can I ask a background
> question:
> What's the source of the concurrency issues you've described? Is this
> Access locking tables and rows that have to do with these objects, or its
> it something else? Is there somewhere I can read a little more about
> this?
>
> Many thanks,
>
> Eric
>
> Allen Browne wrote:[color=green]
>> Hi Eric
>>
>> First thing to check is that you have the latest service pack for Office
>> 2000 and also for JET 4. Locate msaccess.exe (typically under Program
>> Files\Microsoft Office\Office). Right-click and choose Version. You
>> should see 9.0.0.6620. Locate msjet40.dll (typically under
>> windows\system32). Its minor version should start with an 8, i.e.
>> 4.0.8xxx.0. If not, get the updates from the Downloads section at
>> support.microsoft.com.
>>
>> It is possible to import the corruption into a new mdb. Decompiling first
>> might help. To decompile a copy of the database by enter something like
>> this at the command prompt while Access is not running. It is all one
>> line, and include the quotes:
>> "c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
>> "c:\MyPath\MyDatabase.mdb"
>> Then open the database and compact it:
>> Tools | Database Utilities | Copact
>>
>> Then create the new mdb, and uncheck the boxes under:
>> Tools | Options | General | Name AutoCorrect
>> Explanation of why:
>> http://members.iinet.net.au/~allenbrowne/bug-03.html
>> You can then import the objects into this new mdb.
>>
>> Now you want to check for any code that could be causing concurrency
>> issues. This can be a big task, but you can start with code in the
>> modules of the problem form, its 2 subforms, or any procedure called by
>> those 3.
>>
>> In particular:
>> 1. Explicitly save the record in the form before doing anything that may
>> need that done, such as moving record, closing the form, viewing or
>> altering the same data in a recordset (including the RecordsetClone of
>> the form), opening another form/report that accesses the same data,
>> applying/removing a filter, changing the sort order, reassigning the
>> RecordSource, executing an action query on the same table(s), etc.
>>
>> 2. If the RecordSource of the form or subforms includes any memo fields,
>> hyperlinks, or OLE objects (i.e. fields accessed via a pointer), be
>> super-cautious about offering 2 places where the user can modify these.
>> It is very easy to run into concurrency issues with these.
>>
>> 3. Make certain your code cleans up after itself, i.e. explicitly close
>> what you open (and only what you open). In the error recovery/exit
>> section of your module, set all objects to Nothing. Objects that are not
>> dereferenced can cause concurrency issues.
>>
>> Of course, if the database is used by more than 1 person at a time, make
>> sure it is split, and each user has their own copy of the front end.
>>
>> For other tips on avoiding corruption, see:
>> http://members.iinet.net.au/~allenbrowne/ser-25.html
>> Particularly, #5 can help you avoid this particular problem.[/color][/color]

re: Strange corruption problem in A2K

Wow, again, great answer. I'm glad I asked. :)

As it happens one of the key problems is that I am presenting data from
a continuous view subform in a more detailed popup, and my method of
doing so resulted in exactly the concurrency issue you mention below.

This is a so-called "dirty read", correct? If so, then the fact that I
was corrupting the db makes perfect sense, as dirty reads are known to
cause corruption problems.

Well, I certainly owe you a beer, proverbial or literal for the time
you've saved. Thanks a million.

EE

Allen Browne wrote:[color=blue]
> When you begin editing a record, Access copies it into the buffer, and then
> writes the buffer back when you save.
>
> If you then edit another instance of the same data in another form or in
> another instance of Access and save that, the original buffer copy you were
> working with previously is now out of date, so when you go to save it you
> receive the conflict dialog.
>
> "The same data" means the same record, or records in the same buffer page
> (2k text/4k unicode), and data referenced by the same pointers (for BLOB
> fields).
>
> It is always worth a little extra thought/code to avoid these issues and
> keep things up to date, even though Access is very good at sorting these out
> in general.
>
> My personal recommendation is to use "No Locks" (optimistic locking, the
> default), but not "Row-level locking" (performance loss, and possibily some
> issues.)
>[/color]