访问SQL：从插入语句或从DAO.QueryDef获取受影响的记录的标识值(Access SQL: Get the identity value of the affected record from

访问SQL：从插入语句或从DAO.QueryDef获取受影响的记录的标识值(Access SQL: Get the identity value of the affected record from an insert statement Or From a DAO.QueryDef)

我必须转换一些SQL查询来访问SQL。 其中一些使用@@ identity将两个链接的记录插入到两个不同的表中。 不幸的是，访问查询只能包含一条语句（即，在插入语句后我不能选择@@标识）。

这不可能：

insert into table1 (values) select values from table1 where id=@id; select @@identity;

这个示例语句应该将表1中的记录复制到上述表中的新记录中，然后在表2中执行相同的操作，引用Table1中的新记录。

以下是sql查询的外观：

INSERT INTO Table1 ( some_value, the_other_values) SELECT @new_value as some_value, the_other_values FROM Table1 WHERE id = @oldID SELECT @New_id = @@identity INSERT INTO Table2 ( idP, other_values) SELECT @New_id AS idP, other_values FROM Table2 WHERE (idP = @Old_id)

我试图用两个语句，使用vba，但我没有办法从另一个查询访问@@身份。

这里是vba - 我省略了声明，实际的代码是一个循环，但这是重要的部分：

Set qdf = CurrentDb.QueryDefs("first_part_of_query") qdf!New_id = userInput_newID qdf!oldID = userInput_oldID qdf.Execute Set qdf = CurrentDb.QueryDefs("second_part_of_query") qdf!New_id = the_identityvalue 'WHAT DO I SET THIS TO? qdf.Execute

I have to convert some SQL queries to access SQL. Some of them use @@identity to insert two linked records into two different tables. Unfortunately, an access query can only contain one statement(i.e. I can not select @@identity after the insert statement).

This is not possible:

insert into table1 (values) select values from table1 where id=@id; select @@identity;

This example statement is supposed to Copy a record from Table1 to a new record in the said table and then do the same in Table2, referencing the new record from Table1.

Here's what the sql query looks like:

INSERT INTO Table1 ( some_value, the_other_values) SELECT @new_value as some_value, the_other_values FROM Table1 WHERE id = @oldID SELECT @New_id = @@identity INSERT INTO Table2 ( idP, other_values) SELECT @New_id AS idP, other_values FROM Table2 WHERE (idP = @Old_id)

I tried to do this in two statements, using vba, but I have found no way to access @@identity from the other query.

Here's the vba - I omitted the declarations, the actual code is a loop but this is the important part:

Set qdf = CurrentDb.QueryDefs("first_part_of_query") qdf!New_id = userInput_newID qdf!oldID = userInput_oldID qdf.Execute Set qdf = CurrentDb.QueryDefs("second_part_of_query") qdf!New_id = the_identityvalue 'WHAT DO I SET THIS TO? qdf.Execute

最满意答案

您需要使用数据库的单个实例，然后您可以使用OpenRecordset("SELECT @@IDENTITY")来获取标识值。

Dim db As DAO.Database Set db = CurrentDb Set qdf = db.QueryDefs("first_part_of_query") qdf!New_id = userInput_newID qdf!oldID = userInput_oldID qdf.Execute Set qdf = db.QueryDefs("second_part_of_query") qdf!New_id = db.OpenRecordset("SELECT @@IDENTITY").Fields(0) qdf.Execute

You need to use a single instance of your database, and then you can just use OpenRecordset("SELECT @@IDENTITY") to obtain the identity value.

Dim db As DAO.Database Set db = CurrentDb Set qdf = db.QueryDefs("first_part_of_query") qdf!New_id = userInput_newID qdf!oldID = userInput_oldID qdf.Execute Set qdf = db.QueryDefs("second_part_of_query") qdf!New_id = db.OpenRecordset("SELECT @@IDENTITY").Fields(0) qdf.Execute