Results 1 to 4 of 4

Syncronize/Updated data from ODBC MySQL Connection to MS Access Tables Specific Fields

  1. #1
    a4ron is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    2

    Syncronize/Updated data from ODBC MySQL Connection to MS Access Tables Specific Fields

    Hi,

    I have used a connector driver and attached to a MySQL database with product data - product code, size, length etc. used in a website.



    I also have another local database within MS Access which is used to update a product design program that has other columns fields - (ID, Etc.) but also contains the size and lengths of the products. I need the local DB (MS Access) to be updated/syncronized with changes from columns (length, size) that will have been altered on the MySQL database (as the master), there will also be new products added to the MySQL that needs to download to the local MS Access DB.

    Is there an easy way of doing this in MS Access, I have tried various ways but cannot seem to get this to work? (admittedly a bit of a novice in MS Access)

    Evey time I create a relaionship with the two product code fields I get a on-to-one relationship, but I guess I will need to link each filed I need to update (length, size etc.) and enforce referential integrity, But this is always unavailable?

  2. #2
    Ajax is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,078
    you can't enforce referential integrity between tables in different db's.

    Looks like you will need two queries, one to do the updates and one to do the inserts for new products

    However I would look at your db design - why not just link to the MySQL table and use this linked table to provide the values you want?

  3. #3
    a4ron is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    2
    Thanks,

    How would I go about doing this "link to the MySQL table and use this linked table to provide the values you want"?
    Is this simple enough to do?

  4. #4
    Ajax is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,078
    go to the external data tab and select ODBC database, then follow the prompts

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

Similar Threads

  1. How to make a persistent ODBC connection MySQL to Access
    By Yann63 in forum Import/Export Data
    Replies: 4
    Last Post: 01-24-2014, 09:44 AM
  2. Odbc connection mysql 5.1
    By PATRICK in forum Programming
    Replies: 0
    Last Post: 03-07-2013, 05:53 PM
  3. Replies: 3
    Last Post: 01-06-2012, 04:17 PM
  4. Replies: 5
    Last Post: 10-25-2011, 08:01 AM
  5. Access data page connection to MySQL
    By dkperez in forum Access
    Replies: 1
    Last Post: 09-29-2009, 06:02 AM

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
  •  
Tech Forums: Microsoft Office Forums