Restore primary key programmatically

I have a VB2008 app that we recently converted from VB6. It uses an Access 2000 DB. After completely removing all ADO and replacing with ADO.NET, we seem to be having a frequent problem where some tables in the database are losing their primary key. This may be happening after we compact the database.

Currently we download the database from the customer, open it in Access and restore the primary key.

I have two questions:

1. Is there any way to determine what may be causing this to try to stop it?

2. Is there a way I can programmatically restore a primary key in a table so we don't need to download the demo?

Who is Participating?
Brook BraswellConnect With a Mentor Application Development ManagerCommented:
This does not add a new column.  In fact if you list a columns that does not exist you will receive the error
Column 'bogus' not found

But as I mentioned earlier - you will need to trap for an error indicating that the Key already exists...

Try this...

Private Sub RebuildTableIndex(ByVal sTbl As String, ByVal sIndex As String)
            Dim iErr As Integer
10          iErr = 0
20          On Error GoTo PROC_ERR
            ' in this proc we will temporarily assume a table and index value
30          If Trim(sTbl) = "" And Trim(sIndex) = "" Then
40             sTbl = "tblCLMS"
50             sIndex = "[id]"
60          End If
            ' open the mock vendor item screen...
70          SQL = "ALTER TABLE [" & sTbl & "] ADD PRIMARY KEY ( " & sIndex & " );"
80          myConn.Execute SQL

90          Exit Sub
100         If InStr(1, UCase(ERR.Description), "TABLE ALREADY HAS A PRIMARY KEY", vbTextCompare) > 0 Then
               ' YOU ARE OK...KEEP GOING
               ' but if you wish to rebuild the index anyway
110            If DoAnyWay Then
120               SQL = "ALTER TABLE [" & sTbl & "] DROP PRIMARY KEY"
130               myConn.Execute SQL
140               Resume
150            End If
160            Exit Sub
170         End If
180         If InStr(1, UCase(ERR.Description), "NOT FOUND", vbTextCompare) > 0 Then
190            If AddColumn Then
                  ' CREATE THE COLUMN ???
200               SQL = "ALTER TABLE [" & sTbl & "] ADD " & sIndex & " bigint NOT NULL DEFAULT autoincrement"
210               myConn.Execute SQL
220               Resume
230            End If
240            Exit Sub
250         End If
260         If iErr > 3 Then
               ' YOUR ERROR LOGGING HERE
270            Resume PROC_EXIT
280         Else
290            iErr = iErr + 1
300            Resume
310         End If

End Sub

Open in new window

When you have an "Autonumber" field in Access, and you compact the database, then the next key for that field is reset to the maximum value in the table (+1).

This has nothing to do with using ADO.NET instead of ADO.

If yo uwant to restore the key programmatically, how are you going to find out what the previous key was?  If you have a way to determine the next key that should be used, then why are you using autonumber fields?
Can you calrify what you mean by "losing their primary key".  Is the column gone, or what?  Is it an Autonumber field or programatically managed primary key?

Compacting the database doesn't, as a rule, remove primary keys.  Compacting *does* reset the next key as joriszwaenep explained.  And I agree ADO/ADO.NET is likely not to be the issue.  The behavior you explained is abnormal.  I've seen abnormal behavior, but not this.  I'd suggest the first thing you should do is make frequent backup copies until you resolve this because I think there are two possiblities:
1. There may be corruption
2. Your program may be causing the problem
In either case, data loss is a possiblity.

Suggested actions:
1. Make a copy and work with the copy, not your one and only master, for the following steps.
2. Consider converting your Access 2000 mdb to a newer Access MDB.  This shouldn't affect your front end app at all other than your conection code.  (Test of course.)  Alternatively, you should be able to switch to SQL Server Express if you don't need more than a small number of users connecting simultaneously.  Note: SQL 2005 Express automatically allowed remote connections when installed, but SQL 2008 Express requires a configuration change to enable it after install.  See this link for an example of how to change this:
3. To check for and try to resolve corruption, create a new blank Access Database and import the objects from the existing db to the new one.  I've found this helps resolve many abnormal issues with Access DB's.  If you get an error during the import process where it stops on one particular object (I assume you only have tables in the Access db), that object is likely corrupted.  The process usually continues, but write each object name down that it stops on, and ideally, recreate it from a known good source.  If you have a problem with certain tables that won't transfer this way, maybe you can export the table to Excel or CSV to review and clean up the data.  Then import back into your new db.
4. As for the application possibly causing the problem, you may have to put some kind of logging in place to check for conditions that lead up to or cause the problem.
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Brook BraswellApplication Development ManagerCommented:
in vb6 to rebuild a primary key


This will raise an error if the key is already there...
you only need to capture the error and decide to ignore it or drop the key and rebuild it.

AndyAinscowFreelance programmer / ConsultantCommented:
If it is an autonumber field 'reusing' numbers in use then this is a known problem with some versions of Access databases.  Nothing to do with ado or  :-(

I have experienced things like the following:
ID's 1, 2, 4, 5
After compaction the next ID autogenerated is 3 which is OK then 4 which results in a duplicate key - error.
LeeLiamAuthor Commented:
Let me explain a little further. We have hundreds of customers using our app. Occasionally, when they cannot update the database from a change on a grid, for example, we download their DB. When I open it with Access, the table is there with all data. The only problem is the primary key indicator next to the ID (Autonumbered) field is missing. The column with all IDs is present and all IDs are fine.

All I do is open the table in design mode, right click the ID field and select Primary Key. The little key indicator reappears and we upload the DB back to the customer. Then everything works as it should. All updating works fine.

We used to see the about once a year with the old VB6 program. Now with the .NET version, we're seeing it about once a week. I fear it may have to do with the connected versus disconnected nature or ADO versus ADO.NET coupled with my inexperience. I'm certain I'm closing every connection (in addition to using the USING statement for virtually all connections. We did turn connection pooling on (which makes Access run MUCH faster). But I'd love to know what to look for in the program to try to make this stop.

Also, I'd like to put some code in the program to restore restore the primary key back to the column that had it. It looks like the code Brook1966 gave might do this. I just want to make sure it does not add a new column as the primary key. In other words, can I use:


Will this just put the primary key indicator back on the existing ID column, or does it add a new column, or will it give me an error if the column is already there even if it is not set as the primary key?

Yes, that SQL stmt will only add a primary key.  Adding a column would be "ADD COLUMN..."  So it is safe to do that once you have the error condition, but you're right to look for the cause of the error instead of applying a bandaid.  Are there any stmts in the code that alter tables at all, or is it just accessing the data for add/delete/updates?
LeeLiamAuthor Commented:

I'm not doing anything to alter tables, just normal CRUD stuff.


That looks great. I'll be giving it a try today.

LeeLiamAuthor Commented:
Works great. Just what I needed.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.