"Operation not supported in transactions" error when deleting on f

  • Thread starter Thread starter Eric Sambell
  • Start date Start date
E

Eric Sambell

Hello,

I have been having a problem with a form of mine with several layers of
sub-forms in Access 2007 SP1. When I delete a record from the lowest level of
sub-form it disappears from the list as normal, but then when I go to move to
another record, or do pretty much anything else Access throws an "operation
not supported in transactions" error. As well the record doesn't actually get
deleted. All of the sub-forms are in datasheet view, and so they appear as if
they are several layers of sub-datasheets. Also the base tables are linked
from a seperate backend .mdb file. I get a very similar result when I do a
"paste append."

I assume that Access is opening a transaction to delete (or "paste append")
but not completing it. Does anyone know of a fix or a way to force Access to
complete the transaction?

Thanks,

Eric Sambell
 
Are you absolutely certain you have Service Pack 1 applied?
This is one of the issues addressed in SP1:
http://support.microsoft.com/kb/942378

I was a bit confused here. AFAIK, you cannot place a subform in a form that
is in datasheet view, so I don't understand what you mean by subforms in
datasheet view that contain sub-subforms. (You seem to understand the
distinction between a subform and a subdatasheet.)
 
Allen,

Thanks for your response. When I originally encoutered this problem I was
not running SP1, but after reading that KB article I installed SP1, but the
problem persisted. As for the multiple levels of sub-forms in datasheet view,
it appears to be a new feature in Access 2007. I don't think it was
publicized I just happened to stumble across it by mistake some time ago. If
you have a sub-form, with a sub-form in it set both to default to datasheet
view, and it turns out you end up with what looks like a table with a
sub-datasheet, but that you can have code behind. I wouldn't have believed it
myself if I'd read it in this forum, since it seems couter-intuitive, but in
fact it works. Very handy, but I guess maybe they haven't fully vetted it,
since delete and "paste append" don't seem to function properly.

Thanks,


Eric Sambell
 
Okay, Eric, I can reproduce that. It certainly looks like a new bug in
Access 2007.

Deleting from a subform in datasheet view, on a subform in datasheet view,
gives the error you named, when you navigate away after the deletion.

Will try to pass this on to Microsoft, though I suspect the guys are rather
busy this week.
 
Allen,

Great thanks for your help! I wouldn't know how to let the MS guys know when
I come accross this stuff, so if you could pass it on that would be great.
It's hard to believe no one has had this problem yet.

Eric
 
Is there a workaround that i could use to avoid this bug ?


this bug also freezes up the whole SQL back-end server. (2005)
prevents all other users from using the DB until access app is killed.
 
While I'm not aware of a workaround, I have passed your comments on to the
Access team (without investigating them), to help them determine the
business impact of the bug.
 
Allen Browne said:
... I have passed your comments on
to the Access team (without investigating them), ...
Click to expand...

What I intended was:
... I have passed on your comments (without investigating them)
to the Access team ...

And no: I did not investigate the Access team either. :-)

(Gotta luv Englesh, eh?)
 
Thank you Allen,
If the access team wants to see this bug in real life, i'll be happy
to show them.
just happens that i work 20 miles from redmond :)

what is really strange to me is that this really looks like a huge
bug, how come there is no internet buzz about it? took me weeks of
googeling to find your post.
no one uses datasheet subforms ?

wojtek
 
(e-mail address removed) wrote in
m:
no one uses datasheet subforms ?
Click to expand...

Mostly, no, and the reason is because they tend to be a performance
drain.

It's too bad, because they have some surprisingly useful features
(e.g., embedding a non-datasheet subform in a datasheet).
 
Re your final line:
no one uses datasheet subforms ?
Click to expand...

Well, I can't speak for everyone else, but I don't use tend to use them
because:

a) Most of my apps have to be compatible with previous versions, which did
not have spit forms, and did not support subforms containing subforms in
Datasheet view.

b) I almost always use Continuous forms rather that Datasheet view forms, as
it saves support calls from users how mess up the columns (hide, freeze, one
column being full width, and so on.)
 
a) Most of my apps have to be compatible with previous versions,
which did not have spit forms, and did not support subforms
containing subforms in Datasheet view.
Click to expand...

Er, what? Access 2000 and on have all supported subforms embedded in
a datasheet. It's one of those things I stumbled onto accidentally
-- viewing a normal form with subforms in datasheet view I noticed
that hitting the plus sign displayed whatever the active subform was
when I entered datasheet view -- but it's been discussed in the
newsgroups as one workaround for the inability to embed continuos
forms more than one level deep.
 
If the access team wants to see this bug in real life, i'll be happy
to show them.
... looks like a huge bug
Click to expand...

A.D.Tejpal (one of the other MS Access MVPs) took a look at this bug, and
suggested this workaround:

Work-around:
==========
Placement of code as given below, in inner-most form's module, is found to
eliminate the problem. (Important: Form's KeyPreview property should be set
to Yes).

Best wishes,
A.D. Tejpal
------------

' Code in inner-most subform's module
'==================================
' Declarations section
Option Compare Database
Option Explicit

' Total number of columns in datasheet
Private Const TotColumns As Long = 5
'---------------------------------------------------

Private Sub Form_KeyDown(KeyCode As Integer, _
Shift As Integer)
On Error Resume Next
Dim StartPos As Long, Cnt As Long

If KeyCode = 46 Then
' Ignore if full record is not in selected state
' This will retain normal action within controls.
If Me.SelWidth <> TotColumns Then
Exit Sub
End If

KeyCode = 0

If MsgBox("Shall Delete Selected Records ?", _
vbYesNo) <> vbYes Then
Exit Sub
End If

StartPos = Me.CurrentRecord - 1

' If multiple records are selected, delete all
' such records
For Cnt = Me.SelHeight - 1 To 0 Step -1
Me.Recordset.AbsolutePosition = _
StartPos + Cnt ' (A)
Me.Recordset.Delete ' (B)
Next
Me.Requery

If StartPos > 0 Then
Me.Recordset.MoveLast ' (C)
Me.Recordset.AbsolutePosition = _
StartPos - 1 ' (D)
End If
End If

' Note: The recordset in (C) & (D) represents
' post - deletion stage, while that in (A) & (B)
' is pre-deletion stage. Therefore, not wrapped
' in With / End With block.
On Error GoTo 0
End Sub
'---------------------------------------------------

Private Sub Form_Load()
Me.KeyPreview = True
End Sub
'==================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Operation not supported 2
Adding a Username to a Transaction from a Form 1
How to setup database to make transactions (transfers) between two bank accounts 0
selected record in listbox 1
Overriding constraint restriction message, when deleting record(s) from form 6
Highlight rows in a subform datasheet view 0
Access Updating inventory levels 0
How do I link two Sub forms? 1

Back
Top