Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
3  
Maybe its my misunderstanding, but why cant you use autonumber in MSAccess? – David Jan 4 '10 at 22:22
The Autonumber will be appropriate only if the ID is being used only for internal record-keeping purposes (e.g., to related records in a child table to those in a parent table). If the ID is being exposed to the users, you need to use your own code for incrementing an ID field. VBA code for doing that in Access has been posted all over the place in any number of Access forums, so it oughtn't be hard to find (it's even been posted on SO!). – David-W-Fenton Jan 5 '10 at 2:34

3 Answers

up vote 0 down vote accepted

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

share|improve this answer
2  
This could be quite unsafe if there are several users. – Remou Jan 5 '10 at 0:09
Cheers i was looking for a way to do it in VB – Seedorf Jan 5 '10 at 2:37
up vote 5 down vote

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

share|improve this answer
Thats a good idea. But sadly it has to be done in the VB side in excel. I'll up you for the answer tho. – Seedorf Jan 5 '10 at 2:38
up vote 0 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.