Results 1 to 2 of 2
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

    Best way to import customer list to Access on a weekly basis

    I've got a DB where I have a table of customers and it only has three fields (customerID, customerFirstName, customer,LastName). I import in a new customer list every week in order to capture new customers. Right now, the code just deletes everything from the table and then appends the new list. Is this the best way to go about it? I ask because outside of new customers and name changes, the overwhelming majority of the data stays the same and idk if deleting it all every time is really necessary or efficient.

    I'm fairly new at Access so bear with me. Is there a way that on import, instead of deleting everything, it checks 1) if there's any new customers to add based on customerID & then adds them and 2) if there's any name changes to exisiting customers based on customer ID & then updates them? Below is my current code:

    Code:
    Private Sub bttnImportCustomerList_Click()
    
    Dim wdShell As Object
    
    
    On Error GoTo ImportIt_Err
        
        Application.FileDialog(msoFileDialogOpen).Title = "Please select the customer list for uploading."
        Application.FileDialog(msoFileDialogOpen).InitialFileName = "C:\Documents"
        Application.FileDialog(msoFileDialogOpen).Filters.Add "Excel Spreadsheets", "*.xlsx", 1
        Application.FileDialog(msoFileDialogOpen).FilterIndex = 1
        Application.FileDialog(msoFileDialogOpen).Show
        strFile_Path = Application.FileDialog(msoFileDialogOpen).SelectedItems.Item(1)
                
        DoCmd.SetWarnings (WarningsOff)
        DoCmd.OpenQuery "qryClear_CustomerList"
        DoCmd.RunSavedImportExport "Import-CustomerList"
    
    
        StrResponse = MsgBox("Process Complete!")
    
    
    ImportIt_Exit:
        Exit Sub
    
    
    ImportIt_Err:
        MsgBox Error$
        Resume ImportIt_Exit
    
    
    End Sub


  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IMO, no. If anything fails after the delete you can end up with nothing. To avoid that you have to wrap it all in a Transaction. Far easier to run an append and then an update query (or reverse that) or try what's known as an Upsert query.

    Just a quick glance but

    Code:
    With Application.FileDialog(msoFileDialogOpen)
       .Title = "Please select the customer list for uploading."
       .InitialFileName = "C:\Documents"
       . and so on
    
    End With
    Plus if you turn off warnings and don't set them back on, they remain off until db is reopened. Could be a big problem. Always turn back on after your exit label code and in conjunction with an error handler.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 02-28-2017, 10:06 PM
  2. Replies: 7
    Last Post: 08-06-2016, 07:05 PM
  3. Access - product list by customer margin
    By Evans2 in forum Access
    Replies: 1
    Last Post: 11-29-2015, 06:21 AM
  4. Replies: 3
    Last Post: 08-30-2012, 05:05 PM
  5. Excel Import/Append Data (weekly)
    By MartinL in forum Import/Export Data
    Replies: 1
    Last Post: 08-12-2010, 06:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums