如何从 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屋！