Problems connecting Access 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).

Mike

"Mike" <mikee@mikee.ath.cx> wrote in message
news:y4axg.1946$i25.897@fe05.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).

As long as the DSN is working correctly you should be able to connect using
the 'file' 'get external data' menu from within access. From the menu you
have the choice to either 'import' or 'link' to the external database.
Choose the option you want - import as it suggests imports all you data into
a local Access table, link on the other hand provides a live link to your
MySQL database, so any changes made in Access affect your MySQL DB
directly - and then from the following dialogue choose ODBC Databases() from
the 'files of type' drop down at the bottom of the dialogue. The next
dialogue will give you the option to select the datasource that you want to
connect to and finally a list of available tables will appear. I've worked
successfully with links between MS Access and MySQL on WinXP, though I have
no experience with Linux, Fedora, but provided the ODBC is the correct
version and working fine I don't see why you should have any problems.

Regards
Matt

ClickToWalk wrote on 7/24/06 11:18 PM:
> "Mike" <mikee@mikee.ath.cx> wrote in message
> news:y4axg.1946$i25.897@fe05.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).
>
> As long as the DSN is working correctly you should be able to connect using
> the 'file' 'get external data' menu from within access. From the menu you
> have the choice to either 'import' or 'link' to the external database.
> Choose the option you want - import as it suggests imports all you data into
> a local Access table, link on the other hand provides a live link to your
> MySQL database, so any changes made in Access affect your MySQL DB
> directly - and then from the following dialogue choose ODBC Databases() from
> the 'files of type' drop down at the bottom of the dialogue. The next
> dialogue will give you the option to select the datasource that you want to
> connect to and finally a list of available tables will appear. I've worked
> successfully with links between MS Access and MySQL on WinXP, though I have
> no experience with Linux, Fedora, but provided the ODBC is the correct
> version and working fine I don't see why you should have any problems.

Good instructions. I've got nothing to add to them. But since we're on
the subject of linking Access to MySQL, has it been your experience that
linked tables can be used in the same way that native Access tables are,
namely in forms? I'm attempting to accommodate a user who wants to
maintain an Access frontend to his data that we are storing in MySQL 5.

I've read the MySQL tips for using ODBC in Access [1] and have followed
the instructions contained within. But we're still having problems in
some of the forms, especially ones that work with more than one table.
It could very well be badly-made forms, but I want to make sure that the
Access-ODBC-MySQL link is reliable first. Have you (or anyone, for that
matter) come across any nonstandard behavior that you needed to work around?

Thanks,

Jake

[1]: http://dev.mysql.com/doc/refman/5.0/...s-apptips.html

