SQL Backend, Delete record error 3246.

[gerrythefish]

Hi all,

What am I not understanding? Access 2013 with SQL backend.

I have a subform datasheet. If I try to right click to delete a record I get an error that I cant delete because its based on a view that has 2 tables in it.

So I tried to cancel the delete event, run a simple delete query on 1 table, The record does successfully delete but then Im left with a record with "#deleted" in each field.

If I try to requery the datasheet I get error 3246 Operation not supprted in transactions.

Heres my form delete event

On Error GoTo ErrorHandler

DoCmd.RunSQL "DELETE FROM PIT_PDDb_Module WHERE PIT_Module_ID = " & Me.PIT_Module_ID
Me.Requery
Cancel = True

 

[Ranman256]

You may not have permission for a cascade delete.
Does this table have any related tables joined?
Is this the bottom child table?
You may not be able to use a view, use the table.

 

[gerrythefish]

Yes, but, as I said, the record gets deleted just fine, I get the error if I try to requery the datasheet form with a me.requery in the on delete event.

Here is what I have
I have a parent table (Teams) shown on the main form
Team_ID Team_Name The_Timestamp
1 Orange
2 Blue

I have a child table (Players)
Player_ID Player_Name The_Timestamp
1 Billy
2 Bob
3 Sue

I have a table that links players to teams, any 1 player can be on multiple teams.
Team_Players
Team_Player_ID Team_ID Player_ID The_Timestamp
1 1 1
2 1 2
3 1 3
4 2 2

I have a main form with Teams and a subform that I want to show the players in alphabetical order in Datasheet view, so the subform is linked to the view.

So I have a view:
SELECT dbo.Team_Players.Team_Player_ID, dbo.Team_Players.Team_ID, dbo.Team_Players.Player_ID, dbo.Team_Players.The_Timestamp,
dbo.Players.Player_Name
FROM dbo.Team_Players INNER JOIN
dbo.Players ON dbo.Team_Players.Player_ID = dbo.Players.Player_ID

If I right click to delete teh record from the view I get a SQL error that I cant do this because the view has multiple tables.

So I put this in the Form_Delete event

Private Sub Form_Delete(Cancel As Integer)
DoCmd.RunSQL "DELETE From Team_PLayers WHERE Team_Player_ID = " & Me.Team_Player_ID

Cancel = True

End Sub

This succesfully deletes the record. But I have to close and open the form to refresh it. If I add me.requery to this event = I get error #3246 operation not supported in transactions.

How do i requery the datasheet to remove the deleted record?

You may not have permission for a cascade delete.
Does this table have any related tables joined?
Is this the bottom child table?
You may not be able to use a view, use the table.

 

[gerrythefish]

Dumb newbie mistake - cant use views as the source of the form. Gah! seems like this would make it so much easier.

I have some complex queries in access that are really slow and completely bombed when I moved the backend to SQL. This is why I really want to use linked views as the source of a form, - it looks like it should work but its complicated. In addition to the problem above. When i relinked all my tables and views, the tables maintained their primary keys in Access, but the views did not - which means I have to add an index/primary key to each view after I refresh the views - didnt know that. I probably will selectively use views but only where absaloutely necessary. Gah!

Would really like to find a single source that says when migrating backend to SQL here is the beginners guide to when to use linked tables, VBA ADO, VBA DAO, queries in access, views in SQL, stored procedures etc. The holy grail?

You may not have permission for a cascade delete.
Does this table have any related tables joined?
Is this the bottom child table?
You may not be able to use a view, use the table.