Results 1 to 9 of 9
  1. #1
    khughes46 is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Grapevine, TX
    Posts
    231

    Best Practices to Import Data and Update Tables


    I have several tables to be updated daily with external data. Some I can simply replace after a backup and a delete of the records. Others I have to only add new records. What is the best sequence of events to accomplish this? I am twisting my brain to the point where I think I am over-thinking this and keep changing my mind. Can someone please help me with my dilemma? I’m sure it’s a simple process; I just can’t seem to get a handle on it. Thanks.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Put it in macros.
    1st it adds new records. The table is keyed so there cant be dupes. (append qry)

    Then it adjusts existing recs via update qry.
    (maybe 1 table gets emptied), then filled in order to to calcs, then it too runs an update.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    All depends on the structure of the external data and the file type. Not enough info for specific advice.

    Might be able to import directly to table or maybe set link to external data then run UPDATE and/or INSERT SELECT actions.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    khughes46 is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Grapevine, TX
    Posts
    231
    Most of the external data is text, even the numbers. All tables have a primary key that ties back to one table. This primary key is a mix of alpha-numeric (i.e. s12-34-5678 and 123-45-6789). I am not familiar with INSERT SELECT.

  5. #5
    khughes46 is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Grapevine, TX
    Posts
    231
    So say I have 100 records in the current table. I import a file with 110 records and append to the current table; only 10 records will get added from the append? Slick, never thought of that. I was always appending to an empty table. Makes life easier. Thanks.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not exactly. The APPEND could add all 110 records. If the table has a field set to not allow duplicates (not an autonumber field) and this same field is in the new dataset, any records with a value that is already in the table will not be appended because they would cause duplicates. So then, yes, only 10 records would append.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    khughes46 is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Grapevine, TX
    Posts
    231
    I have no duplicates set. I tried to import/append and got subscript out of range. What causes this?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What method did you use? Show code for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    khughes46 is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Grapevine, TX
    Posts
    231
    I can't get it to repeat the error now. This isn't the first time I have gotten this error. When it comes again I'll open another thread; closing this one for now.

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

Similar Threads

  1. Replies: 7
    Last Post: 04-16-2014, 07:07 AM
  2. Replies: 1
    Last Post: 07-22-2013, 01:30 PM
  3. Replies: 9
    Last Post: 03-25-2013, 02:30 AM
  4. Replies: 4
    Last Post: 01-14-2013, 09:35 PM
  5. Import Excel Data to Multiple Access Tables
    By colby in forum Import/Export Data
    Replies: 3
    Last Post: 11-04-2011, 12:17 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