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.
