asked on 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.
Visual Basic.NET.NET ProgrammingMicrosoft Visual Studio
Last Comment
Fernando Soto
how is this db bound to your project? using bindingsource? , using dataset designer?,
ASKER
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
Hi Tony;
Glad it worked out for you. Talk to you soon.
Glad it worked out for you. Talk to you soon.