2

I have an Access database with some tables which get updated daily with some values. I need the tables to have an ID field which is auto-generated when the other values of the table are imported from Excel. I want to automate all by keeping a button and writing a VBA code. Right now I have a form which imports the value into the table, but it just imports the exact values which I have. I also need an extra column of ID that has to be auto generated when I import. Please help me.

Excel Sheet Sample:

ProductName | ProductValue
------------+--------------
ABC         | 76
SDF         | 87

DATABASE TABLE

ID|ProductName| Product Value
--+-----------+--------------
1 | ABC       |76
2 | SDF       |87

Excel sheet updates each day with new value and it has to be put to the Access database with ID automatically incremented. So after the daily update the values are incremented from the previous value. 

Dim filepath As String

filepath = "E:\rt.xlsx"
If FileExist(filepath) Then
    DoCmd.TransferSpreadsheet acImport, , "TempfromExcel", filepath, True
    DoCmd.OpenQuery "qryAppend", acViewNormal

Else
    MsgBox ("File not found.")
End If
Improve this question
4
  • I have created temporary table which imports the value and have a written a append query to append the values.
    – Arun Raja
    Apr 17, 2015 at 9:10
  • Hope the question is clear. I want to generate ID auto number from the previous number in the table.
    – Arun Raja
    Apr 17, 2015 at 9:58
  • What updates? The product value in access? And you want that to update the excel sheet?
    – Raystafarian
    Apr 17, 2015 at 12:17
  • 1
    If you want to have a unique value column, you should add an AutoNumber field to your Access table, Then if you want to have a field that always return from 1 to row counts of your table you should create a View for that ;).
    – shA.t
    May 9, 2015 at 12:27

1 Answer 1

Reset to default
0

As stated in the comments, the best method is to set the ID field to be an AutoNumber. If you already have a bunch of data in that field, you will need to reset the field.

Setting an AutoNumber

Once that's done, simply don't include the field in your append query. Access will automatically fill in whatever the next value is for each new record.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .