如何产生一个ID字段,如果删去一条记录,就自动更改这个ID,使得ID始终连续,不断开,(后面的自动加一)
比如
ID Name Address Phone
1 Jim China 4321032
2 Make China 6512890
3 Tom USA 3780172
4 Bill France 5210317
删除 3 Tom USA 3780172 后变成:
ID Name Address Phone
1 Jim China 4321032
2 Make China 6512890
3 Bill France 5210317
用自动编号不行!
比如
ID Name Address Phone
1 Jim China 4321032
2 Make China 6512890
3 Tom USA 3780172
4 Bill France 5210317
删除 3 Tom USA 3780172 后变成:
ID Name Address Phone
1 Jim China 4321032
2 Make China 6512890
3 Bill France 5210317
用自动编号不行!
Dim x As Integer '循环计数器
Dim intID As Integer '用来作为新记录的 ID 号
Dim sSql As String
Dim Rec As New ADODB.Recordset
Rec.Open "SELECT * FROM " & TableName & " order by [ID]", cnId, adOpenStatic, adLockBatchOptimistic
If Rec.RecordCount = 0 Then
AutoId = 1
Rec.Close
Set Rec = Nothing
Exit Function
End If
If Not Rec.EOF Then
Rec.MoveFirst
For x = 1 To Rec.RecordCount Step 1
If x <> Rec.Fields("ID") Then
intID = x
Exit For
End If
intID = x + 1
Rec.MoveNext
Next x
End If
Rec.Close
Set Rec = Nothing
AutoId = IIf(intID, intID, 1)
End Function
,然后每次删除记录后,执行如下代码:Adodc1.Refresh
n = Adodc1.Recordset.RecordCount
If n = 0 Then
Exit Sub
End If
For i = 1 To n
Adodc1.Recordset("ID") = Adodc1.Recordset.Book
Adodc1.Recordset.MoveNext
Next
Private Sub Command1_Click()
Adodc1.Refresh
n = Adodc1.Recordset.RecordCount
If n = 0 Then
Exit Sub
End If
For i = 1 To n
Adodc1.Recordset("ID1") = i
Adodc1.Recordset.MoveNext
NextEnd Sub