By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,491 Members | 3,230 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,491 IT Pros & Developers. It's quick & easy.

Strange corruption problem in A2K

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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" <wh*******@bonbon.net> wrote in message
news:jq********************@speakeasy.net...
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

Nov 13 '05 #2

P: n/a
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:
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.

Nov 13 '05 #3

P: n/a
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" <wh*******@bonbon.net> wrote in message
news:2N********************@speakeasy.net...
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:
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.

Nov 13 '05 #4

P: n/a
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:
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.)

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.