ODBC connection problem

[jewels]

I have been happily linking to a MySQL database via ODBC for a while but all of a sudden, Access 2003 is crashing whenever I try to see the actual MySQL database. No message given. It just gives the Microsoft Error window and closes down the MDB. ODBC tests succesfull. Any ideas?

 

[KenHigg]

Hum...

Does it do this on all/any machine(s), or just one?

 

[jewels]

I'm trying to do it on the server itself.

 

[KenHigg]

Have you tried compacting/repairing the .mdb?

 

[jewels]

Yes, I have. I see that yesterday someone posted a similar problem.

 

[KenHigg]

Can you successfully do a new, test .mdb and connect to the table?

 

[jewels]

Nope, does the same thing.

 

[jacksonpt]

I recently upgraded from office 2000 to office 2003 and had the same problem. I switched back to 2000 and am having a different problem (see my other thread for details about that). This is purely a guess, but I suspect that Microsoft released a patch/update that somehow messed with the ODBC stuff, so now we are kind of screwed.

I have a couple of ideas for work-arounds, but neither of them are all that great, nor easy/efficient.

 

[KenHigg]

Have you looked to see if MySql has any new odbc drivers?

 

[jacksonpt]

Can you successfully do a new, test .mdb and connect to the table?

I can't. None of my ODBC connections are working.

 

[KenHigg]

I wonder if you do a non-unicode (Access 2000) .mdb and get it to work?

 

[jacksonpt]

Good news folks... I found what looks to be a solution. I've only tried it once, but my ODBC connection is again working.

This is on a PC running Windows 2000 Pro and Office 2000 Pro. I suspected that a windows update might have goofed things up, so I uninstalled the "Update Rollup 1 for Windows 2000 SP4" update. I can now connect to my MySQL DBs through MS Access via ODBC.

Not sure how this affects you folks running other OSs, but it's a foot in the door.

 

[jewels]

Just found this on the MySQL site:

Posted by John Bissell on July 2 2005 4:09am [Delete] [Edit]

A note for Windows 2000 users (Office XP Developer,Access XP, odbc-connector 3.51.11-2) - after installing the "Update Rollup 1 for Windows 2000 Service Pack 4 (KB891861)" every attempt to update or add linked odbc tables crashes Access (using the existing data link seemed fine). They updated msjet40.dll (7/19/2004), and it seems to not play well with the odbc program. Replacing the dll with the previous version (3/1/2004) fixed the problem.

Answers some questions!!

 

[jacksonpt]

Just found this on the MySQL site:

Posted by John Bissell on July 2 2005 4:09am [Delete] [Edit]

A note for Windows 2000 users (Office XP Developer,Access XP, odbc-connector 3.51.11-2) - after installing the "Update Rollup 1 for Windows 2000 Service Pack 4 (KB891861)" every attempt to update or add linked odbc tables crashes Access (using the existing data link seemed fine). They updated msjet40.dll (7/19/2004), and it seems to not play well with the odbc program. Replacing the dll with the previous version (3/1/2004) fixed the problem.

Answers some questions!!

Cool - good find, not sure how I missed that. I'll have to reinstall the update from microsoft, then patch the patch with the old .dll - thanks.

 

[jewels]

Just one last point of interest: when replacing the msjet40.dll file, remember to first update the version hidden in the protected dllcache directory otherwise the replacement version continues to be overwritten.