Access 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将两个链接的记录插入到两个不同的表中.不幸的是，访问查询只能包含一个语句(即，在插入语句之后我不能选择@@ identity).

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).

这是不可能的:

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

该示例语句应该将表1中的记录复制到所述表中的新记录，然后在表2中进行相同操作，并引用表1中的新记录.

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.

这是sql查询的样子:

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)

我尝试使用vba在两个语句中执行此操作，但是我没有找到从其他查询访问@@ identity的方法.

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

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

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")获取标识值.

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

这篇关于Access SQL:从插入语句或从DAO.QueryDef获取受影响的记录的标识值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！