Results 1 to 7 of 7
  1. #1
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45

    How to decide whether to import or link Excel data to Access

    I'm creating a local version of a web based database.

    The data is contained in 50 Excel workbooks and is updated weekly, although I may decide to update less frequently than that.



    I'd be most grateful to know what questions I should consider when deciding whether to import the data or create links to it.

    Thanks

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Is the data in the workbooks essentially the same layout?
    Is it TableA for site A, TableA for site B type information? etc
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45
    No. Each table contains largely unique data, with keys to link relevant data together.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If the layout's are fixed, and you can easily identify new records / data from existing data, I would import the data to fixed tables within you database.
    Long term this is much easier to work with than relying on linked tables, particularly if you need to share the data from them.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Is each weekly cycle independent of the other previous work, or does it build on what was done previously (do you need to access previous data)?
    If independent, and the data layout is the same, meaning some columns and column names - it does not matter if there are more or less records (rows), then linking should work fine.
    (As long as the data is in a location where everyone who needs access to it can access it).

    If you were to go the way of importing, are you deleting the previous data from the database?
    If so, then you will definitely need to Compact and Repair the database regularly, or else its size will bloat and you will run out of room.
    Since you said you are dealing with 50 workbooks, I would also look to automating the imports as much as possible using Macros or VBA.

  6. #6
    sanal is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Feb 2018
    Posts
    43

    Post Automate import data from excel

    How to automate import data from 12 excel workbooks to 12 access table

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Hijacking a thread is poor forum etiquette and usually does not serve you well as threads with answers get less attention, especially old threads.

    Review http://www.accessmvp.com/KDSnell/EXCEL_Import.htm for ideas.

    Develop code and when you encounter specific issue, post a question.
    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.

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

Similar Threads

  1. Replies: 2
    Last Post: 01-12-2017, 10:35 AM
  2. Replies: 7
    Last Post: 04-16-2014, 07:07 AM
  3. Macro to mimic Import & Link Excel button on external data
    By arnstrb in forum Import/Export Data
    Replies: 1
    Last Post: 03-16-2014, 07:52 PM
  4. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  5. Replies: 2
    Last Post: 12-26-2012, 02:58 PM

Tags for this Thread

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