This isn't necessarily a "bug" as such, but may be to do with how MS Access
stores connection information. To me, their system seems illogical, though
it may conform to standards and so on I suppose.
If you look at the Connection String of any linked ODBC table (or
pass-through query) you'll see something like this:
(you can view this by typing into the Immediate Window of the visual basic
editor: print currentdb.TableDefs("Your_Table_Name").Connect )
Now what you find is that a lot of information is stored twice. The DSN
already has the host name (JASMINE), the port (3306, Access incorrectly has
0, though this may mean "use the default"), the database (zoe) and the
option flags (17451). So along with storing that the DSN to use is Zoe
(which holds all that information) Access also stores it directly.
What I'm wondering is if your DSN ahs the correct settings, but Access's
internal connection string has them incorrectly and still thinks
SERVER=localhost or similar. To change this, either relink the tables or
(which may be more likely to work) manually set the connection strings to
I wrote some code a little while back which dives into the registry to pull
out the DSN settings for a given DSN and then propagates them throughout the
linked tables and pass-through queries in the MDB - if you're interested I
can post it.
From: Onno Broekmans [mailto:onnodb@stripped]
Sent: 03 July 2006 14:25
Subject: MyODBC 3.51.12 automatically resetting connection parameters
Today, I installed MyODBC 3.51.12 on a Windows XP computer to access a
*remote* MySQL database through Microsoft Access 2002 (XP). Testing the
connection works perfectly.
However, when I try to access the database through Access, MyODBC pops up
a login form. On this login form (which is the same as the "Configure Data
Source Name" dialog) everything is grayed out, except the username and
password. The strange thing is, that on the login form, the "host"
parameter is reset to "localhost"! Pressing OK makes MyODBC fail to
connect --- which makes sense, since there's no MySQL installed on
Summarizing: it seems that MyODBC automatically resets "host" to
"localhost", instead of the original setting, upon Access trying to
initiate the connection.
Is this a bug? Is there a workaround?
Thanks in advance!