Need Advice for Changing the Primary Key on an Access data table already bound to a VB.NET project
Hello Experts.
I have learned from past experience that it can be hazardous to make changes to a back-end data table when it is bound to an existing VB.NET project in Visual Studio.
I would like to believe that it is possible to pull this task off without crashing my project, and would like to seek any advise from the EE community on how to do this.
Specifically, I have a single MDB file which contains a dozen or so tables and queries/views, with all their relationships. Everything is created/designed using MS Access 2016.
I want to change the primary key on one of the tables within that MDB. That would also require removing some of the relationships, and establishing new ones as needed.
Right now, the primary key for the Roster table is sequentially assigned, but the first non-primary field is and always will be unique, so I would really like to change it.
So, I am looking for some step-by-step instructions from someone who has successfully done this within the context of a previously bound Visual Studio project.
As always, don't hesitate to ask if I've left any important details out.
Thanks,
Tony G.
I have learned from past experience that it can be hazardous to make changes to a back-end data table when it is bound to an existing VB.NET project in Visual Studio.
I would like to believe that it is possible to pull this task off without crashing my project, and would like to seek any advise from the EE community on how to do this.
Specifically, I have a single MDB file which contains a dozen or so tables and queries/views, with all their relationships. Everything is created/designed using MS Access 2016.
I want to change the primary key on one of the tables within that MDB. That would also require removing some of the relationships, and establishing new ones as needed.
Right now, the primary key for the Roster table is sequentially assigned, but the first non-primary field is and always will be unique, so I would really like to change it.
So, I am looking for some step-by-step instructions from someone who has successfully done this within the context of a previously bound Visual Studio project.
As always, don't hesitate to ask if I've left any important details out.
Thanks,
Tony G.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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.
Thanks for jumping in, Arana.
Visual Studio makes it pretty easy to connect to an external database. It was a while ago, but I'm pretty sure I just used a Wizard to do it.
I didn't use any code, only the drag-and-drop method to let Visual Studio do all the gruntwork. Of course, I kind of wish I had done that from the beginning -- I probably wouldn't be posting these kinds of questions if I had!
So, I have many Binding Sources, many Table Adapters, and can use the VS Dataset Designer to view (and change if I dare) my tables, views and relationships. On a good day, if I add a new field to a table, I can use the Wizard to get my project to recognize the missing column, and add it to my project. Changing the Primary Key for any table is almost certainly disastrous, so I'm REALLY hoping that someone has found a way to do this without the big crash!
Sorry I couldn't be more helpful, but that's all I know.
Visual Studio makes it pretty easy to connect to an external database. It was a while ago, but I'm pretty sure I just used a Wizard to do it.
I didn't use any code, only the drag-and-drop method to let Visual Studio do all the gruntwork. Of course, I kind of wish I had done that from the beginning -- I probably wouldn't be posting these kinds of questions if I had!
So, I have many Binding Sources, many Table Adapters, and can use the VS Dataset Designer to view (and change if I dare) my tables, views and relationships. On a good day, if I add a new field to a table, I can use the Wizard to get my project to recognize the missing column, and add it to my project. Changing the Primary Key for any table is almost certainly disastrous, so I'm REALLY hoping that someone has found a way to do this without the big crash!
Sorry I couldn't be more helpful, but that's all I know.
Hi Tony;
As you may know I have no experience changing the primary key in a Access database. I have found this Microsoft documentation on how to do so. Before attempting to do this please backup your database file as well as your VB .Net project because both will be affected if something goes terribly wrong.
Add or change a table’s primary key in Access
On that page follow the link for, "Change the primary key in Access".
Once that is done you will also need to update your code in the project specifically with the Table Adapter's that have been modified in the database.
Good Luck.
As you may know I have no experience changing the primary key in a Access database. I have found this Microsoft documentation on how to do so. Before attempting to do this please backup your database file as well as your VB .Net project because both will be affected if something goes terribly wrong.
Add or change a table’s primary key in Access
On that page follow the link for, "Change the primary key in Access".
Once that is done you will also need to update your code in the project specifically with the Table Adapter's that have been modified in the database.
Good Luck.
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
Thanks Fernando for the encouragement. I gathered up my bravado and changed the Primary Key in table MyPlayers using the MS Access Designer UI. I removed the old sequential key from both the table and the query/view. No trouble there.
Next, I went into the SNAP project and used the Wizard to add the new Primary Key and remove the old sequential key from the table and view. Applying these changes produced the multitude of errors along with the creation of SNAPDataSet1.Designer.vb. Rather than just delete it, I used WinDiff to compare the two, and clearly saw that the new one contained my changes, and the original one did not. So, I decided to delete the original one, and all the errors went away.
The only thing remaining is to rename it (remove the '1') but Visual Studio doesn't appear to offer this as an option from the Solution Explorer, so I'm content to leave it as is. Perhaps these numbers will remind me just how many times I've changed the back-end database.
Kind Regards,
Tony Gardner
Next, I went into the SNAP project and used the Wizard to add the new Primary Key and remove the old sequential key from the table and view. Applying these changes produced the multitude of errors along with the creation of SNAPDataSet1.Designer.vb. Rather than just delete it, I used WinDiff to compare the two, and clearly saw that the new one contained my changes, and the original one did not. So, I decided to delete the original one, and all the errors went away.
The only thing remaining is to rename it (remove the '1') but Visual Studio doesn't appear to offer this as an option from the Solution Explorer, so I'm content to leave it as is. Perhaps these numbers will remind me just how many times I've changed the back-end database.
Kind Regards,
Tony Gardner
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.