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

    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:

    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
        strFile_Path = Application.FileDialog(msoFileDialogOpen).SelectedItems.Item(1)
        DoCmd.SetWarnings (WarningsOff)
        DoCmd.OpenQuery "qryClear_CustomerList"
        DoCmd.RunSavedImportExport "Import-CustomerList"
        StrResponse = MsgBox("Process Complete!")
        Exit Sub
        MsgBox Error$
        Resume ImportIt_Exit
    End Sub

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Ontario, Canada
    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

    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.
    Grief o'er the loss of one loved is not a destination, but a journey.
    Oft content upon arrival are we, only to find there are miles yet to travel.


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