Problems connecting to ODBC

I have transferred the data from Access to MySQL using
MySQL's tool. The transfer worked great and saved me a
lot of time (thanks MySQL). Now I'm trying to get the
Access database, or a new access database, to talk across
ODBC and see the data.

I created the DSN through the control panel and it works
without error.

How do I setup Access so it can see the data. The data
was on windows (M$ Access) and is now on linux (Fedora
Core 5).

Followup: Seems like it connects, the 'Test' in the DSN
definition returns data. I turned on logging through
the DSN dialog and I see traffic between the DSN/application
(MS Access) and the MySQL server. Still, I do not see
anything usable returned from the MySQL server to Access.

Mike

Re: Problems connecting to ODBC

"Mike" wrote in message
news:6yfxg.708$Fy2.457@fe04.lga...
> I have transferred the data from Access to MySQL using
> MySQL's tool. The transfer worked great and saved me a
> lot of time (thanks MySQL). Now I'm trying to get the
> Access database, or a new access database, to talk across
> ODBC and see the data.
>
> I created the DSN through the control panel and it works
> without error.
>
> How do I setup Access so it can see the data. The data
> was on windows (M$ Access) and is now on linux (Fedora
> Core 5).
>
> Followup: Seems like it connects, the 'Test' in the DSN
> definition returns data. I turned on logging through
> the DSN dialog and I see traffic between the DSN/application
> (MS Access) and the MySQL server. Still, I do not see
> anything usable returned from the MySQL server to Access.

You do have the the MySQL ODBC driver installed on the Windows machine -
yes?

Investigate "linked tables" in Access. Linked tables can be pointers to
tables residing on a MySQL server but look and act as if they were ordinary
Access tables. You can program away using Microsoft tools in blissful
disregard of the fact that the data actually resides on a MySQL/Linux
server.

If you already have an ODBC data source defined - you can right click on the
[Tables] pane of Access and select the Linked table wizard to help you set
them up.

Also - check out pass through queries.

Thomas Bartkus

Re: Problems connecting to ODBC

Permissions also may play a role in this. I have a computer at wor
(Windows 2000) on which I am not an administrator. Although I
installed the MySQL ODBC driver, and set up and tested the ODBC
connection, when I actually launch Access and try to import tables from
the MySQL database, nothing happens. No error messages, nothing.

On my personal laptop (Windows XP) on which I AM the administrator,
this works very smoothly. I don't know if the difference here is
between 2000 vs XP or admin vs non-admin, but my suspicion is that
permissions may play a role.

Andy


Mike wrote:
> I have transferred the data from Access to MySQL using
> MySQL's tool. The transfer worked great and saved me a
> lot of time (thanks MySQL). Now I'm trying to get the
> Access database, or a new access database, to talk across
> ODBC and see the data.

Re: Problems connecting to ODBC

On 2006-07-25, Thomas Bartkus wrote:
> "Mike" wrote in message
> news:6yfxg.708$Fy2.457@fe04.lga...
>> I have transferred the data from Access to MySQL using
>> MySQL's tool. The transfer worked great and saved me a
>> lot of time (thanks MySQL). Now I'm trying to get the
>> Access database, or a new access database, to talk across
>> ODBC and see the data.
>>
>> I created the DSN through the control panel and it works
>> without error.
>>
>> How do I setup Access so it can see the data. The data
>> was on windows (M$ Access) and is now on linux (Fedora
>> Core 5).
>>
>> Followup: Seems like it connects, the 'Test' in the DSN
>> definition returns data. I turned on logging through
>> the DSN dialog and I see traffic between the DSN/application
>> (MS Access) and the MySQL server. Still, I do not see
>> anything usable returned from the MySQL server to Access.
>
> You do have the the MySQL ODBC driver installed on the Windows machine -
> yes?
>
> Investigate "linked tables" in Access. Linked tables can be pointers to
> tables residing on a MySQL server but look and act as if they were ordinary
> Access tables. You can program away using Microsoft tools in blissful
> disregard of the fact that the data actually resides on a MySQL/Linux
> server.
>
> If you already have an ODBC data source defined - you can right click on the
> [Tables] pane of Access and select the Linked table wizard to help you set
> them up.
>
> Also - check out pass through queries.
>
> Thomas Bartkus

Found it finally. Microsoft was/is 'helping' me. One of the updates from
Microsoft updated the file c:\winnt\system32\msjet40.dll to version 4.9025.
This version does not work talking to MySQL (at least not version 5.x that
is on my Fedora Core 5 box). At Microsoft (http://support.microsoft.com/kb/829558/)
there is SP8 for Jet 4.0. Pull the version for your OS (95/98/2000/Xp/etc),
execute the program like you're going to do the update, remember the filename
where files are extracted. In that file grab the file msjet40.dll. Do a
properties on this file. The version should be 4.0.8015.0. That version works
on my box. Rename the file in c:\winnt\system32\msjet40.dll (I used msjet40.dll.9025)
and copy the SP8 file to the name msjet40.dll. No reboot is required and I
can now link to my MySQL data.

Thanks for everone's help.

Mike