如何从 Excel 中的 VBA 查询 Microsoft Access 数据库字段 [英] How to query Microsoft Access Database fields from VBA in Excel
问题描述
我目前正在编写一个程序,用于将在 Excel 表单中输入的内容保存到 Access 数据库中.每当我从 Excel 表单中的字段添加数据时,我都会尝试将 Access 中的主键字段增加1".
I am currently writing a program to save stuff entered in a form in Excel into a database in Access. I am trying to increment a primary key field within Access by "1" whenever I add data from the fields in my Excel form.
因为我已将此字段声明为 PRIMARY KEY NOT NULL 字段,所以除非已声明主键字段,否则它不允许我添加另一行数据.我不希望用户输入 PK 数据,因为这很愚蠢.
Because I have declared this field as a PRIMARY KEY NOT NULL field, it doesn't allow me add another row of data unless the primary key field has been declared. I do not want the user to enter the PK data, as that would be silly.
我将如何从 Excel 执行 DDL 到 Access 中,例如 MAX(CustomerID)
以找到 Access 表中的最大 ID,然后添加 MAX(CustomerID) + 1
使用 RS.FIELD("CustomerID") = MAX(CustomerID) + 1
进入 ID 字段.
How would I go about doing DDL from Excel into Access like say MAX(CustomerID)
to find the maximum ID wihtin the Access table then adding MAX(CustomerID) + 1
into the ID field using RS.FIELD("CustomerID") = MAX(CustomerID) + 1
.
如果您在这件事上提供任何帮助,我将不胜感激.提前致谢.
I would be grateful for any help in this matter. Thanks in advance.
推荐答案
我喜欢使用其他答案中建议的自动编号字段的想法.
I like the idea of using an AutoNumber field as suggested in other answers.
但是,如果您希望避开自动编号,您可以在 Excel 的 VBA 中使用 Access 的 DLookup 函数:
However if you wish to steer clear of AutoNumber you can use Access's DLookup function from your VBA in Excel:
rs.AddNew
rs.Fields("CustomerID") = Access.DLookup("Max(CustomerID)", "Customer") + 1
...
rs.Update
假设您的基表是 Customer
.
您必须在工具->引用中添加对 Microsoft Access 的引用
You would have to add the reference to Microsoft Access in Tools->References
这篇关于如何从 Excel 中的 VBA 查询 Microsoft Access 数据库字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!