如何从MySQL数据库获取最后一个插入值并更新Access中的字段? [英] How can I get the last insert value from MySQL database and update a field in Access?

问题描述

我有一个涉及人类遗体的复杂数据库，因此用于输入数据的Access表单也非常复杂.我有几个子窗体(Skeleton_index，Element_index和其他几个).我提到的两个子表单将数据发送到未绑定的主表单上的它们各自的字段(element_link，skelenet_link)，其他表单从那里使用数据.对于Skeleton_index，用户必须输入个人的姓名，并且该姓名会立即显示在skeleton_index字段中.我遇到的问题是，我不希望用户必须输入Element_index的主键数据，因为它应该为auto_increment.这是一个问题，因为Element_id中的自动递增的值不会立即显示在element_link字段中.为了使其显示，用户必须创建一个新元素，然后返回到他们正在编辑的元素.我想避免这种情况.

I have a complex database involving human remains, so the Access forms to input the data are also quite complicated. I have several subforms (Skeleton_index, Element_index, and a few others). The two subforms I mentioned by name send data to their respective fields (element_link, skeleton_link) on an unbound master form, from where the data is used by other forms. For Skeleton_index the user has to input the name of the individual and it shows up in the skeleton_index field instantly. The problem I have is that I don't want the user to have to input the primary key data for Element_index as it should auto_increment. This is a problem because the auto incremented value in Element_id doesn't show up in the element_link field instantly. To get it to show, the user has to create a new element and then go back to the one they were editing. I want to avoid this.

我想要做的是在获得焦点时将Element_id文本框更新为新的auto_increment主键.VBA代码应从MySQL(InnoDB)Element_index表中获取最后一个主键，向其中添加一个，然后以Element_index形式更新Element_id字段中的值.

What I want to do is update the Element_id textbox to a new auto_increment primary key when it gets focus. The VBA code should fetch the last primary key from the MySQL (InnoDB) Element_index table, add one to it and then update the value in the Element_id field in the Element_index form.

这是我的尝试，只是失败了.

This is my attempt and it just plain fails.

Private Sub Element_id_GotFocus()
    SQL = "SELECT LAST_INSERT_ID();"
    lastID = DoCmd.RunSQL(SQL)
    newID = Int(lastID) + 1
    Element_id.Value = newID
End Sub

数据库在最初的实例中将只有一个用户，但是将来可能会更多.

The database will have a single user in the first instance, but there may be more in the future.

解决方案:我添加了一个带有两个宏的按钮:一个用于保存插入的记录的宏，另一个用于刷新表单的宏.不需要复杂的VBA.

SOLUTION: I added a button with two macros: one that saves the inserted record and a second one that refreshes the form. No need for complicated VBA.

推荐答案

您的尝试在很多方面都是错误的.主要的是:

Your attempt is wrong in many ways. The main ones being:

1. 您不能使用 DoCmd.RunSQL 来运行选择查询.它只运行动作查询.
2. 您需要使用传递查询来运行包含MySQL特定功能的查询.
3. 除非您在用于将行插入到感兴趣的表中的同一连接上执行，否则不能使用 LAST_INSERTED_ID().

1. You can't use DoCmd.RunSQL to run select queries. It only runs action queries.
2. You need to use a passthrough query to run queries that contain MySQL-specific functions.
3. You can't use LAST_INSERTED_ID() unless you execute it over the same connection that was used to insert a row into the table you're interested in.

最好使用QueryDef执行直通查询，并使用INFORMATION_SCHEMA.TABLES表检索下一个自动编号:

You're better off using a QueryDef to execute a passthrough query, and using the INFORMATION_SCHEMA.TABLES table to retrieve the next autonumber:

'Create a new temporary query, uses `With` instead of storing it in a variable
With CurrentDb.CreateQueryDef("")
    'Make it a pass-through query that connects to MySQL
    .Connect = "ODBC;<enter connection string here>"
    'Set the SQL for the query: queries table definition, gets auto_increment seed
    .SQL = "SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ""<table name here>"" AND TABLE_SCHEMA = ""<database name here>"""
    'The query should return records (not an action query)
    .ReturnsRecords = True
    'Execute the query, get the results into a snapshot-type recordset
    'And set your field equal to the first column of the first row of the recordset
    Element_id.Value = .OpenRecordset(dbOpenSnapshot).Fields(0).Value
End With

这篇关于如何从MySQL数据库获取最后一个插入值并更新Access中的字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！