Question

Access 怎么获取插入一条记录后的“自动编号”的id号

Access 中用 INSERT INTO语句插入一条记录后，怎么获取这条记录的“自动编号”的id号？
我知道PHP+MYSQL环境中可以用mysql_insert_id()函数获取。
哪位高手可以告诉我怎么可以在Access中获取这样的id号。

Answer 1

方法一：

很简单，先取id后update

dim rcd as long
Dim rs As New ADODB.Recordset
rs.Open "表1", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.AddNew
rcd=rs("id")
rs.update
rs.Close

docmd.runsql "update … where id="&str(rcd)

方法二：

必须 jet oledb 4 支持
Private Sub AutoIncTest()
Dim cnn As ADODB.Connection
'Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Set cnn = CurrentProject.Connection
'Set cmd = New ADODB.Command
'Set cmd.ActiveConnection = cnn
'cmd.CommandType = adCmdText
'cmd.CommandText = "INSERT INTO tblNewOrder2 " & _
"(ItemId, Quantity) VALUES (1, 20)"
'cmd.Execute
'Set cmd = Nothing
cnn.execute "insert into tblneworder2 (item) values ('dd')"
Set rst = New ADODB.Recordset
rst.Open "SELECT @@IDENTITY AS LastOrderId", _
cnn,
Options:=adCmdText
Debug.Print "OrderId for new record = " & _
rst("LastOrderId")
rst.Close
Set rst = Nothing
End Sub

Answer 2

呵,只要想通了会很简单的..

思路:
1.先把数据添加到数据库中
2.添加完成后,再执行一次查询最大ID的操作即可.
查询例:select max(id) from table 或select id from table order by id desc

Answer 3

这样是不严谨的