Import Excel to existing table without duplicates

[cathalfarrell]

Hi Everyone,

I have a an excel sheet of member details pulled from another system. When new members are added I want to import these into the same table.

If I do it creates duplicate values for members that were already existing. If I link to the excel sheet I get a criteria mismatch in my forms and I'm not sure why.

If I run a makro to delete the current table and import the sheet to a new table of the same name, the new table has loads of blank records with the imported data at the end.

Can anyone advise the best way to import this data into the table?

Many thanks
C.

 

[John Big Booty]

Try importing the excel data into a temporary table, and then use an unmatched query to import the new data into your existing table.

 

[John Big Booty]

Here's an example of a working DB that I use on a daily basis. Copy the DB and excel file to the same location.

 

[cathalfarrell]

Hi John,

Thanks for you help, I'm not very familiar with working with DB's and a bit of a newbie.

Can you attach the DB and Spreadsheet you mentioned in your post?

I really appreciate your help.

C.

 

[John Big Booty]

Doh!!!

It needed to be compacted a little

NOTE:
This DB sample contains date criteria, and date sensitive events, which will need to be removed, if this DB is downloaded more than three months from the date of posting.

 

[JohnoGreg]

Hey John Big Booty,
Thank you for the sample Access file - *I have created an access database which is modelled on this. So far, my Import command in FRM_import is not recognising the file. I receive the message to indicate that no file is present - even though I have an excel file named "Import" saved in the path location (desktop). I have attempted changing the path to a folder with the same result.
Below is my Command string - I am not sure where I have gone wrong, and would be grateful for any advice:



DoCmd.SetWarnings False
* * path = Application.CurrentProject.path & "\Import"

* * 'Loop through the folder & build file list
* * strFile = Dir("T:\AdhocReporting\Corporate\User\Import.xls")

* * While strFile <> ""
* * * * 'add files to the list
* * * * intFile = intFile + 1
* * * * ReDim Preserve strFileList(1 To intFile)
* * * * strFileList(intFile) = strFile
* * * * strFile = Dir()
* * Wend

* * 'see if any files were found
* * If intFile = 0 Then
* * * * MsgBox "Could Not Find File 'Import.XLS'"
* * * * DoCmd.Close acForm, "Data_Import"
* * Exit Sub
* * End If

* * 'cycle through the list of files
* * For intFile = 1 To UBound(strFileList)
* * * * filename = Application.CurrentProject.path & "" & strFileList(intFile)
* * * * 'DoCmd.TransferText acImportDelim, , "TBL_OPApptData", filename, False
* * * *
* * * * DoCmd.TransferSpreadsheet acImport, , "TBL_OPApptData", filename, False
* * * *
* * Next intFile


Does anybody have any suggestions?

Best wishes

John

 

[arnelgp]

maybe it's not .xls that is present, maybe .xlsx:

DoCmd.SetWarnings False
  path = Application.CurrentProject.path & "\Import"

  'Loop through the folder & build file list
  strFile = Dir("T:\AdhocReporting\Corporate\User\Import.xl*")

  While strFile <> ""
    'add files to the list
    intFile = intFile + 1
    ReDim Preserve strFileList(1 To intFile)
    strFileList(intFile) = strFile
    strFile = Dir()
  Wend

  'see if any files were found
  If intFile = 0 Then
    MsgBox "Could Not Find File 'Import.XLS'"
    DoCmd.Close acForm, "Data_Import"
  Exit Sub
  End If

  'cycle through the list of files
  For intFile = 1 To UBound(strFileList)
    filename = Application.CurrentProject.path & "\" & strFileList(intFile)
    'DoCmd.TransferText acImportDelim, , "TBL_OPApptData", filename, False
    
    DoCmd.TransferSpreadsheet acImport, , "TBL_OPApptData", filename, False
    
  Next intFile

 

[The_Doc_Man]

John, you are getting answers so you know you are not being ignored or neglected. However, may I offer a suggestion?

When looking at older threads, it MIGHT be better if you opened a new thread and just did a copy of the thread and paste in the hyperlink to it. You were appending to a nine-year-old thread. You never know when the member in question has moved on to become inactive on the forum - or has passed on. Big John Booty has been inactive on this forum since 2017 (which you could determine from his profile if you looked at it.)