Results 1 to 6 of 6
  1. #1
    Sanguineus is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    19

    Checking Data Exists on Import

    Good Morning All,

    Is there a *quick* way of checking if data exists in a table during an import process?

    We have data that is collected manually in a spread sheet that I would like house in a table, currently it is something in the region of 50K records in size, periodically older entries will be changed so the corresponding entry in the table will need to be amended to.

    The excel table has 5 fields, the first 4 are categorisations and the final field a count, the access table has the same fields. I've tried a variety of methods to search for the matching entries in the table (loading the table into an array, a sql select query matching on the fields values, and dlookups but all seem quite slow, taking an hour or so to run through.

    In essence I need something that will take 5 pieces of data from the excel sheet (Date, Catergory1, Catergory2, Catergory3, Catergory4, Tally), match this against the entries in the table; if all match skip to the next entry, if all match but the tally is different, update the tally field in the table with the new value, and finally if date, carergory1, catergory2, catergory3, catergory4 cannot be found add a new entry.

    If anyone can point me in the right direction I'd be very grateful.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Try setting those 5 fields as a unique compound index. Now import records. The index will not allow duplicates. How slow does this run?

    Can you set link to the Excel?

    Review https://stackoverflow.com/questions/...g-in-ms-access

    Correct spelling is: Category

    Do you really want that extra 'r'?
    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.

  3. #3
    Sanguineus is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    19
    Hi June,

    Thank you for your reply, firstly now the extra r was an error juggling several tasks at the same time and wasn't 100% concentrating on my spelling

    I should have mentioned and I'm sorry I didn't that we use a different import sheet for each month (not the best idea I know, but getting this changed will take some time, and I need the data before I am likely to achieve implementation of a better data gathering alternative), so I'm looping through several worksheets (April, May, June, July etc.) - As an aside I'm pretty new to access, pretty good with excel, VBA even a little SQL but just finding my feet with access - thank you so much for this.

    I think I'm seeing how this works I've created the unique compound index encompassing the input date and the categorisations.

    I then try and insert the next recordset, if it succeeds then there's nothing more to do, if it fails then we can be sure that everything apart from the count is already entered, at this point is it worth checking the count or just overwrite it regardless?

  4. #4
    Sanguineus is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    19
    Hi Again June,

    I've put together the bits you kindly pointed me in the direction of, I had to go away from the keyboard for a bit but came back and realised I was over thinking things quite considerably. I had to delete all data that previously existed in the table as there were already quite a few duplicates I was unaware off.

    The first run through is processing 150 records in 5 - 6 seconds, but these were all INSERTs.

    A quick bit of table top math suggests a single day will take 341 seconds (c.6 minutes) to import, and an entire month (worksheet) 10547 seconds (3hrs 10 seconds) and 12 months 37 hours or so... that's a tad too long if my math is correct.

    ::EDIT:: when its checking data already entered its much faster c.150 records a second - so I might just have to swallow the initial import time, until I'm caught back up with this years imports::

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Probably not worth checking the count, just overwrite.

    So this is as resolved as it can be?
    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.

  6. #6
    Sanguineus is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    19
    I think you maybe right, The only thing I could do to speed things up is to change the frequency where I allow a 'doevents', initially it was after 150 iteration, but I'm going to try and change this to 150 'Changes' (Inserts or updates) or 300 iterations without a change.

    I think its a matter of tinkering to find the right balance.

    thank you again for your help!

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

Similar Threads

  1. checking import values
    By Darla in forum Queries
    Replies: 5
    Last Post: 02-15-2018, 06:44 PM
  2. Replies: 1
    Last Post: 07-10-2012, 11:13 AM
  3. Checking beforehand whether a file exists
    By Ronald in forum Programming
    Replies: 2
    Last Post: 07-27-2011, 08:48 AM
  4. Checking if a year exists via DLookup
    By walter189 in forum Programming
    Replies: 6
    Last Post: 07-22-2011, 10:37 PM
  5. Checking if files exists
    By geoffishere in forum Access
    Replies: 1
    Last Post: 02-09-2010, 01:32 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