Results 1 to 4 of 4
  1. #1
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151

    ODBC Connection restricting ability to add Records

    I have a program that stores it's data in an MYSQL database. I created an ODBC connection using a Read-Only user to read the information from the Tables and linking them to my own Tables in Access to pass the information through to my forms and reports. This has been running well for the past few months since I created it.

    This week, they had this program used as a second instance which stored a second instance in the MYSQL database using a different port. I created a second ODBC connection to this instance to use in Access similar to the first. I copied my first Access database and in the second Access copy, I went to the ODBC Linked Manager and changed the tables to the second instance.

    When I open any of the Tables, I do see the data from the second instance. I can run queries on any tables in the new Access database and have the ability to add new records. However, In both Access databases, I have Queries which have Tables from this access database as well as fields from the linked Table from the Read-Only MYSQL database. However, in my first Access database, I am still able to add records to the Table through the Query. However in my second Access which is copied from the first in these queries, it will display the records but not allow me to add new records.



    I spent a good hour comparing both tables and queries and properties and can not see anything different in the second database from the first. If I remove this linked Table from any of the queries I am able to add records once again.

    Any one have any idea where I should look to resolve this.

    Thanks,

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Make sure the linked tables in 2nd MySQL instance have a primary Key field. When you link it might ask you to select the key field. Try to relink the table(s) again.

  3. #3
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    I checked and the Linked Tables do show Primary Key fields if I check in design mode

  4. #4
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    I think I found the problem. The field in the ODBC Database which I am linking to my Table in Access. In my original Database this field has the property in Indexed to no, whereas in the new database, this field has the property of indexed to Yes (No Duplicates). Therefore in my first Access when I link the Tables under relationships, the link is One-to-Many, whereas in the second one, it links as indeterminate.

    I can attempt to change the property of the new ODBC to no indexed from MYSQL, however, I am not sure if this will mess up the proprietary program for which this database is. Both instances are running the same proprietary program, however the second one which is giving me a problem is using a new version of the program where the databases were slightly changed.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-02-2015, 09:44 AM
  2. ODBC Connection
    By debelliotte in forum Access
    Replies: 3
    Last Post: 11-03-2014, 12:32 PM
  3. Replies: 1
    Last Post: 12-11-2013, 01:18 PM
  4. Replies: 5
    Last Post: 10-25-2011, 08:01 AM
  5. ODBC connection
    By avicenna in forum Import/Export Data
    Replies: 2
    Last Post: 08-02-2010, 02:33 AM

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