"Jake Krohn" <krohnk@ece.cmu.edu> wrote in message
news:ea58ka$iae$1@nntp.ece.cmu.edu...
> ClickToWalk wrote on 7/24/06 11:18 PM:
>> "Mike" <mikee@mikee.ath.cx> wrote in message
>> news:y4axg.1946$i25.897@fe05.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).
>>
>> As long as the DSN is working correctly you should be able to connect
>> using the 'file' 'get external data' menu from within access. From the
>> menu you have the choice to either 'import' or 'link' to the external
>> database. Choose the option you want - import as it suggests imports all
>> you data into a local Access table, link on the other hand provides a
>> live link to your MySQL database, so any changes made in Access affect
>> your MySQL DB directly - and then from the following dialogue choose ODBC
>> Databases() from the 'files of type' drop down at the bottom of the
>> dialogue. The next dialogue will give you the option to select the
>> datasource that you want to connect to and finally a list of available
>> tables will appear. I've worked successfully with links between MS Access
>> and MySQL on WinXP, though I have no experience with Linux, Fedora, but
>> provided the ODBC is the correct version and working fine I don't see why
>> you should have any problems.
>
> Good instructions. I've got nothing to add to them. But since we're on the
> subject of linking Access to MySQL, has it been your experience that
> linked tables can be used in the same way that native Access tables are,
> namely in forms? I'm attempting to accommodate a user who wants to
> maintain an Access frontend to his data that we are storing in MySQL 5.
>
> I've read the MySQL tips for using ODBC in Access [1] and have followed
> the instructions contained within. But we're still having problems in some
> of the forms, especially ones that work with more than one table. It could
> very well be badly-made forms, but I want to make sure that the
> Access-ODBC-MySQL link is reliable first. Have you (or anyone, for that
> matter) come across any nonstandard behavior that you needed to work
> around?
>
> ClickToWalk wrote on 7/24/06 11:18 PM:
>> "Mike" <mikee@mikee.ath.cx> wrote in message
>> news:y4axg.1946$i25.897@fe05.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).
>>
>> As long as the DSN is working correctly you should be able to connect
>> using the 'file' 'get external data' menu from within access. From the
>> menu you have the choice to either 'import' or 'link' to the external
>> database. Choose the option you want - import as it suggests imports all
>> you data into a local Access table, link on the other hand provides a
>> live link to your MySQL database, so any changes made in Access affect
>> your MySQL DB directly - and then from the following dialogue choose ODBC
>> Databases() from the 'files of type' drop down at the bottom of the
>> dialogue. The next dialogue will give you the option to select the
>> datasource that you want to connect to and finally a list of available
>> tables will appear. I've worked successfully with links between MS Access
>> and MySQL on WinXP, though I have no experience with Linux, Fedora, but
>> provided the ODBC is the correct version and working fine I don't see why
>> you should have any problems.
>
> Good instructions. I've got nothing to add to them. But since we're on the
> subject of linking Access to MySQL, has it been your experience that
> linked tables can be used in the same way that native Access tables are,
> namely in forms? I'm attempting to accommodate a user who wants to
> maintain an Access frontend to his data that we are storing in MySQL 5.
>
> I've read the MySQL tips for using ODBC in Access [1] and have followed
> the instructions contained within. But we're still having problems in some
> of the forms, especially ones that work with more than one table. It could
> very well be badly-made forms, but I want to make sure that the
> Access-ODBC-MySQL link is reliable first. Have you (or anyone, for that
> matter) come across any nonstandard behavior that you needed to work
> around?

Hi Jake, I hit the wrong reply button so have reposted here now

The majority of issues I had with Access related to what went on behind
the dragging and dropping i.e. when creating queries or relationships the
construct is actually stored in a 'hidden' table within access, if any
corruption occurs there, it will cause unpredictable results elsewhere.
Other quirks I experienced were in the use of checkboxes; MS Access I think
stored a 'checked' control as a 1 and unchecked as 0, but when translated to
MySQL the values were -1 and 0, and dates; where the base date for MS and
MySQL (unix timestamp) are different. Thinking on, this didn't cause
difficulties with Access necessarily, but did with Excel.

I think the most important thing to remember is to (1) make sure that you
set up keys correctly on your data sources. Any keys in the MySQL database
will translate to access, and (2) Understand the relationships of your
tables. You can set up relationships using the manager in Access and this
will enable it (in most cases) to construct any queries correctly, any
unusual behaviour in forms is likely to be due to the queries at the back of
them. MS Access is a great tool for quickly putting user applications
together, but I found that it's important to have a good understanding of
what's going on behind the 'friendly' user interface. In summary, don't
assume that MS Access does everything right all the time.


Regards
Matt

"ClickToWalk" <admin@clicktowalk.com> wrote in message
news:44c63a6f$1_1@mk-nntp-2.news.uk.tiscali.com...
>
> The majority of issues I had with Access related to what went on behind
> the dragging and dropping i.e. when creating queries or relationships the
> construct is actually stored in a 'hidden' table within access, if any
> corruption occurs there, it will cause unpredictable results elsewhere.

> In summary, don't
> assume that MS Access does everything right all the time.

Just did a quick look on Google and found this
http://discuss.joelonsoftware.com/de...ign.4.80421.10

Illustrates what I was talking about.

Quote: "I guess Access is being to smart for its own good here. It
surprises me though, that a 2003 product corrupts data the way Access does
here"

