1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

ACCESS 2003 and MySQL - problems with linked tables

Discussion in 'MySQL' started by Efrat M, Apr 27, 2019.

0
  1. #1
    Hi,

    I have an application developed via MS-ACCESS 2003.

    I migrated the data (ACCESS tables) to a MySQL 5.7.25.

    Then, I linked the MySQL tables to the ACCESS.

    Most of data works correctly (viewing the tables content and accessing the data via the Forms).

    I have a problem with 2 table:

    1. Table contains list of activities (5 fields per record): up to 210 records, all works fine. After adding record no. 211, there is a problem:
    when opening the table in ACCESS in view mode, I get the "ODBC - call failed" message, and only 86 records are displayed, where all fields have "#Name?" value.

    But, when accessing the table data from the Forms, the data is OK.


    2. Table contains list of people details (name, address, etc.): In case of few records in this table, all works fine. But when using the full data (197 records), there is a problem:
    In view mode in ACCESS, I can see all data correctly. But I cannot access the data from my Forms. The table and fields are not recognized in the code.

    Hope you can help me with these issues. THANKS!
     
    Efrat M, Apr 27, 2019 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #2
    Do you need to maintain the Access database or can you export to Mysql and cut the ties?
     
    sarahk, Apr 27, 2019 IP
  3. Efrat M

    Efrat M Peon

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3
    Sorry, I do not understood your answer. In other words:

    I have a database stored in MYSQL.

    This DB is linked to a MS-ACCESS application that is located in my personal computer. I want to access this DB from the ACCESS application.

    But in case of tables with more than about 200 records, I get the problems described.
     
    Efrat M, Apr 27, 2019 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #4
    What is the business problem that you are trying to solve. I'm pretty sure that there are better tools than Access.
    Is it that you don't always have an internet connection and you need to sync an offline and online system?

    Can you write a program that exports the MySQL database in an access friendly (ie csv) format and then write an script in access that imports it?
     
    sarahk, Apr 28, 2019 IP
  5. Efrat M

    Efrat M Peon

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #5
    I do not have Internet problems,
    I do not have Import problem. I tried to import in several ways, and the result is the same.
    If the linked table contains 100 records - the ACCESS works perfect. When adding record no. 101 (directly via table view, or using the INSERT SQL commnad) - ACCESS can not connect correctly to the linked table.
    I guess something is wrong in the ODBC or MYSQL definitions.
     
    Efrat M, Apr 28, 2019 IP
  6. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #6
    Hey, didn't mean to offend. Just trying to understand why you're using 16yo software.
    Have you checked your code for bugs?
    You haven't got a limit in there somewhere from when you were testing?
    If you create a test table in MySQL filled with random names are you able to get beyond row 101?
     
    sarahk, Apr 28, 2019 IP
  7. Efrat M

    Efrat M Peon

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #7

    I understand and appreciate very much your willing to help.

    This ACCESS application was written long time ago, that's the reason for using it.

    There are no bugs, since until now, all tables were located in the ACCESS and all worked fine.

    Now I want that the data will be common for few users, through the internet.

    The problems started after export the data to MYSQL and linking the remote tables to ACCESS.

    And, yes - I had created test table in MYSQL and the same problem occurred.

    THANKS!
     
    Efrat M, Apr 28, 2019 IP
  8. Spoiltdiva

    Spoiltdiva Acclaimed Member

    Messages:
    7,739
    Likes Received:
    2,896
    Best Answers:
    53
    Trophy Points:
    520
    #8
    Hey, quit beating up our newbies! (just kidding-ha*ha!):rolleyes:
     
    Spoiltdiva, Apr 28, 2019 IP