How to Import only new records from excel

[kinuthia54]

Hi, I have a macro that combines many worksheets into one and after I need this worksheet to update a Table in Access but only with newly generated records. I have linked the combined worksheet to the Access DB as a table though the table I want to update is different which contains previously generated records. Any ideas, especially if I can add the code to the existing macro or a query that runs when i open the DB.

[ADezii]

@kinuthia54
On little trick that I use when transferring limited information/data between Excel and Access is to use the System Registry for the transference since they will Save and Retrieve data from the same Key using SaveSetting and GetSetting(). A simple example below will illustrate this point:

• In Execl, save the First ID of the newly Added Record:
  

  SaveSetting appname:="MASTER Worksheet", Section:="Data Merge", _
              Key:="First ID", setting:=1000000

• In Access, retrieve the ID of the first newly Added Record:
  

  Dim lngFirstRecID As Long
   
  lngFirstRecID = GetSetting(appname:="MASTER Worksheet", Section:="Data Merge", _
                            Key:="First ID", Default:="999999")
   
  If lngFirstRecID = 999999 Then
    MsgBox "No First Record ID stored"
  Else
    MsgBox "First ID of Newly Added Record: " & lngFirstRecID
  End If

[kinuthia54]

Thanks Adezii,

The only issue I have is its hard to tell which is the first newly added record, since this main excel sheet is re consituted by combining many other worksheets around 2000 of them which contain the changes. The only comparison i have is with the Access table that has the earlier records. hence I need something to compare the old access table and the excel sheet and only pick what isnt in the access table to import.

Thanks

[FishVal]

If you set a unique index to the Access table field (or multifield index for several fields) which is supposed to be unique per record, then database engine automatically omit adding records already present in your table.

[NeoPa]

If you have the worksheet linked as a pseudo-table in Access (I think you have), then you can design a query which includes both tables with a Type-2 connection between the Unique-ID fields and filter out all those records except where the destination key is Null. That will copy only the non-existent records by design and will run more efficiently than copying all and relying on the existing ones to fail.

[kinuthia54]

Thank you so much this will solve my issue

[NeoPa]

Good for you, and we're pleased to have helped.

Welcome to Bytes!