Practical Geezer
17 February 2005

Matt

On 2006-07-25, ClickToWalk <admin@clicktowalk.com> wrote:
>
> "Mike" <mikee@mikee.ath.cx> wrote in message
> news:y4axg.1946$i25.897@fe05.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).
>
> As long as the DSN is working correctly you should be able to connect using
> the 'file' 'get external data' menu from within access. From the menu you
> have the choice to either 'import' or 'link' to the external database.
> Choose the option you want - import as it suggests imports all you data into
> a local Access table, link on the other hand provides a live link to your
> MySQL database, so any changes made in Access affect your MySQL DB
> directly - and then from the following dialogue choose ODBC Databases() from
> the 'files of type' drop down at the bottom of the dialogue. The next
> dialogue will give you the option to select the datasource that you want to
> connect to and finally a list of available tables will appear. I've worked
> successfully with links between MS Access and MySQL on WinXP, though I have
> no experience with Linux, Fedora, but provided the ODBC is the correct
> version and working fine I don't see why you should have any problems.
>
> Regards
> Matt
>
>

I can see in the c:\sql.log log where access is talking to mysql. I created
the MSysCon table, thinking that was a problem since this table was not found
(I since have learned this table is for customization only and it is ok if
the table does not exist). Using MS Access I start the program, go to the
dialog to open an ODBC connection, choose my working ODBC connection, then
get a dialog of creating tables. I chose a new linked table and them am
returned back to the dialog. I am not presented with a list of tables in the
MySQL database to choose from nor anything else. I just get dumped back to
the dialog that has at the top menus for 'Open', 'Design', or 'New'.

Where do I go from here or how do I create the linked tables?

Mike

"Mike" <mikee@mikee.ath.cx> wrote in message
news:hqrxg.143$nL.37@fe06.lga...
> On 2006-07-25, ClickToWalk <admin@clicktowalk.com> wrote:
>>
>> "Mike" <mikee@mikee.ath.cx> wrote in message
>> news:y4axg.1946$i25.897@fe05.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).
>>
>> As long as the DSN is working correctly you should be able to connect
>> using
>> the 'file' 'get external data' menu from within access. From the menu you
>> have the choice to either 'import' or 'link' to the external database.
>> Choose the option you want - import as it suggests imports all you data
>> into
>> a local Access table, link on the other hand provides a live link to your
>> MySQL database, so any changes made in Access affect your MySQL DB
>> directly - and then from the following dialogue choose ODBC Databases()
>> from
>> the 'files of type' drop down at the bottom of the dialogue. The next
>> dialogue will give you the option to select the datasource that you want
>> to
>> connect to and finally a list of available tables will appear. I've
>> worked
>> successfully with links between MS Access and MySQL on WinXP, though I
>> have
>> no experience with Linux, Fedora, but provided the ODBC is the correct
>> version and working fine I don't see why you should have any problems.
>>
>> Regards
>> Matt
>>
>>
>
> I can see in the c:\sql.log log where access is talking to mysql. I
> created
> the MSysCon table, thinking that was a problem since this table was not
> found
> (I since have learned this table is for customization only and it is ok if
> the table does not exist). Using MS Access I start the program, go to the
> dialog to open an ODBC connection, choose my working ODBC connection, then
> get a dialog of creating tables. I chose a new linked table and them am
> returned back to the dialog. I am not presented with a list of tables in
> the
> MySQL database to choose from nor anything else. I just get dumped back to
> the dialog that has at the top menus for 'Open', 'Design', or 'New'.
>
> Where do I go from here or how do I create the linked tables?
>
> Mike

In MS Access

File > Get External Data > Link Tables

In 'Link' dialogue, select 'ODBC Databases () from the 'Files of type'
dropdown.

In 'Select Data Source' dialogue, find and select your MySQL data source and
click [OK]

In the 'Link Tables' dialogue that appears, select the tables that you want
to link to your MS Access file/app and click [OK]

The tables should then appear as linked tables in your 'Tables' objects.
Linked tables have a Globe icon.

