Post-SP3 issues when deleting records

[Justin]

Good morning,

After installing SP3 on machines throughout our organization, we've
discovered that Access will consistently crash when trying to delete certain
records. I've been able to duplicate it with different databases on
different workstations consistently.

1. Create a simple access database with two tables.
2. Create a foreign key in table2 and create the 1-to-many relationship (w/
cascade deletes).
3. Add a few records in each table.
4. Open up the table with the "1" in the 1-to-many relationship. You
should have a (+) sign next to each row you can click to see the
5. If you delete a record without expanding the row to show the related
rows first, it will delete w/o a problem. However, if you expand the row and
then collapse it, Access 2003 w/ SP3 will crash.

In case the steps above aren't clear, here's a screencast of the steps I'm
talking about:

http://www.screencast.com/users/Jus...ng/media/80481f97-6438-4dca-99cd-9f84fc003ec4

You'll notice in the screencast that it doesn't seem to matter if the child
table actually has any related records for the row you want to delete; Access
still crashes whether there are related rows or not.

In the screencast, I'm using v11.8204.8172, but we've seen the same issues
w/ 11.8166.8221. This same process worked fine on this machine when I had
v11.6566.6568 (SP2) installed, but Access started crashing immediately after
installing SP3 on my test box.

I've tried the various post-SP3 hotfix packages I found, but none seemed to
help. Haven't been able to find any other relevant hotfixes, either.

Any suggestions? Hotfix or something I'm overlooking?

Thanks,

Justin

 

[Tom Wickerath]

Hi Justin,

I wouldn't have believed it, but I decided to create a quickie database to
test your assertion. I can now confirm that I'm able to reproduce this issue,
using Access 2003, version 11.8204.8221 (SP3). I will bring this to the
attention of the Access Development Team at Microsoft.

In the meantime, my suggestion is to eliminate your use of the SubDatasheets
(that's the name of the "+" sign that you referenced in the one-side table).
You need to first disable Name Autocorrect, under Tools | Options (General
tab). Then open each table in design view. Click on View | Properties. Set
the Subdatasheet Name from the default [Auto] to [None]. I guess I've never
run into this issue in the past, as I make it a habit to disable both Name
Autocorrect and Subdatasheets in all Access applications. Here are the
reasons why. The first KB article includes code that allows you to quickly
sweep through all tables, to set the Subdatasheet to [None]. However, to get
this setting to "stick", you must disable Name Autocorrect first:

BUG: Slow performance on linked tables in Access 2002 and Office Access
2003
http://support.microsoft.com/?id=275085

Slow performance when user opens an object with Name AutoCorrect
enabled in Microsoft Access
http://support.microsoft.com/?id=290181


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Justin]

Great. Thanks, Tom.

