Auto_Increment Fields in Access/mySQL linked tables won't AutoNumber

[Michael Zore]

I have an Access 97 application I’m converting from MS SQL7 to mySQL, designed and implemented by a predecessor. The data converted over to mySQL well (with a little tweaking), but I'm having a problem with all of the auto increment fields.

When the Access tables are linked via ODBC to SQL7, the field appears as type “AutoNumber” – correct, given the identity column in the database. After converting to mySQL 4.0 and adding primary keys and auto_increment flags, the fields now appear as “Number” instead. I’m using mySQL ODBC Driver 3.51.09, SQL7, Access 97, and mySQL 4.0.17. I’ve confirmed the databases have correct auto_increment, primary key, and field settings.

I’m been fighting with this for about a day now, so I’ve even started to try a variety of “Hail Mary” options. I’ve updated to mySQL 4.1.3 beta (no improvement). I’ve gone back to a prior copy of the Access .MDB file with the old DSN links intact and built a new DSN with the same name (queries fail as the links refer to “dbo.tablename”, not “tablename”); tried renaming the database “dbo” with the old DSN (same results); added timestamps to all tables with autos (most had one already); tried changing the field types in Access (can’t store the changes for linked files); heck, I even tried sacrificing a rubber chicken – all to no avail.

This seems like an obvious operation, so I think I’m missing something obvious, but have no idea what. As the lack of AutoNumber breaks a large chunk of the software (and my job is not to rewrite or port the software at this point, if possible, just move the data of the old NT server and SQL7), I’m looking for a way to make it recognize the Auto fields correctly.

Suggestions, anyone?