MySQL 5.1 and MS Access Insert on Linked Table Error

Posted on 2012-09-18
Last Modified: 2012-09-19
Say, The MS Access sql code was working fine when connecting to linux My SQL.
Moved DB to Windows 7 (latest My SQL server).
I can access data natively no problem and even add records from within Access. When I execute sql codes get 3155 ODBC --insert on a linked tabe failed...
Any ideas?
Question by:shaunwingin
    LVL 1

    Expert Comment

    Which MySQL ODBC driver are you using? The older driver (3.51) is more stable. I've had issues with the newer driver as well, and had to revert to the older one.
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    Many times a SQL table will require a TimeStamp field for Access to be able to update it. Also, if these are linked tables, be sure that the Primary Key has been properly represented in the linked table. You can open the link in Design view (Access will complain, but do it anyway), and then review the Indexes - is the Primary Key Index there? If not, delete the link and recreate it, and be sure that the indexes are represented correctly.

    Author Comment

    I'm using 5.1
    Traced issue to allow nul not being enabled on certain data fields in the table.
    Is there a setting to handle data which is blank but allow null is not checked?
    LVL 1

    Accepted Solution

    If the data is blank, you may have to pass an empty string or a space to ensure the db sees it as blank data rather than null. I've had to do that as well. And I've also dealt with the TimeStamp requirement as suggested by LSM; the inclusion of a TimeStamp field in the MySQL table has alleviated many record update headaches.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now