Results 1 to 6 of 6
  1. #1
    tlovett1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    3

    Synchronizing a local with a linked table

    Here's the backstory:

    My client uses a MS Access database to keep track of their orders inside a table let's say is called "ms_orders". We are building them a web application that serves as a portal for customers to view their orders. The portal is built using PHP/MySQL. As such we need a way to read this MS Access database inside the web application.

    So far my best idea is to create a duplicate orders table inside MySQL, called "mysql_orders". Then in MS Access create a linked version of "mysql_orders". Finally, creating data macros inside MS Access such that when "ms_orders" is updated, inserted, deleted reflect those changes to "mysql_orders".

    Does this sound like the best solution? Does anyone else have any ideas?



    Thanks

  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
    Cross post https://stackoverflow.com/questions/...-automatically
    Although, no mention of web app.
    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
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    'orders' usually consist of 2 or more tables - order header, order lines for example.

    And to be clear the mySQL table is for reporting only, customers cannot do things like change the order, create new ones?

    Assuming updates are only one way and one record at a time, would think it better to use vba with ADO to connect to mySQL and execute a stored procedure to update the mySQL table. Depends on your provider and the server the mySQL sits on - many won't allow direct access to the db other than via web pages on the server.

    Alternative is if you provide an API that vba can call and SEND the data

  4. #4
    tlovett1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    3
    Right MySQL table is for reporting only. Customers can not change anything.

    Ajax or anyone else - would you be willing to provide some direct help to me? Happy to pay your hourly rate.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I could be interested - tried to PM you but you have set your profile to not receive private messages.

    If you PM me with your contact details, we can move on from there

  6. #6
    tlovett1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    3
    For some reason, I don't even see PM. Maybe because my account is new? Can you email me - tlovett88 [at] gmail.com

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

Similar Threads

  1. Replies: 4
    Last Post: 10-10-2019, 10:16 AM
  2. Replies: 6
    Last Post: 08-09-2018, 04:30 PM
  3. Replies: 3
    Last Post: 06-07-2018, 07:08 AM
  4. Replies: 3
    Last Post: 01-01-2015, 05:25 PM
  5. How to Create local copy of linked Table
    By behnam in forum Programming
    Replies: 3
    Last Post: 11-20-2014, 05:49 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