How to save 2 related Access database tables in a dataset
I am using VB.Net and Visual studio 2015. I have an application with a Access database table (.accdb) that is nearing the 255 field limit. Therefore, I have split the table into two tables and created a nested Parent/Child relationship between the two tables in an attempt to overcome this limitation.
The problem is that I cannot update the two tables when the Save button is pressed. The TableAdapterManager will update the Parent table, but not the Child table.
Has anyone had this problem and found a solution? I have seen many other asking the same question, but I have not seen any solutions. Below is the code for the Save button.
The problem is that I cannot update the two tables when the Save button is pressed. The TableAdapterManager will update the Parent table, but not the Child table.
Has anyone had this problem and found a solution? I have seen many other asking the same question, but I have not seen any solutions. Below is the code for the Save button.
'Save Button
Private Sub ParentTableBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles ParentTableBindingNavigatorSaveItem.Click
Me.Validate()
Me.ParentTableBindingSource.EndEdit()
Me.ChildTableBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Database1DataSet)
End SubASKER
Coachman, thank you for your reply. You are correct, it is a 1 to 1 relationship and both tables will have changes. If the user makes a change to the Parent table, there will be a change needed in the Child table.
I am a novice at VB.net and haven't had to deal with updating related tables before, so I wasn't sure if a nested relationship was correct or not. I will make the relationship not nested and see what that does.
Thanks for the suggestion.
I am a novice at VB.net and haven't had to deal with updating related tables before, so I wasn't sure if a nested relationship was correct or not. I will make the relationship not nested and see what that does.
Thanks for the suggestion.
Hi Bob
still not sure why you need to make a change in both if you change data in one.
IMO, you should update each table based on its primary key (which coincidentally is the same in both tables)
and only update the affected table (unless you change data in both, and have to update 2 tables)
you could code so that a single update button would update both, regardless of which needs to be updated.
The problem isn't Access-related, and my knowledge of VB.net extends to ADO recordsets, so you may get more traction in a .net forum?
still not sure why you need to make a change in both if you change data in one.
IMO, you should update each table based on its primary key (which coincidentally is the same in both tables)
and only update the affected table (unless you change data in both, and have to update 2 tables)
you could code so that a single update button would update both, regardless of which needs to be updated.
The problem isn't Access-related, and my knowledge of VB.net extends to ADO recordsets, so you may get more traction in a .net forum?
If you have a table with more than 255 columns, you almost certainly have created a spreadsheet rather than a properly normalized relational table. The best solution is to normalize the tables.
Splitting into two tables didn't really solve your problem because you will need to use two separate forms. The 255 limit is for a recordset which is either a table or a query so there is no way to join the two tables and exceed the 255 columns.
Splitting into two tables didn't really solve your problem because you will need to use two separate forms. The 255 limit is for a recordset which is either a table or a query so there is no way to join the two tables and exceed the 255 columns.
I'd agree that a table with 255 fields is almost certainly not properly normalized.
That said, how you update related tables depends on how you're creating and working with those tables. If you're using data adapters, then you can "relate" your tables and allow the data adapter to handle the load. If you're working with the data manually, then you'd have to make the updates in the right order (i.e. Parent first, then Child).
That said, how you update related tables depends on how you're creating and working with those tables. If you're using data adapters, then you can "relate" your tables and allow the data adapter to handle the load. If you're working with the data manually, then you'd have to make the updates in the right order (i.e. Parent first, then Child).
ASKER
My table only has about 120 columns, but as I understand it, for the limit calculation each column counts as two because one field is for the column and one is for the value, or something like that. All of the values in the columns are necessary to do the various calculations and to store the values the user enters. I do have other separate tables that store related data.
120 is still a lot. I've been doing this for 50 years and except in a data warehouse where the tables were explicitly flattened, I've never needed more than about 50 columns and that was for the whole gamut of business applications from mortgage banking to shop floor control to airplane engine inspection specs to catalogs.
In any event, columns don't count twice. I'm not sure what you are referring to. The limit is 255 columns in a recordset.
In any event, columns don't count twice. I'm not sure what you are referring to. The limit is 255 columns in a recordset.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, point taken. I will look into Normalizing my table. I am not sure how to Normalize my table into 20 to 50 columns, but I will look into it.
ASKER
I will normalize my tables.
Once you identify the repeating group or groups, it will be easy.
if the db relationships are defined as 1:1 then you would need to update whichever table data is affected; independently of the other