Bug #25854 Weird errors with Access 2000 linking to Mysql (myodbc)
Submitted: 25 Jan 2007 18:09 Modified: 6 Mar 2007 8:48
Reporter: Stefan Bertels Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.12 OS:Microsoft Windows (Win 2000 SP4)
Assigned to: CPU Architecture:Any
Tags: Access 2000, error 3011, MyODBC, Win 2000

[25 Jan 2007 18:09] Stefan Bertels
Description:
Scenario (sorry for long text, but very weird problem):

Win 2000 SP4 machine with dual-core pentium and 1GB RAM

I guess the problem does not depend on MySQL Server 5.0/4.1 I tried to install on the same machine. It might be MyODBC.

I have an Access 2000 database which has a bunch of linked tables, one of them named "tabledefinitions". When I launch the MDB a form opens and some code runs. This code uses the "tabledefinitions" table on some mysql server (linked using MyODBC) to get some table names. This works. Then the code tries to re-create links to all found table names. This does not work. I get very strange results.

I tried multiple MyODBC versions (3.51.11-2, 3.51.12, 5.0.10 beta) but non of them worked. In some cases I could not create those links manually:
- I got weird error messages or
- I got no error messages at all but got no "table selection window" either

In some other cases (tried a lot with different MyODBC / ODBC DSNs etc.) I could create those links manually but the code could not! In this case i got an 3011 error saying the table XYZ could not be found (table exists). Trying some more ways I could occasionally get the code to link one (1) table but no more. I many cases I got no error messages at all. I tried many combinations of MyODBC, server version, recreated DSN multiple times, even removed DSNs manually in registry.

Another fance thing: When I shut down Access the CPU load increased (full load on one CPU core). The GUI disappeared but MSACCESS.EXE still run. I think this is due to some "open connection" or "open resource" which cannot be closed running in some loop.

The function in code I use to link the tables is:

docmd.TransferDatabase acLink, "ODBC", "ODBC;DSN=mydsn;uid=user;pwd=pass", acTable, "TABNAME", "TABNAME", false, true

(I know this method might be outdated Access/DAO technique.)

I have some NT machines which use MySQL 4.0.20a (Win32) and MyODBC 3.51.09 where this works fine. It did not work using these versions on the Win2000 machine (MySQl being on another NT machine), either.

I had different connection problems using non-root users, too. I'm quite sure the reason is this combination of Win2000 SP4, Access2000 SP3 (tried SP1a), MyODBC.

Of course I tried to update the code using some newer ADO link mechanism. But I could not get this to work (until now).

Another strange issue: Sometimes on including all those tables by code I did not get a positive result (all tables missing) but I had some tables (maybe only one (1) link was new) that had some "TABNAME1" name (additional "1" as suffix, Access seems to add this if the original name already exists). But "TABNAME" could not be seen (system and hidden objects enabled).

If I could see links I ALWAYS could use them -- regardless of the way they were created or the name they have (TABNAME1). The main problem seems to get the links programmatically.

I'm quite sure it is no security thing because the setup is easy (just one database, root and two more users) and I could setup the new server looking on the old one.

How to repeat:
1. Win 2000 SP4
2. Access 2000 SP1a or SP3
3. MyODBC 3.51.12 (e.g.)
4. MySQL-Server (4.0, 4.1 or 5.0)
5. create ODBC DSN to some database on server:
   DSN=mydsn
   ip=<mysql server ip>
   database=test
   user=some
   pass=     (I let this empty most times, but tried with correct pw, too)
   option=3  (if available, MyODBC seems to have no options)
6. Create Access database with form having some code
7. include _multiple_ statements like
docmd.TransferDatabase acLink, "ODBC", "ODBC;DSN=mydsn;uid=user;pwd=pass", acTable, "TABNAME", "TABNAME", false, true

Suggested fix:
Maybe some very weird myODBC bug.
[5 Feb 2007 12:32] Bogdan Degtyariov
Please check the version of msjet40.dll file. In case it is 4.0.9025.0 you need to replace it by some older version. MS Update should create backup of older versions, so older version of msjet40.dll is somewhere in WINDOWS directory.
[5 Feb 2007 13:09] Tonci Grgin
Stefan, can you please post your JET version as well as replace it with older one as Bogdan asked. Please, post your findings.
[6 Mar 2007 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[6 Mar 2007 8:48] Stefan Bertels
Yes, msjet40.dll was 4.0.9025.0

This seems to be the version supplied with W2K SP4 and MDAC update will not install another version itself. I manually changed the dll. I can't really say what the new version is, AFAIR it was something 4.0.8*.* -- I extracted it out of MDAC 2.8 (SP1?)

Seems to work now. Thanks for the hint. Maybe this should be included in MyODBC installer as warning!
[26 Jul 2007 16:08] MC Brown
I've added an FAQ about this issue to the Errors and Resolutions section.