[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Restore primary key programmatically

Posted on 2010-09-23
Medium Priority
Last Modified: 2012-05-10
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?

Question by:LeeLiam
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
LVL 10

Expert Comment

ID: 33747025
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?
LVL 11

Expert Comment

ID: 33752404
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: http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/
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.
LVL 14

Expert Comment

by:Brook Braswell
ID: 33753572
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.

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

LVL 45

Expert Comment

ID: 33754465
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 ado.net.  :-(

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.

Author Comment

ID: 33754602
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?

LVL 11

Expert Comment

ID: 33754774
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?
LVL 14

Accepted Solution

Brook Braswell earned 2000 total points
ID: 33754871
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


Author Comment

ID: 33755431

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


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


Author Closing Comment

ID: 33770929
Works great. Just what I needed.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question