strSql = "select * into temp1 from 表1 order by name desc"
strSql = "alter table temp1 add COLUMN NewOrderId AUTOINCREMENT
strSql = "update 表1,temp1 set 表1.OrderId = temp1.NewOrderId where 表1.serialid = temp1.serialid"
注意上述3句SQL语句,需要增加某个临时表,用完了手动删除掉。不用临时表也能做,不过性能比较差
Function asfs()
Dim strSql As String
DropTable "temp1"
strSql = "select * into temp1 from 表1 order by name desc"
CurrentProject.Connection.Execute strSql
strSql = "alter table temp1 add COLUMN NewOrderId AUTOINCREMENT(1,1)"
CurrentProject.Connection.Execute strSql
strSql = "update 表1,temp1 set 表1.OrderId = temp1.NewOrderId where 表1.serialid = temp1.serialid"
CurrentProject.Connection.Execute strSql
End Function
Function DropTable(ByVal table As String)
Dim strSql As String
On Error Resume Next
Dim Rs As New ADODB.Recordset
Dim Conn As New ADODB.Connection
Dim I As Integer
Set Conn = CurrentProject.Connection
Rs.Open "select * from 表1 order by f1", Conn, adOpenDynamic, adLockOptimistic
I = 0
Do While Not Rs.EOF
I = I + 1
Rs.Fields("OrderID") = I
Rs.Update
Rs.MoveNext
Loop