[RESOLVED] Updating Parent/Child FK Constraints using OleDb and Access
I was tasked with upgrading an Access database that didn't have any table relationships (I don't know why you'd go to the trouble of making a completely flat database?) to one that utilized relations and constraints. Additionally, the program needed a full GUI wrapper to handle some fairly complex calculations, so it was originally written in .NET. I kept with the use of VB.NET, but I wanted to avoid using the Access database. Unfortunately, there were certain criteria that required the use of Access (don't ask).
So I got to coding, and everything was working fine until I reached the part in the development where I needed to "save" data back to the database. Every variation of code I could find to update the database resulted in Foreign Key exceptions. I was unable to write records to any tables that had a foreign key reference to any other tables. Parent tables updated fine, but any table that was a child to any other table failed the update.
I tried setting the relationships in the database AND the DataSet to Cascade on Insert and still received the error. The AutoIncrement seed for all of the primary keys in the DataSet were set to -1, but I still received the error.
Then, during one of many searches, I stumbled across a post about my exact problem (here). jmcilhinney hit the nail on the head by stating that "Jet and ACE OLE DB providers don't support multiple SQL statements in a single command". Then further stated that "Because you can't refresh the IDs automatically, you have no choice but to save the parent data, retrieve the parent data again and then update the foreign keys yourself somehow. It's a pain, and it's why you shouldn't use Access when you need to save related data this way."
Unfortunately, I didn't know how to go about updating the foreign keys myself. After more searching, I found this article. The sample code in the article didn't match my exact needs (honestly, what example code ever matches someone else's program's intent), and since I spent so much time trying to figure this out, I decided to post what finally worked for me.
In my project, I have a typed DataSet representing the Access database. I create all of the DataTables and BindingSources through code right after the InitializeComponent() method call when a new instance of my form is created. All variables representing DataTables and BindingSources are declared WithEvents so that I can hook their events during design time. I did NOT create a TableAdapterManager, as I was unable to get the UpdateAll(dataset) method to work.
As a result, I needed to break the Delete, Insert and Update commands out separately for each of my DataTables following the delete up (from Child table to Parent table) and Insert/Update down (from parent table to child table) heuristic. However, this alone still resulted in the Foreign Key Exceptions.
Through other articles, I learned that the DataSet Design Wizard does not include the types of relations from the source database when it creates the associated relationships in the DataSet. So, I set all of the relationships in the DataSet designer to create both Foreign Key and Relationship constraints, Update Rule: Cascade, Delete Rule: SetNull, Accept/Reject Rule: None, and left the Nested Relation unchecked.
Following the MSDN article, I returned to my form's code, and added the following event method:
Code:
Private Sub DataAdapter_RowUpdated(ByVal sender As Object, ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs)
' only work on inserted rows
If e.StatementType = StatementType.Insert Then
' use existing connection, otherwise select will fail
Using cmd As New OleDbCommand("SELECT @@IDENTITY", e.Command.Connection)
' set the record's primary key, the cascade should handle the rest
e.Row("ID") = CInt(cmd.ExecuteScalar)
End Using
' prevent hidden call to accept changes
e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub
Lastly, for each of the Tables that had Child relationships, I added an event handler to trap the TableAdapter's DataAdapter OnRowUpdated event:
Code:
' in form's New() method
SetTableAdapterRowUpdatedHandle(Me.taParent1.Adapter)
SetTableAdapterRowUpdatedHandle(Me.taParent2.Adapter)
' method to add handler to passed DataAdapter
' I have lots of TableAdapters in my project and didn't want to type
' the AddHandler code a bunch, so I made a method
Private Sub SetTableAdapterRowUpdatedHandle(ByRef ta As OleDbDataAdapter)
AddHandler ta.RowUpdated, AddressOf DataAdapter_RowUpdated
End Sub
Now, for a simplified example from my project. Let's say I have one Child table that references two Parent Tables, as follows
Code:
Table Name: Child
--------------------------------
ID Integer, Primary Key
Parent1_ID Integer, Foreign Key
Parent2_ID Integer, Foreign Key
.
.
.
Table Name: Parent1
------------------------
ID Integer, PK
.
.
.
Table Name: Parent2
------------------------
ID Integer, PK
.
.
.
The code to update the database would be as follows:
Code:
Try
' Remove all deleted Child rows
Me.taChild.Update(dsData.Child.Select("", "", DataViewRowState.Deleted))
' Update Parent1 Table
Me.taParent1.Update(dsData.Parent1)
' Update Parent2 Table
Me.taParent2.Update(dsData.Parent2)
' Insert new Child rows
Me.taChild.Update(dsData.Child.Select("", "", DataViewRowState.Added))
' Update all modified child rows
Me.taChild.Update(dsData.Child.Select("", "", DataViewRowState.ModifiedCurrent))
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
Because we hooked the DataAdapter's RowUpdated event, and our relationships are set to cascade, all of the foreign keys are updated with the new AutoIncrement number assigned to the Parent record by the database, thus satisfying our foreign key constraint.
Hooray! It finally worked for me! Hopefully, there aren't any bugs in my code samples, and anyone with this problem will find this post useful.
If you've read this far, congrats and thanks.