The linked tables can then be used in the same way as any other tables, bear
in mind that since the table is linked you are working directly on the data
in the external MySQL database.

Regards
Matt

NB While you can't edit the table design you can view it.

> I can see in the c:\sql.log log where access is talking to mysql. I
> created
> the MSysCon table, thinking that was a problem since this table was not
> found
> (I since have learned this table is for customization only and it is ok if
> the table does not exist). Using MS Access I start the program, go to the
> dialog to open an ODBC connection, choose my working ODBC connection, then
> get a dialog of creating tables. I chose a new linked table and them am
> returned back to the dialog. I am not presented with a list of tables in
> the
> MySQL database to choose from nor anything else. I just get dumped back to
> the dialog that has at the top menus for 'Open', 'Design', or 'New'.

There shouldn't be any 'creating' involved other than a link. You are
linking to an external datasource and reading data from it on a 'live'
basis.

>
> Where do I go from here or how do I create the linked tables?

Do you already have an MS Access database created or are you working from a
'quick start' type dialog box?

I have been working on the basis that you already have an app e.g. myapp.mdb
open and you are trying to link MySQL tables to it.

Matt

On 2006-07-25, ClickToWalk <admin@clicktowalk.com> wrote:
>
> "Mike" <mikee@mikee.ath.cx> wrote in message
> news:hqrxg.143$nL.37@fe06.lga...
>> On 2006-07-25, ClickToWalk <admin@clicktowalk.com> wrote:
>>>
>>> "Mike" <mikee@mikee.ath.cx> wrote in message
>>> news:y4axg.1946$i25.897@fe05.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).
>>>
>>> As long as the DSN is working correctly you should be able to connect
>>> using
>>> the 'file' 'get external data' menu from within access. From the menu you
>>> have the choice to either 'import' or 'link' to the external database.
>>> Choose the option you want - import as it suggests imports all you data
>>> into
>>> a local Access table, link on the other hand provides a live link to your
>>> MySQL database, so any changes made in Access affect your MySQL DB
>>> directly - and then from the following dialogue choose ODBC Databases()
>>> from
>>> the 'files of type' drop down at the bottom of the dialogue. The next
>>> dialogue will give you the option to select the datasource that you want
>>> to
>>> connect to and finally a list of available tables will appear. I've
>>> worked
>>> successfully with links between MS Access and MySQL on WinXP, though I
>>> have
>>> no experience with Linux, Fedora, but provided the ODBC is the correct
>>> version and working fine I don't see why you should have any problems.
>>>
>>> Regards
>>> Matt
>>>
>>>
>>
>> I can see in the c:\sql.log log where access is talking to mysql. I
>> created
>> the MSysCon table, thinking that was a problem since this table was not
>> found
>> (I since have learned this table is for customization only and it is ok if
>> the table does not exist). Using MS Access I start the program, go to the
>> dialog to open an ODBC connection, choose my working ODBC connection, then
>> get a dialog of creating tables. I chose a new linked table and them am
>> returned back to the dialog. I am not presented with a list of tables in
>> the
>> MySQL database to choose from nor anything else. I just get dumped back to
>> the dialog that has at the top menus for 'Open', 'Design', or 'New'.
>>
>> Where do I go from here or how do I create the linked tables?
>>
>> Mike
>
> In MS Access
>
> File > Get External Data > Link Tables
>
> In 'Link' dialogue, select 'ODBC Databases () from the 'Files of type'
> dropdown.
>
> In 'Select Data Source' dialogue, find and select your MySQL data source and
> click [OK]
>
> In the 'Link Tables' dialogue that appears, select the tables that you want
> to link to your MS Access file/app and click [OK]
>
> The tables should then appear as linked tables in your 'Tables' objects.
> Linked tables have a Globe icon.
>
> The linked tables can then be used in the same way as any other tables, bear
> in mind that since the table is linked you are working directly on the data
> in the external MySQL database.
>
> Regards
> Matt
>
> NB While you can't edit the table design you can view it.
>
>
>
>
>
>

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