Append values to Access table from Excel with ID autogenerated

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

Arun Raja

Posted 2015-04-17T04:40:56.327

Reputation: 121

I have created temporary table which imports the value and have a written a append query to append the values. – Arun Raja – 2015-04-17T09:10:44.243

Hope the question is clear. I want to generate ID auto number from the previous number in the table. – Arun Raja – 2015-04-17T09:58:59.397

What updates? The product value in access? And you want that to update the excel sheet? – Raystafarian – 2015-04-17T12:17:45.447

1If 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 – 2015-05-09T12:27:03.353

Append values to Access table from Excel with ID autogenerated

Answers