Import file and updated/add new records to table

[AccessNovice123]

I am looking for VBA to do the following things for btn_click () on my form in my Access 2016 database:

1. Import my excel file (import.xlsx) into an existing table (tblCustomers) where Cust_ID is the common field in the import file and table. This import will occur monthly so the table needs to be an accumulation of records. Would a temp table be a better practice?
2. Add new records from import file to tblCustomers
3. Update existing records from import file to tblCustomers where data from tblCustomers is different from the Import file for the same Cust_ID (i.e. last name changed due to marriage)
4. Populate "Modified" date field in tblCustomers with date the import was run.

I am sure something like CurrentDb.Execute method could works as well but I was unclear of the difference between DoCmd.OpenQuery Any help would be appreciated, thank you.
I have the following code but it does not Update the existing records, it just adds them as new records.

Private Sub btnUpdateAppend_Click() Dim filepath As String Dim User As String  User = Environ("username") filepath = "C:\Users\" & User & "\Desktop\import.xlsx" If FileExist(filepath) Then DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblCustomers", filepath, True  If IsNull(DLookup("[Cust_ID]", "NewData")) Then MsgBox "No new data to import!"  Else DoCmd.OpenQuery "qryAppend2", acViewNormal MsgBox "New data imported!" End If  If IsNull(DLookup("[Cust_ID]", "NewData1")) Then MsgBox "No new data to update!"  Else DoCmd.OpenQuery "qryUPDATE", acViewNormal MsgBox "Data updated!" End If  Else MsgBox "File not found. Please check filename or file location." End If  End Sub

 

[Ranman256]

i have a generic xl file , File2Import.xls.
This is linked in as a table
Every new file i get, I do a SaveAs c:\temp\File2Import.xls.
then run the macro:
Q1 runs an append query, to import New items. (using outer join query since they dont exist)
Q2 runs an update query. (to update ALL items in the xl file to the data table) since new items now exist.

 

[arnelgp]

here is a sample update from excel file.

Private Sub UpdateCustomerFromExcel(strFileToImport As String)
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' NOTE:
    '
    ' FileToImport  = the path and filename + extension of your excel file.
    ' sample:         "d:\files\import.xlsx"
    '
    ' The import will look at Sheet1 of your worksheet to import.
    '
    ' Another important note:
    '
    ' You should make Cust_ID as Primary Key or if there is already one,
    ' create an Index on this field with No Duplicate.
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim strSQLUpdate As String
    strSQLUpdate = "Update Import_Macro As A Outer Join " & _
                        "[Excel 12.0 Xml;IMEX=2;HDR=YES;ACCDB=YES;Database=" & _
                        strFileToImport & "].[Sheet1$] As B " & _
                        "On A.Cust_ID=B.Cust_ID " & _
                        "Set " & _
                        "A.Ident_Num=B.Ident_Num, " & _
                        "A.LNames=B.LNames," & _
                        "A.FName=B.FName," & _
                        "A.Modified=Date;"
                        
End Sub

 

[arnelgp]

ooops, there is a revision

Public Sub UpdateCustomerFromExcel(strFileToImport As String)
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' NOTE:
    '
    ' FileToImport  = the path and filename + extension of your excel file.
    ' sample:         "d:\files\import.xlsx"
    '
    ' The import will look at Sheet1 of your worksheet to import.
    '
    ' Another important note:
    '
    ' You should make Cust_ID as Primary Key or if there is already one,
    ' create an Index on this field with No Duplicate.
    '
    ' On both your Table and Excel file, Cust_ID must be of SAME TYPE.
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim strSQLUpdate As String
    strSQLUpdate = "Update Import_Macro As A Right Join " & _
                        "[Excel 12.0 Xml;IMEX=2;HDR=YES;ACCDB=YES;Database=" & _
                        strFileToImport & "].[Sheet1$] As B " & _
                        "On A.Cust_ID=B.Cust_ID " & _
                        "Set " & _
                        "A.Cust_ID=B.Cust_ID," & _
                        "A.Ident_Num=B.Ident_Num," & _
                        "A.LNames=B.LNames," & _
                        "A.FName=B.FName," & _
                        "A.Modified=Now();"
    CurrentDb.Execute strSQLUpdate
                        
