Solved

Restore primary key programmatically

Posted on 2010-09-23
9
739 Views
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?

Thanks.
0
Comment
Question by:LeeLiam
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 10

Expert Comment

by:joriszwaenepoel
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?
0
 
LVL 11

Expert Comment

by:kbirecki
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.
0
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 33753572
in vb6 to rebuild a primary key

SQL = "ALTER TABLE [TABLE NAME] ADD PRIMARY KEY [KEYNAME] ASC"
CONN.EXECUTE SQL

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.

SQL = "ALTER TABLE [TABLE NAME] DROP PRIMARY KEY"
 CONN.EXECUTE SQL
 
 
0
 
LVL 44

Expert Comment

by:AndyAinscow
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.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:LeeLiam
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:

SQL = "ALTER TABLE [tblCLMS] ADD PRIMARY KEY [ID] ASC"

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?

Thanks.
0
 
LVL 11

Expert Comment

by:kbirecki
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?
0
 
LVL 14

Accepted Solution

by:
Brook Braswell earned 500 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

PROC_EXIT:
90          Exit Sub
PROC_ERR:
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
               ' YOUR TABLE DOES NOT HAVE THIS COLUMN...
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

0
 

Author Comment

by:LeeLiam
ID: 33755431
kbirecki,

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

Brook1966,

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

Thanks.
0
 

Author Closing Comment

by:LeeLiam
ID: 33770929
Works great. Just what I needed.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now