使用一个rs集也一样:先设置一个ID来标记记录号,对应的值为Value.以下写的仅供参考,无非是重复使用了一些语句来实现目标,自己调试。 dim N as integer dim strSQL as string dim conn as new ADODB.connection dim rs as new ADODB.recordset strSQL = app.path & "A.mdb" strSQL = Replace(strSQL, "\\", "\")
With conn If .State = 1 Then .Close End If .CursorLocation = adUseClient .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & strSQL .Open End With With rs .Open "SELECT Id, IDvalue FROM Table1 ORDER BY Id;", conn, adOpenStatic, adLockOptimistic End With for N = 3 to 5 '改动3及以后的Id号 with rs .move (N-1) .Id = (N+1) .update end with next N with rs .addnew '新增一个ID为3的项 .Id =3 .Value3 = XX 'XX是你赋予的新增项目的值。 .update end with With rs '重新查出rs集并排序 .close .Open "SELECT Id, IDvalue FROM Table1 ORDER BY Id;", conn, adOpenStatic, adLockOptimistic .movelast .movefirst End With dim newValue as ---'求得前5项的值 for N =1 to (rs.recordcount-1) with rs newvalue =newvalue + .valueN .movenext end with next N with rs .movelast .value6 = newvalue '将前5项的值赋值最后一项的值。 .update end with
如果非要用sql语句来实现,步骤反而复杂。而且,非要指定字段的位置似乎没什么必要。
dim N as integer
dim strSQL as string
dim conn as new ADODB.connection
dim rs as new ADODB.recordset strSQL = app.path & "A.mdb"
strSQL = Replace(strSQL, "\\", "\")
With conn
If .State = 1 Then
.Close
End If
.CursorLocation = adUseClient
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & strSQL
.Open
End With With rs
.Open "SELECT Id, IDvalue FROM Table1 ORDER BY Id;", conn, adOpenStatic, adLockOptimistic
End With
for N = 3 to 5 '改动3及以后的Id号
with rs
.move (N-1)
.Id = (N+1)
.update
end with
next N with rs
.addnew '新增一个ID为3的项
.Id =3
.Value3 = XX 'XX是你赋予的新增项目的值。
.update
end with With rs '重新查出rs集并排序
.close
.Open "SELECT Id, IDvalue FROM Table1 ORDER BY Id;", conn, adOpenStatic, adLockOptimistic
.movelast
.movefirst
End With dim newValue as ---'求得前5项的值
for N =1 to (rs.recordcount-1)
with rs
newvalue =newvalue + .valueN
.movenext
end with
next N with rs
.movelast
.value6 = newvalue '将前5项的值赋值最后一项的值。
.update
end with
rs.close
conn.close