Results 1 to 6 of 6
  1. #1
    QuakerInOH is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    3

    Access DB linked to "live" SQL DB - Best Practice for Tracking Changes?

    Good morning Data Junkies,



    I have an Access (2016) DB getting data from a connection to a SQL DB (coming out of our ERP). This DB is constantly receiving updates from many thousands of records, relating to jobs, operations and various manufacturing data fields.

    My challenge is to identify which changes to individual records are driving overall trends or changes to sub-totals. So far, the only effective method (effective, but also very tedious) I have come up with is to manually save an excel version of the data and utilize basic formulas to compare 'snapshots' of the data. Is there a best practice for tracking the changes to the data coming into my Access DB in order to handle this task in a more automated manner?

    Thanks in advance for your ideas!

    Regards,
    Quaker

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    my table that gets the import, has a CreateDate field (date), default = now()
    when I import data, the timestamp records it.

    Is this what you need?

  3. #3
    QuakerInOH is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    3
    Ranman,
    Thanks for your suggestion. You stated that you have a CreateDate field, but are you currently using it in any of your analysis? If so, I'd be interested to know how you have made use of it.

    I am not currently getting a field like that from our ERP system, but I am going to try to create one internally within my Access DB so that I can track when changes are pushed from the SQL table. That should help with querying for changes within a certain timespan.

    Thanks!
    Quaker

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Do you need to track the actual changes, or can you just display the new values? You can have refresh buttons on forms which will requery the data, or you can put an OnTimer event in the main form which would refresh all open forms.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    This DB is constantly receiving updates from many thousands of records
    when you say receives, do you mean you import the data, or are simply linked to it?

    If importing then you should run a number of queries to create a history file. This typically requires your destination table to have date fields for created/updated and deleted. Making use of left joins in your queries and assuming you have a uniqueID in the ERP data you can use

    Query1. Append all new records from ERP where there is not a match on the uniqueID in your destination table. populate the created/updated field with now()
    Query2. Update records in your destination table that are not in your ERP data - these have been deleted, so update the deleted field with now()
    Query3. Append records in both ERP and destination tables which match on the uniqueID and there has been a change in the underlying data.populate the created/updated field with now()

    Depending on data volumes and performance considerations you may then want a 'current view' table which can be extracted from your history table.

    The current view would be the record for any given uniqueID with the latest created/updated value and deleted value is null

    An earlier view would be the record for any given uniqueID with the latest created/updated value prior to the date required and deleted value is null or greater than the date required

  6. #6
    QuakerInOH is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    3
    Ajax,
    The delay in response to your post is due to the conversations it has led to between myself and our lead developer, who is designing the SQL Database from which my DB will be getting updates. I have been working with "flat" files for too long, and needed to be better educated on how a properly designed relational database works.

    Whereas I had been expecting to create relationships with a table in my DB linked to the external SQL DB (as one might do with an Excel workbook), I now understand that I need to load all the data into my Access tables and get updates on any changes from the external DB. We will be incorporating your suggestions for creating a history file into the SQL DB being populated from our legacy ERP.

    Thank you for your thorough response and for giving me the benefit of your experience!

    Best Regards,
    Quaker

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

Similar Threads

  1. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  2. Ways to display a "Live" job list
    By stildawn in forum Access
    Replies: 10
    Last Post: 10-19-2013, 03:47 PM
  3. Replies: 2
    Last Post: 11-14-2012, 04:47 PM
  4. Replies: 1
    Last Post: 04-25-2011, 12:36 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