在 MS Access 中执行查询时如何保留主键的自动编号？

发布于 2024-11-25 22:45:33 字数 368 浏览 6 评论 0原文

我正在尝试在查询中执行类似以下操作：

Dim rs As RecordSet
Dim NewPrimaryKey as Long

Set rs = Currentdb.OpenRecordset("SELECT * FROM MyTable WHERE MyPrimaryKey Is Null;")

With rs
      .AddNew
      NewPrimaryKey = !MyPrimaryKey
      !DateValue = Now()
      ...
      .Update
End With

任何关于如何使用我可以使用 JET 引擎在 MS Access 2003 中执行的查询来执行此操作的指示将不胜感激。

原文

I am trying to do something like the following in a query:

Dim rs As RecordSet
Dim NewPrimaryKey as Long

Set rs = Currentdb.OpenRecordset("SELECT * FROM MyTable WHERE MyPrimaryKey Is Null;")

With rs
      .AddNew
      NewPrimaryKey = !MyPrimaryKey
      !DateValue = Now()
      ...
      .Update
End With

Any pointers on how to do t his using a query that I can execute in MS Access 2003 using the JET engine would be greatly appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

忱杏 2024-12-02 22:45:33

你可以使用两条SQL语句来完成我认为你想要的。首先是插入。然后“SELECT @@Identity”以获取最后添加的自动编号值。将对象变量用于两个 SQL 语句的数据库连接。

Dim db As DAO.Database
Dim NewPrimaryKey As Long
Dim strInsert As String

strInsert = "INSERT INTO MyTable ([DateValue])" & vbCrLf & _
    "VALUES (Now());"
Set db = CurrentDb
db.Execute strInsert, dbFailOnError
NewPrimaryKey = db.OpenRecordset("SELECT @@Identity")(0)
Debug.Print NewPrimaryKey
Set db = Nothing

我将字段名称 DateValue 括在方括号中，因为它是保留字。

编辑：如果您使用一条 SQL 语句插入多条记录，SELECT @@Identity 仍将为您提供最后一个自动编号。它是通过该连接实例执行的插入的最后一个自动编号。而且您不会得到所使用的自动编号的序列；只有最后一张。

strInsert = "INSERT INTO MyTable3 ([some_text])" & vbCrLf & _
    "SELECT TOP 3 foo_text FROM tblFoo" & vbCrLf & _
    "WHERE foo_text Is Not Null ORDER BY foo_text;"

You can use two SQL statements to accomplish what I think you want. First an INSERT. Then "SELECT @@Identity" to get the last added autonumber value. Use an object variable for the database connection with both SQL statements.

Dim db As DAO.Database
Dim NewPrimaryKey As Long
Dim strInsert As String

strInsert = "INSERT INTO MyTable ([DateValue])" & vbCrLf & _
    "VALUES (Now());"
Set db = CurrentDb
db.Execute strInsert, dbFailOnError
NewPrimaryKey = db.OpenRecordset("SELECT @@Identity")(0)
Debug.Print NewPrimaryKey
Set db = Nothing

I enclosed the field name DateValue in square brackets because it is a reserved word.

Edit: If you insert multiple records with one SQL statement, SELECT @@Identity will still give you the last autonumber. It's the last autonumber for inserts performed through that connection instance. And you don't get a sequence of the autonumbers used; only the last one.

strInsert = "INSERT INTO MyTable3 ([some_text])" & vbCrLf & _
    "SELECT TOP 3 foo_text FROM tblFoo" & vbCrLf & _
    "WHERE foo_text Is Not Null ORDER BY foo_text;"

回复收藏 0 原文

~没有更多了~