MySQL Forums
Forum List  »  Microsoft Access

Posted by: Martin King-Turner
Date: May 25, 2005 08:24AM

I have seen the same problem when migrating the MS Access Northwind (NWind.mdb) sample database to MySQL. The problem occurs with the Orders form, which uses exactly the same approach as you describe. I've figured out the problem, but not (yet) the solution. Here's what I've found so far by inspecting the MySQL general query log:

The problem occurs when one or more of the fields in the main form contain a NULL value. The data from the main form is inserted correctly into the database, including the NULL values. Next, a select query is issued to retrieve the auto_increment value for row just inserted. This query uses a syntax similar to SELECT id (or whatever the auto_increment field is called) FROM table WHERE ..... In the WHERE clause, the query contains all the fields from the main form and the associated values. The problem comes in the syntax of the WHERE clause - it reads WHERE field1=value1 AND field2=value2 AND field3=NULL AND field4=value4, etc. The error is with the NULL syntax - it should be field3 IS NULL, rather than field3=NULL. Running the query with field3=NULL *always* returns no rows (see Since the query returns no rows, Access shows #Deleted in the form.

There are a couple of ways to verify that this is the problem:-
1) Fill in *all* the fields with data on the main form in Access and verify that it works OK (it should), and
2) Switch on the general query log in MySQL and inspect the SQL statements.

Now the issue - where does the bug lie? Can anyone tell me whether this is a MyODBC bug (I'm using 4.51.11-2 on Win XP SP2), or is it an MS Access bug (I'm using Office XP)? If I knew where the bug lay, I'd know where to report it....


Options: ReplyQuote

Written By
April 02, 2005 09:10AM
April 03, 2005 08:37AM
April 12, 2005 08:39AM
May 25, 2005 08:24AM
May 26, 2005 07:17PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.