Import file and updated/add new records to table (1 Viewer)

AccessNovice123

Registered User.
Local time
Today, 11:06
Joined
Aug 16, 2017
Messages
13
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.



Code:
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

Well-known member
Local time
Today, 14:06
Joined
Apr 9, 2015
Messages
4,339
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:06
Joined
May 7, 2009
Messages
19,169
here is a sample update from excel file.
Code:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:06
Joined
May 7, 2009
Messages
19,169
ooops, there is a revision
Code:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:06
Joined
May 7, 2009
Messages
19,169
ooops, there is a revision
Code:
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

Registered User.
Local time
Today, 11:06
Joined
Aug 16, 2017
Messages
13
Thank you for your response.

I get a compile error: syntax error.

Code:
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

Registered User.
Local time
Today, 11:06
Joined
Aug 16, 2017
Messages
13
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 :
Code:
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
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:06
Joined
May 7, 2009
Messages
19,169
I see you got the solution. Anyway you call the sub:

UpdateCustomerFromExcel "nameofyourtable"
 

Users who are viewing this thread

Top Bottom