使用INSERT INTO时是否可以确定记录ID？ [英] Is it possible to determine the record ID when using INSERT INTO?

问题描述

假设在多用户环境中，你需要在一个VBA程序中追加查询SQL

看起来像INSERT INTO MyTable ......

和下一行读取MyVar = DMax（[MyKeyField]，MyTable ...


你永远无法确定MyVar将是

设置为附加查询运行时创建的键字段值

。现在，还有

其他方法 - 我知道 - 这将确保

你''正确的记录。但我想知道

如果附加查询中有任何retun值

情况，Access将返回一些内容

可用于唯一标识

附加的记录???

Suppose, in a multi-user environment, you
have append query SQL in a VBA procedure
that looks like INSERT INTO MyTable...
and the next line reads MyVar=DMax("[MyKeyField]","MyTable...

You can never be certain that MyVar will be
set to the key-field value that was created
when the Append query ran. Now, there are
other ways to do it - I know - that will ensure
you ''nab'' the correct record. But I was wondering
if there was any retun value in an Append query
situation that Access will return something that
can be used to uniquely identify the record that
was appended???

推荐答案

在Access 2000及更高版本中，您可以这样做：


函数ShowIdentity（）As Variant

Dim db As DAO.Database

Dim rs As DAO.Recordset


设置db = DBEngine（0 ）（0）

db.Execute" INSERT INTO MyTable（MyField）SELECT''nuffin''AS Expr1;"


Set rs = db .OpenRecordset（" SELECT @@ IDENTITY AS LastID;"）

ShowIdentity = rs！LastID

rs.Close


设置rs = Nothing

设置db = Nothing

结束功能

在任何版本的Access中，您都可以使用OpenRecorset，AddNew ，并阅读关键字段的值

。


-

Allen Browne - Microsoft MVP。西澳大利亚珀斯

访问用户提示 - http：// allenbrowne .com / tips.html

回复群组，而不是mvps dot org的allenbrowne。


" MLH" < CR ** @ NorthState.net写信息

新闻：eq **************************** **** @ 4ax.com ...

In Access 2000 and later, you can do this:

Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT ''nuffin'' AS Expr1;"

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
End Function

In any version of Access, you can OpenRecorset, AddNew, and read the value
of the key field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MLH" <CR**@NorthState.netwrote in message
news:eq********************************@4ax.com...

假设在多用户环境中，你有
附加查询SQL一个VBA程序

看起来像INSERT INTO MyTable ......

和下一行读取MyVar = DMax（" [MyKeyField]"，'MyTable .. 。


你永远不能确定MyVar将被设置为创建的关键字段值

当追加查询已经运行了。现在，有其他方法可以做到这一点 - 我知道 - 这将确保你能够确保正确的记录。但是我很想知道

如果附加查询中有任何重新调整值

情况，Access将返回一些内容

可用于唯一标识记录

附加了???

Suppose, in a multi-user environment, you
have append query SQL in a VBA procedure
that looks like INSERT INTO MyTable...
and the next line reads MyVar=DMax("[MyKeyField]","MyTable...

You can never be certain that MyVar will be
set to the key-field value that was created
when the Append query ran. Now, there are
other ways to do it - I know - that will ensure
you ''nab'' the correct record. But I was wondering
if there was any retun value in an Append query
situation that Access will return something that
can be used to uniquely identify the record that
was appended???

On Thu，2007年4月12日20:45:03 +0800，Allen Browne ;

< Al ********* @ SeeSig.Invalidwrote：

On Thu, 12 Apr 2007 20:45:03 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

>在任何版本的Access中，您都可以使用OpenRecorset ，AddNew，并读取关键字段的值
。

>In any version of Access, you can OpenRecorset, AddNew, and read the value
of the key field.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


我正在使用A97。 OpenRecordset.AddNew将工作4我。

感谢艾伦。

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I''m using A97. The OpenRecordset.AddNew will work 4 me.
Thankx Allen.

2007年4月12日星期四20:45:03 +0800，" ; Allen Browne"

< Al ********* @ SeeSig.Invalidwrote：


如果你要开一个记录集无论如何，我会用它来插入

。

那么，不要只是读取关键字段的值，我想你

需要这样做：

set rs = db.OpenRecordset（" SomeTable"，dbOpenTable）

rs.AddNew

... 。

rs.Update

rs.Move 0，rs.LastModified

ShowIdentity = rs！PK_Field


-Tom。

On Thu, 12 Apr 2007 20:45:03 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

If you''re going to open a recordset anyway, I would use it to insert
as well.
Then rather than just reading the value of the key field, I think you
need to do this:
set rs=db.OpenRecordset("SomeTable", dbOpenTable)
rs.AddNew
....
rs.Update
rs.Move 0, rs.LastModified
ShowIdentity = rs!PK_Field

-Tom.

>在Access 2000及更高版本中，您可以这样做：

功能ShowIdentity（ ）作为Variant

Dim db作为DAO.Database

Dim rs作为DAO.Recordset


设置db = DBEngine（0） （0）

db.Execute" INSERT INTO MyTable（MyField）SELECT 'nuffin''AS Expr1;"


设置rs = db.OpenRecordset（" SELECT @@ IDENTITY AS LastID;"）

ShowIdentity = rs！LastID

rs.Close


设置rs =无什么

设置db = Nothing
结束功能。

>In Access 2000 and later, you can do this:

Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT ''nuffin'' AS Expr1;"

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
End Function

In any version of Access, you can OpenRecorset, AddNew, and read the value
of the key field.

这篇关于使用INSERT INTO时是否可以确定记录ID？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！