MySQL Forums
Forum List  »  Microsoft Access

Re: MySQL and 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 http://dev.mysql.com/doc/mysql/en/problems-with-null.html). 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....

Martin

Options: ReplyQuote


Subject
Views
Written By
Posted
3191
April 02, 2005 09:10AM
2176
April 03, 2005 08:37AM
2147
April 12, 2005 08:39AM
Re: MySQL and ACCESS
2031
May 25, 2005 08:24AM
1807
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.