Need Advice for Changing the Primary Key on an Access data table already bound to a VB.NET project
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.
Sr. Programmer/Analyst
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.
Sr. Programmer/Analyst
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
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions