How to Import only new records from excel

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.

re: How to Import only new records from excel

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:
  
  Expand|Select|Wrap|Line Numbers

  1. SaveSetting appname:="MASTER Worksheet", Section:="Data Merge", _
  2.             Key:="First ID", setting:=1000000
• In Access, retrieve the ID of the first newly Added Record:
  
  Expand|Select|Wrap|Line Numbers

  1. Dim lngFirstRecID As Long
  2.  
  3. lngFirstRecID = GetSetting(appname:="MASTER Worksheet", Section:="Data Merge", _
  4.                           Key:="First ID", Default:="999999")
  5.  
  6. If lngFirstRecID = 999999 Then
  7.   MsgBox "No First Record ID stored"
  8. Else
  9.   MsgBox "First ID of Newly Added Record: " & lngFirstRecID
  10. End If

re: How to Import only new records from excel

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

re: How to Import only new records from excel

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.

re: How to Import only new records from excel

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.

re: How to Import only new records from excel

Thank you so much this will solve my issue

re: How to Import only new records from excel

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

Welcome to Bytes!