We help IT Professionals succeed at work.

Need Advice for Changing the Primary Key on an Access data table already bound to a VB.NET project

Last Modified: 2018-10-04
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.

Tony G.
Watch Question


how is this db bound to your project? using bindingsource? , using  dataset designer?,
Tony GardnerSr. Programmer/Analyst


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.
Distinguished Expert 2017
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
Tony GardnerSr. Programmer/Analyst


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
Fernando SotoRetired
Distinguished Expert 2017

Hi Tony;

Glad it worked out for you. Talk to you soon.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why 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.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
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