Given that Access 2003 is outside the mainstream support window
(http://support.microsoft.com/lifecycle/?p1=2509), is it at all likely that a
hot fix will be released to address this problem?

Thanks,

Justin

Hi Justin,

I wouldn't have believed it, but I decided to create a quickie database to
test your assertion. I can now confirm that I'm able to reproduce this issue,
using Access 2003, version 11.8204.8221 (SP3). I will bring this to the
attention of the Access Development Team at Microsoft.

In the meantime, my suggestion is to eliminate your use of the SubDatasheets
(that's the name of the "+" sign that you referenced in the one-side table).
You need to first disable Name Autocorrect, under Tools | Options (General
tab). Then open each table in design view. Click on View | Properties. Set
the Subdatasheet Name from the default [Auto] to [None]. I guess I've never
run into this issue in the past, as I make it a habit to disable both Name
Autocorrect and Subdatasheets in all Access applications. Here are the
reasons why. The first KB article includes code that allows you to quickly
sweep through all tables, to set the Subdatasheet to [None]. However, to get
this setting to "stick", you must disable Name Autocorrect first:

BUG: Slow performance on linked tables in Access 2002 and Office Access
2003
http://support.microsoft.com/?id=275085

Slow performance when user opens an object with Name AutoCorrect
enabled in Microsoft Access
http://support.microsoft.com/?id=290181


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Good morning,

After installing SP3 on machines throughout our organization, we've
discovered that Access will consistently crash when trying to delete certain
records. I've been able to duplicate it with different databases on
different workstations consistently.

1. Create a simple access database with two tables.
2. Create a foreign key in table2 and create the 1-to-many relationship (w/
cascade deletes).
3. Add a few records in each table.
4. Open up the table with the "1" in the 1-to-many relationship. You
should have a (+) sign next to each row you can click to see the
5. If you delete a record without expanding the row to show the related
rows first, it will delete w/o a problem. However, if you expand the row and
then collapse it, Access 2003 w/ SP3 will crash.

In case the steps above aren't clear, here's a screencast of the steps I'm
talking about:

http://www.screencast.com/users/Jus...ng/media/80481f97-6438-4dca-99cd-9f84fc003ec4

You'll notice in the screencast that it doesn't seem to matter if the child
table actually has any related records for the row you want to delete; Access
still crashes whether there are related rows or not.

In the screencast, I'm using v11.8204.8172, but we've seen the same issues
w/ 11.8166.8221. This same process worked fine on this machine when I had
v11.6566.6568 (SP2) installed, but Access started crashing immediately after
installing SP3 on my test box.

I've tried the various post-SP3 hotfix packages I found, but none seemed to
help. Haven't been able to find any other relevant hotfixes, either.

Any suggestions? Hotfix or something I'm overlooking?

Thanks,

Justin

 

[Tom Wickerath]

Hi Justin,

I cannot say one way or the other whether or not Microsoft will address this
bug. If I were to place a bet that involved my hard-earned money, I'd be
betting on no fix forthcoming, since this is not a security issue, and there
is a workaround: namely, to set the Subdatasheet property to [None].

The good news is that I heard back from my contact at Microsoft (I had sent
him a test database). He replied indicating that this issue does not
reproduce in either Access 2007 or Access 2010.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[David W. Fenton]

I cannot say one way or the other whether or not Microsoft will
address this bug. If I were to place a bet that involved my
hard-earned money, I'd be betting on no fix forthcoming, since
this is not a security issue, and there is a workaround: namely,
to set the Subdatasheet property to [None].

But subdatasheets are incredibly useful.

For instance, a client recently asked for an overview of a form with
a subform. Rather than creating a new form, I just set it up so he
can switch between form view and datasheet view, and the form's
subform appears in the subdatasheet (it's a datasheet form, too). In
a couple of minutes, I provided a feature that has been a huge hit,
and that didn't take require any kind of significant investment in
development time/$$$.

Now, I recognize that sometimes useful features are too buggy to
work, but the bad reputation that subdatasheets have seems to me to
have been unearned since about the Access 2002 timeframe.

It's not that I use them often, it's just that when they *are*
useful, they are an extremely elegant and efficient solution.

 

[Tony Toews [MVP]]

4. Open up the table with the "1" in the 1-to-many relationship. You
should have a (+) sign next to each row you can click to see the
5. If you delete a record without expanding the row to show the related
rows first, it will delete w/o a problem. However, if you expand the row and
then collapse it, Access 2003 w/ SP3 will crash.

I've tried the various post-SP3 hotfix packages I found, but none seemed to
help. Haven't been able to find any other relevant hotfixes, either.

What about this hotfix? http://support.microsoft.com/kb/956722

"Assume that you try to delete a parent record from a table in
Datasheet view in an Access 2003 Service Pack 3 (SP3) database (.mdb).
Additionally, the Datasheet view uses a sub-datasheet that is expanded
to show the related child records. In this scenario, Access 2003 SP3
stops responding."

is somewhat close to your problem.

Tony

 

[Justin]

Sweet! Don't know how I missed that one, but it seems to have worked in my
test environment. Will roll it out to the user and make sure, but it
definitely looks promising!

Justin

 

[Tom Wickerath]

Thanks Tony.
I just applied for the hotfix, received it, and installed it. The hotfix
worked--I am no longer able to repro the problem.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________