Import to access table from excell "Ignore duplicates"

[Ifshaanm]

Hi guys

I have code written which imports excel data to a access table but after the first import it fails due to duplicates, how can i tell it to ignore duplicates in the table and only copy new records?

thanks

code is below.

Function SyncEmployes()
 Dim lngColumn As Long
 Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
 Dim dbs As DAO.Database
 Dim rst As DAO.Recordset
 Dim blnEXCEL As Boolean
 blnEXCEL = False
  On Error Resume Next
 Set xlx = GetObject(, "Excel.Application")
 If Err.Number <> 0 Then
       Set xlx = CreateObject("Excel.Application")
       blnEXCEL = True
 End If
 Err.Clear
 On Error GoTo 0
  xlx.Visible = False
  Set xlw = xlx.Workbooks.Open(" DB\Employees.xls", , True) ' opens in read-only mode
  Set xls = xlw.Worksheets("sheet1")
  Set xlc = xls.Range("A2")
 Set dbs = CurrentDb()
 
 Set rst = dbs.OpenRecordset("Employees", dbOpenDynaset, dbAppendOnly)
 
 Do While xlc.value <> ""
       rst.AddNew
             For lngColumn = 0 To rst.Fields.Count - 1
                   rst.Fields(lngColumn).value = xlc.Offset(0, lngColumn).value
             Next lngColumn
       rst.Update
       Set xlc = xlc.Offset(1, 0)
 Loop
  rst.Close
 Set rst = Nothing
  dbs.Close
 Set dbs = Nothing
 
 Set xlc = Nothing
 Set xls = Nothing
 xlw.Close False
 Set xlw = Nothing
 If blnEXCEL = True Then xlx.Quit
 Set xlx = Nothing
 
 End Function

 

[Gasman]

Test for the duplicate value in destination table.

If not found Add record, if found, skip to next row in Excel

 

[marlan]

Hi,

I would use DoCmd.TransferSpreadsheet (https://msdn.microsoft.com/en-us/library/office/aa220766(v=office.11).aspx) to link to Excel data.

This linked Excel data can be referred to as a table, and be part of a SQL query.
Queries do not error on duplicates, these are just not inserted.

All the best!

 

[marlan]

Here is an example in code:

Sub a()
    Dim rst As DAO.Recordset
    Dim XlsRange As String
    
    'Link to Excel:
    Set rst = CurrentDb.OpenRecordset("Employees", dbOpenDynaset, dbAppendOnly)
    XlsRange = "A2:A" & CStr(rst.Fields.Count - 1) 'There is a way to add Sheet name too.
    DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, "EmployeesXls", "DB\Employees.xls", False, XlsRange
    
    'Execute SQL in hard code:
    CurrentDb.Execute "INSERT INTO Employees (...)" & _
        "SELECT * FROM EmployeesXls"
    
End Sub

If the problem is index duplicates, something like this should work.

Good luck!

 

[Solo712]

Hi guys

I have code written which imports excel data to a access table but after the first import it fails due to duplicates, how can i tell it to ignore duplicates in the table and only copy new records?

thanks

code is below.

Do While xlc.value <> ""
       rst.AddNew
             For lngColumn = 0 To rst.Fields.Count - 1
                    rst.Fields(lngColumn).value = xlc.Offset(0, lngColumn).value
             Next lngColumn
       rst.Update
       Set xlc = xlc.Offset(1, 0)
 Loop

Assuming the unique value is called "ID" and is held in the 'xlc' I would change the above section of code to

 Do While xlc.value <> ""
    If DCount("*", "Employees,"ID=" & xlc.value) > 0 Then 
       rst.Edit 
    Else
       rst.AddNew
    Endif
    For lngColumn = 0 To rst.Fields.Count - 1
        rst.Fields(lngColumn).value = xlc.Offset(0, lngColumn).value
    Next lngColumn
    rst.Update
    Set xlc = xlc.Offset(1, 0)
Loop

This technique allows you to add new records and edit the existing ones. But if you just want to ignore records already in the table then you can do this:

Do While xlc.value <> ""
If DCount("*", "Employees,"ID=" & xlc.value) = 0 Then 
    rst.AddNew
    For lngColumn = 0 To rst.Fields.Count - 1  
        rst.Fields(lngColumn).value = xlc.Offset(0, lngColumn).value
    Next lngColumn
    rst.Update
End If
Set xlc = xlc.Offset(1, 0)
Loop

Best,
Jiri

 

[Ifshaanm]

Thanks guys, getting an error on the dcount.

 If DCount("*", "Employees, "ID=" & xlc.value) > 0 Then

expected list separator or )

on ID

 

[Solo712]

Thanks guys, getting an error on the dcount.

 If DCount("*", "Employees[COLOR=red]"[/COLOR], "ID=" & xlc.value) > 0 Then

expected list separator or )

on ID

There is a missing quote after Employees. My bad!

Best,
Jiri