Append values to Access table from excel with ID autogenerated

Question

I have a access database with some tables which get updated daily with some values. I need the tables to have a ID field which is autogenerated 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 value which I have . I also need a 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 date with new value and it has to be put to the access db with ID automatically incremented.

Answer 1

I created an Excel spreadsheet called ExcelData.xlsx with the sample data you've supplied formatted as a table:

I've then created a form with a button to run some VBA that will import the data to Access (the button name is cmdImport):

As part of the form's VBA, I've written a function that checks whether a table exists or not. The name of the table is given as an argument to function then the function returns TRUE if found or FALSE if not found:

Public Function TableExists(name As String) As Boolean

    ' A function to check whether a table exists
    ' Pass the name of the table as the argument
    ' the function will return TRUE if found or FALSE if not found

    TableExists = DCount("*", "MSysObjects", "Name = '" & name & "' AND Type = 1")

End Function

..and then on the cmdImport button's on-click event I have the following (you'll need replace some stuff for your particular situation, e.g. the file path and file name of your Excel spreadsheet):

Private Sub cmdImport_Click()

' 1 - Delete your Access table "tblProducts" so we can reset the Autonumber field

    If _
        TableExists("tblProducts") = True _
    Then

        ' Delete old "tblProducts":
        DoCmd.DeleteObject acTable, "tblProducts"

    Else

        ' Do nothing as table doesn't already exist

    End If

' 2 - Create and define new Access table "tblProducts"... this allows us to reset the Autonumber values for the ID fields

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fldID As DAO.Field, fldProductName As DAO.Field, fldValue As DAO.Field

    Set db = CurrentDb

    ' Create table defintion
    Set tdf = db.CreateTableDef("tblProducts")

    ' Create field definitions
    Set fldID = tdf.CreateField("ID", dbLong)
    fldID.Attributes = dbAutoIncrField
    fldID.Required = True

    Set fldProductName = tdf.CreateField("ProductName", dbText)
    fldProductName.AllowZeroLength = False
    fldProductName.Required = True

    Set fldValue = tdf.CreateField("ProductValue", dbText)
    fldValue.AllowZeroLength = False
    fldValue.Required = True

    ' Append fields to table
    tdf.Fields.Append fldID
    tdf.Fields.Append fldProductName
    tdf.Fields.Append fldValue

    ' Append table to db
    db.TableDefs.Append tdf

    ' Give it a nudge
    db.TableDefs.Refresh
    Application.RefreshDatabaseWindow

    ' Clean up memory
    Set fldID = Nothing
    Set fldProductName = Nothing
    Set fldValue = Nothing
    Set tdf = Nothing
    Set db = Nothing

' 3 - Check for the old imported data from Excel ("ExcelData") and delete it

    If _
        TableExists("ExcelData") = True _
    Then

        ' Delete old "Excel Data":
        DoCmd.DeleteObject acTable, "ExcelData"

    Else

        ' Do nothing as table doesn't already exist

    End If

' 4 - Import new data from Excel ("ExcelData")

    DoCmd.TransferSpreadsheet acImport, _
                              9, _
                              "ExcelData", _
                              "C:/Your/File_Path/And_File_Name.xlsx", _
                              -1

' 5 - Append new data from "ExcelData" in to "tblProducts"

    Dim sqlAppend As String

    sqlAppend = "INSERT INTO tblProducts ( ProductName, ProductValue )" _
              & " SELECT ExcelData.ProductName, ExcelData.ProductValue" _
              & " FROM ExcelData;"

    CurrentDb.Execute sqlAppend

End Sub

This should result in these tables..

..with new data in your tblProducts table with the ID applied:

Please note that this method completely overwrites everything that was imported before and then re-imports it and assigns the ID field again to all the new data. This means that a record that had, for example, an ID of 1 in a previous import may get a different ID number the next time it's imported depending on whether the order of records in the Excel spreadsheet changes on a new import.

Answer 2

the below will write from excel to access and as the prior comment mentioned use autonumber in the data field type.

Sub LogRecord()

 Dim strSQL As Variant
 Dim accApp As Object
 Dim srcs As Variant
 Dim msg1 As Variant


'your access db
srcs = "C:\Documents and Settings\user\My Documents\Programs\Past Versions\Database V2.mdb"  ''' Live location '''

strSQL = "Select * from [tablename];"


Set accApp = GetObject(srcs, "access.Application")
Set Db = DAO.OpenDatabase(srcs)
Set rs = Db.OpenRecordset(strSQL)
    accApp.Visible = False

 For clref = 1 To Range("A500000").End(xlUp).Row
   Cells(clref, 1).Activate

   On Error Resume Next
       If Len(Cells(clref, 1)) >= 1 Then
           rs.AddNew

           rs![Field1] = Sheets("Gather").Cells(clref, 1).Value
           rs![Field2] = Sheets("Gather").Cells(clref, 2).Value
           rs![Field3] = Sheets("Gather").Cells(clref, 3).Value

           rs.Update
       End If

   Next clref

       If Not rs Is Nothing Then rs.Close

       Set rs = Nothing
       Set Db = Nothing
       accApp.DoCmd.RunSQL strSQL
       accApp.Application.Quit


   End Sub

Answer 3

When you create a table in Access you have different options for the datatype of the new columns you want to create. One of the datatypes is called "AutoNumber". Here some information: http://en.wikipedia.org/wiki/AutoNumber

Set your column ID as an autoNumber and make it a primary key. Now everytime you insert a relation into the table ID will automatically increment a new number.