turn primary key off on a access table

  • Thread starter Thread starter Willy Rubens
  • Start date Start date
the field is not used, and i want to remove it and create it again, so it
will be renumbered. its a autonumber field.
So that it's not shows, that there wore records removed.
thanks for reply.
 
Autonumbers exist for one purpose: to provide a (practically guaranteed)
unique value that can be used as a primary key. They serve that purpose with
or without gaps in the numbering. You should never ascribe any meaning to
the value of an Autonumber field: in fact, if you were to replicate your
database, the Autonumber field would be changed from sequential to random
numbers.

If you're insistent on doing this, why do you feel it needs to be done via a
SQL statement?
 
It has to be done in vb.net every time as a procedure has removed records
from the table, i want to recreate that field. I'ts the wish of a client.
Other people must not see that there wore records deleted.

Thanks
 
I really think you should reconsider the design. Dropping and re-creating
the index and the field every time a record is deleted seems like a very bad
idea to me. With that caveat, though, here's how it might be done. Note that
this assumes that the table does not participate in any relationships. If it
does, you'll have to drop and re-create those, too.

Module Module1

Sub Main()
Dim strConnection As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=Admin;" & _
"Data Source=c:\usenet\test.mdb;" & _
"Persist Security Info=False"
Dim cnn As System.Data.OleDb.OleDbConnection = _
New System.Data.OleDb.OleDbConnection(strConnection)
Dim cmd As System.Data.OleDb.OleDbCommand = _
New System.Data.OleDb.OleDbCommand( _
"DROP INDEX PrimaryKey ON tblTest", cnn)
Try
cnn.Open()
cmd.ExecuteNonQuery()
Console.WriteLine("Index dropped OK!")
cmd.CommandText = _
"DELETE * FROM tblTest WHERE TestID = " & _
"(SELECT MIN(TestID) FROM tblTest)"
cmd.ExecuteNonQuery()
Console.WriteLine("Record deleted OK!")
cmd.CommandText = "ALTER TABLE tblTest DROP COLUMN TestID"
cmd.ExecuteNonQuery()
Console.WriteLine("Column dropped OK!")
cmd.CommandText = _
"ALTER TABLE tblTest ADD COLUMN TestID " & _
"INT NOT NULL IDENTITY"
cmd.ExecuteNonQuery()
Console.WriteLine("Column added OK!")
cmd.CommandText = _
"CREATE INDEX PrimaryKey ON " & _
"tblTest (TestID) WITH PRIMARY"
cmd.ExecuteNonQuery()
Console.WriteLine("Index created OK!")
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
cnn.Close()
Console.ReadLine()
End Try
End Sub

End Module
 
It has to be done in vb.net every time as a procedure has removed records
from the table, i want to recreate that field. I'ts the wish of a client.
Other people must not see that there wore records deleted.
Click to expand...

Those people should not be shown the table datasheet or the value of
the autonumber field, then. In a properly designed database they would
never need to.

John W. Vinson[MVP]
 
Back
Top