Results 1 to 6 of 6
  1. #1
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185

    VBA (or other method) Code To Both Add and Change a "LastModified" Date Field

    Hi All

    I have an existing database that I am working on, currently I have a bandaid solution while I work on a complete rewrite for Azure.

    Currently upon opening it simply pulls all table data from Azure SQL into the local tables (to increase performance).



    There will be 1 maybe 2 admin users who actually update the data, all other users just "look" at the data.

    To save upload/download time, I'm thinking of adding a "LastModified" date/time field to the tables, my question is how to add the date / time in VBA as below:

    1. Add date/time when a new record is added to local table "LastModified" field
    2. Change a date/time when a record is edited (the changes are done via VBA code, and very rarely via a datasheet view form)

    Second part would be code that can run at database opening that:

    1. Compares all local tables records with Azure SQL tables records (would be identical) and only downloads to the local tables records that have different "LastModified" date/times to what is already in the local tables
    2. Code that only downloads from Azure SQL tables the new records that are not already in the local tables (again the tables are identical).

    3. The opposite of the above 1/2, only upload from local tables to Azure SQL tables the changed and new records (based on "LastModified" date/time). Run this code on upload command (handled by VBA).


    Any ideas, hopefully this is a rather easy code as I'm sure something like this is required all the time.

    Cheers

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Point three has a problem in it's theory; What happens if two different people update it on their local FE tables?

    Which one is actually correct? You can't guarantee it simply by the date and time they updated that particular record.

    The other bits are relatively simple to achieve using < or > date comparisons and joined ID Fields between local and server tables.
    Be warned that joining local and server tables can get slow if there are lots of records.
    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
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    this is an issue much the same as with the old access replica functionality (deprecated in 2007).

    you actually need two datetime fields, one to record the date when the azure db 'master' was last updated and the other to record when the user updated their local copy - use the now function to include date and time. Suggest call them MasterDT and UserDT

    you then need a routine which compares both sets of dates. I'm in the middle of something else right now so don't have the time to provide the complete solution and it may be more than you require, but should point you in the right direction

    this link may help http://dfenton.com/DFA/Replication/F...eplication.pdf

  4. #4
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Thanks I'll have a look.

    I dont think 3. will be a problem as only 1 user will be admin and actually do these changes, so there should never be 2 or more people updating at the same time.

    Also when access FE loads it pulls complete data from Azure (hoping to reduce this down to just new data), and when the admin users does an update/new data push it goes straight into local tables and then straight into Azure when they do it.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This looks so familiar. Same workplace?
    https://www.accessforums.net/showthread.php?t=83399
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Nope thats not me haha, I guess kinda similar I'm looking through it now.

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

Similar Threads

  1. Replies: 3
    Last Post: 10-23-2020, 07:13 AM
  2. Replies: 4
    Last Post: 11-14-2019, 11:30 AM
  3. Replies: 32
    Last Post: 03-05-2019, 05:53 PM
  4. Replies: 6
    Last Post: 11-21-2015, 09:52 AM
  5. Replies: 2
    Last Post: 09-29-2012, 11:22 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