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.

   
 '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 Sub

Open in new window

Bob ValentineCPA / Owner - Cellutionware SoftwareAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

COACHMAN99Commented:
if the reason for two tables is to extend a table, why 'nested 1:M'? surely it should be 1:1 on the PK?
if the db relationships are defined as 1:1 then you would need to update whichever table data is affected; independently of the other
Bob ValentineCPA / Owner - Cellutionware SoftwareAuthor Commented:
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.
COACHMAN99Commented:
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?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

PatHartmanCommented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
Bob ValentineCPA / Owner - Cellutionware SoftwareAuthor Commented:
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.
PatHartmanCommented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That's not really accurate. A column is a column, and you can have up to 255 columns (although that is, generally speaking, a bad idea). For that matter, a table with 120 columns is also almost always a sign of a poorly normalized database. In my 20+ years of dealing with databases I can recall very few tables that have more than 50 columns or so, and all of those were scientific in nature, and could have potentially been broken down into subtables.

The reason we harp on this is simple - a properly designed database works MUCH better with defaults in the programming language than does an improperly designed system. A normalized structure lends itself to proper data handling, simpler updates and such, etc etc.

In regard to your TableDataAdapter - have you set the Hierarchical Update settings correctly? I don't use table adapters, so I can't tell you exactly how they should be set, but here's a MSFT article that might give some insight:

https://msdn.microsoft.com/en-us/library/bb629327.aspx?f=255&MSPPError=-2147217396

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob ValentineCPA / Owner - Cellutionware SoftwareAuthor Commented:
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.
Bob ValentineCPA / Owner - Cellutionware SoftwareAuthor Commented:
I will normalize my tables.
PatHartmanCommented:
Once you identify the repeating group or groups, it will be easy.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.