How to query Microsoft Access Database fields from VBA in Excel

Question

Hey guys,

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.

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.

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.

Answer 1

You can declare a column as Autoincrement in Access. Then it will get subsequent values automatically.

Answer 2

I like the idea of using an AutoNumber field as suggested in other answers.

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

Assuming your base table is Customer.

You would have to add the reference to Microsoft Access in Tools->References

Answer 3

Would it not be possible to create the form in access itself and then you can just use the auto number field of the database? Unless there is a specific reason to keep it in excel it seems like a bit of a convoluted solution to the problem