End Sub

 

[arnelgp]

ooops, there is a revision

Public Sub UpdateCustomerFromExcel(strFileToImport As String)
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' NOTE:
    '
    ' FileToImport  = the path and filename + extension of your excel file.
    ' sample:         "d:\files\import.xlsx"
    '
    ' The import will look at Sheet1 of your worksheet to import.
    '
    ' Another important note:
    '
    ' You should make Cust_ID as Primary Key or if there is already one,
    ' create an Index on this field with No Duplicate.
    '
    ' On both your Table and Excel file, Cust_ID must be of SAME TYPE.
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim strSQLUpdate As String
    strSQLUpdate = "Update Import_Macro As A Right Join " & _
                        "[Excel 12.0 Xml;IMEX=2;HDR=YES;ACCDB=YES;Database=" & _
                        strFileToImport & "].[Sheet1$] As B " & _
                        "On A.Cust_ID=B.Cust_ID " & _
                        "Set " & _
                        "A.Cust_ID=B.Cust_ID," & _
                        "A.Ident_Num=B.Ident_Num," & _
                        "A.LNames=B.LNames," & _
                        "A.FName=B.FName," & _
                        "A.Modified=Now();"
    CurrentDb.Execute strSQLUpdate
                        
End Sub

 

[AccessNovice123]

Thank you for your response.

I get a compile error: syntax error.

Public Sub UpdateCustomerFromExcel(strFileToImport As String) Dim strSQLUpdate As String     strSQLUpdate = "Update Import_Macro As A Right Join " & _                         "[Excel 12.0 Xml;IMEX=2;HDR=YES;ACCDB=YES;Database=" & _                         strC:\Users\Documents\import.xlsx & "].[Sheet1$] As B " & _                         "On A.Cust_ID=B.Cust_ID " & _                         "Set " & _                         "A.Cust_ID=B.Cust_ID," & _                         "A.Ident_Num=B.Ident_Num," & _                         "A.LNames=B.LNames," & _                         "A.FName=B.FName," & _                         "A.Modified=Now();"     CurrentDb.Execute strSQLUpdate                          End Sub

Am I missing quotations somewhere? Do I need to reference the actual table name, tblCustomers instead of "A"?

Thanks for your help.

 

[AccessNovice123]

Ranman256,
Thank you. I was able to get this to work with a macro and below is the macro converted to VBA. But this is how I set it up:
1. ImportExportSpreadsheet (imports records to temp table for update/append)
2. SetWarnings: No
3. OpenQuery: Update Query (updates values from temp table to main table)
4. OpenQuery: Append Query (appends values from temp table to main table)
5. OpenQuery: Delete Query (deletes values from temp table)
6. Stop Macro
7. SetWarnings: Yes

Converted VBA :

Function Copy_Of_mcr_Update()
On Error GoTo Copy_Of_mcr_Update_Err

    DoCmd.TransferSpreadsheet acImport, 10, "Import_tmp", "C:\Users\Desktop\Import_v2.xls", True, ""
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryUPDATE", acViewNormal, acEdit
    DoCmd.OpenQuery "qryAppend3", acViewNormal, acEdit
    DoCmd.OpenQuery "qryDelete", acViewNormal, acEdit
    Exit Function
    DoCmd.SetWarnings True


Copy_Of_mcr_Update_Exit:
    Exit Function

Copy_Of_mcr_Update_Err:
    MsgBox Error$
    Resume Copy_Of_mcr_Update_Exit

End Function

 

[arnelgp]

I see you got the solution. Anyway you call the sub:

UpdateCustomerFromExcel "nameofyourtable"