473,320 Members | 1,857 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
Nov 13 '05 #1
4 1879
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

25
by: Neil Ginsberg | last post by:
I have a strange situation with my Access 2000 database. I have code in the database which has worked fine for years, and now all of a sudden doesn't work fine on one or two of my client's...
10
by: Arno R | last post by:
Hi all Yesterday I found a strange corruption-issue that I can't solve yet or actually point my finger at. I converted an A97 app to A2k. I have done this often enough so I didn't expect trouble...
1
by: intl04 | last post by:
I am getting strange print-related error messages when trying to create (not print!) reports. For example, when I click 'new' to create a report then choose 'design view', I get an error message...
8
by: Ondine | last post by:
Hi I have a client running an Access 2000 database on a small network with 3 pc's. Two of the laptop pcs have a data replica, which they use when not connected to the network, the 'server'...
2
by: Joseph Macari | last post by:
I recently installed Office2003 on my computer. I had imported (not linked) a couple of tables from an Access 2000mdb into an Access 2003mdb. I had composed various queries and forms with these...
2
by: AP | last post by:
I have been having some issues with Access lately that I have never encountered. I am running Windows XP and MS Access 2K. It seems that occasionally if I delete an object, it appears to delete,...
11
by: Mike C# | last post by:
Hi all, I keep getting a strange error and can't pin it down. The message is: This application has requested the Runtime to terminate it in an unusual way. Please contact the application's...
25
by: Digital Puer | last post by:
I'm getting a very weird bit corruption in a double. I am on an Intel Red Hat Linux box. uname -a returns: Linux foo.com 2.6.9-34.0.2.ELsmp #1 SMP Fri Jun 30 10:33:58 EDT 2006 i686 i686 i386...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.