RE: MySQL, MyODBC and MS Access
Hi !! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Sunday, April 21, 2002 11:52 PM To: [EMAIL PROTECTED] Subject: MySQL, MyODBC and MS Access Hello! You are my last chance, reading FAQs and asking in newsgroups gives no effect. I have a problem when connecting a MySQL table in MS Access (2000) through MyODBC. MySQL is ver. 3.23.44 running on FreeBSD 4.5 box. Description of the problem: I connect the table and Access correctly retrieves data. But when user tries to add data or to change any field, access performs an error box that some another user has changed this field when you were changing it, but actually nobody was connected to MySQL at that time. This problem takes place only in Windows'98. When I perform the same operation from Windows XP all is correct. Maybe I have to make some additional settings for MyODBC (version 2.50.39-win95) in Windows98? Please help. Thanks in advance! You should set the OPTION=2. I think its listed in the FAQ too. Coming to XP, whats your Access version on XP ? (that might be a latest version, which has many fixes towards ODBC that exists in older versions :) Regards, Venu -- For technical support contracts, go to https://order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Venu [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer /_/ /_/\_, /___/\___\_\___/ California, USA ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL, MyODBC and MS Access
-Original Message- From: Mark Stringham [mailto:[EMAIL PROTECTED]] Sent: Friday, March 22, 2002 2:51 PM To: Chuck Roberts Cc: Mysql; Mark Stringham Subject: RE: MySQL, MyODBC and MS Access Chuck - I am looking for a way for both MS access and MySQL to share realtime data so that if someone where to insert (through a web based admin) or update table in a MySQL db you could open your Access db and see the same data. I don't know if it is possible but that way there wouldn't be an export to MySQL procedure. Does that make sense? I don't know much about Myodbc except it allows you to export your Access database to MySQL. My question is if MySQL gets updated does Access get updated too? I have a client who wants everyone in the office to have the ability to update and view data in an Access db. My understanding of how access works is only one person can be connected or update the db at a time.(depending on the # of odbc connection you Not true. Many people can be connected to an MDB file at the same time if the MDB file is on a network drive. (I've done this.) But Access only handles up to 10-15 simultaneous connections well. Then you have the record locking problem too. What if someone pulls up a record then forgets to let go of it? No one else can update that record (though they could view it). have) My thought was if we got Access and MySQL talking, I could create a web based You don't need to make it web-based. admin that would allow many folks to query and update the MySQL db and if MySQL and Access are talking then the data in the Access db would get updated as well. Really just trying to create a remote way to access and update from the Access db. Sorry for the long story but is this something you've done or is it not at all possible? Yes this is possible. If you are going to have more than 10 simultaneous connections then I would go with Mysql backend with an Access frontend (which requires ODBC). Otherwise I would just plop an MDB file on a network drive. Everyone would be able to see everyone else's changes in either case. You don't need to be copying data between an Access MDB and a mysql db. Does that make sense? It's easier than you think. Chuck p.s. We are working on the same thing here: an Access front end for the pretty, user-friendly screens with a mysql backend db for performance. BUT, myODBC allows Access front ends to come in from the internet also, not just from another client on the company network. So, once a client is set up with Access and MyODBC, they can go over the internet and update our mysql database. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL, MyODBC and MS Access
This seems to be a very common question that appears very frequently on the group, in fact one I have asked in the past too. Could I stick my head over the parapet and suggest a person or persons with experience of Access front ends and MySQL backend puts together an FAQ for this, or better still it's included in detail in the documentation? FWIW a very good utility for exporting tables and data is MyAccess (available from http://www.accessmysql.com/) it would appear that the latest version also supports synchronisation between the two databases. I have successfully used this to move an applications tables from Access to MySQL with very few problems except with new records causing problems in forms due to the way that Access queries the database and deals with the index information. HTH Alex --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.342 / Virus Database: 189 - Release Date: 14/03/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL, MyODBC and MS Access
Hi ! -Original Message- From: Alexander Shaw [mailto:[EMAIL PROTECTED]] Sent: Saturday, March 23, 2002 11:19 PM To: MySQL List Subject: RE: MySQL, MyODBC and MS Access This seems to be a very common question that appears very frequently on the group, in fact one I have asked in the past too. Could I stick my head over the parapet and suggest a person or persons with experience of Access front ends and MySQL backend puts together an FAQ for this, or better still it's included in detail in the documentation? Most of the common question got answered from online MyODBC faq from: http://www.mysql.com/products/myodbc/faq_toc.html If you find something is missing or wants to add under MSAccess section, then please send a mail to mailto:[EMAIL PROTECTED] with details. Thanks. Regards, Venu -- For technical support contracts, go to https://order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Venu [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer /_/ /_/\_, /___/\___\_\___/ California, USA ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL, MyODBC and MS Access
There might be a better way but here's a suggestion: When I needed to switch from an Access db to mysql I couldn't find any docs about exporting the table structures from Access to mysql, so I documented the Access db and manually built the same table structures in mysql. Then export the Access data as comma delimited files and import them into mysql. If you want to do this on a regular basis you could set a schedule(either human or with crontab) to periodically export the Access contents and reload the mysql tables. Andrew Hazen -Original Message- From: Mark Stringham [mailto:[EMAIL PROTECTED]] Sent: Friday, March 22, 2002 3:11 AM To: MySQL Subject: MySQL, MyODBC and MS Access I have a project where I may need to connect an MS ACCESS db and MySQL. I have read some pretty good documentation and know that is possible to take an existing Access db and export it to MYSQL. I have a few questions and wondered if someone might offer some insight. 1. Where must the Access db reside in order to talk with MySQL? 2. After the import to MySQL, if I insert new data into MySQL will the Access db be updated as well? I am looking for a way to access live MS Access data from the web. Obviously, I could connect directly to the Access db itself but with limited connections, I would think it would be hard to share the data . My thought was when someone updated the Access db, the data is then exported to MySQL. I then could use php, JSP or whatever to access the data from the web without having to worry about odbc connections. Am I way off here? Any help is appreciated. thanks Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL, MyODBC and MS Access
hello mark, been doing this quite a bit recently but i cheat a bit... 1st step is to export the table from access as a text file - delimited by comma's. if you load up phpMyAdmin (available at http://www.phpwizard.net/projects/phpMyAdmin/), create your database and then mimic the table set up from access, you can then use the 'Insert textfiles into table' function to load all the information. a bit time consuming but it does work! phpMyAdmin is wicked as well hope this helps, jake I have a project where I may need to connect an MS ACCESS db and MySQL. I have read some pretty good documentation and know that is possible to take an existing Access db and export it to MYSQL. I have a few questions and wondered if someone might offer some insight. 1. Where must the Access db reside in order to talk with MySQL? 2. After the import to MySQL, if I insert new data into MySQL will the Access db be updated as well? I am looking for a way to access live MS Access data from the web. Obviously, I could connect directly to the Access db itself but with limited connections, I would think it would be hard to share the data . My thought was when someone updated the Access db, the data is then exported to MySQL. I then could use php, JSP or whatever to access the data from the web without having to worry about odbc connections. Am I way off here? Any help is appreciated. thanks Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL, MyODBC and MS Access
I'm doing something similar, with an Access front-end and mysql back-end. -Original Message- From: Mark Stringham [mailto:[EMAIL PROTECTED]] Sent: Friday, March 22, 2002 3:11 AM To: MySQL Subject: MySQL, MyODBC and MS Access I have a project where I may need to connect an MS ACCESS db and MySQL. I have read some pretty good documentation and know that is possible to take an existing Access db and export it to MYSQL. I have a few questions and wondered if someone might offer some insight. 1. Where must the Access db reside in order to talk with MySQL? It doesn't matter. As long as you install Myodbc on the client with Access, then myodbc can talk to mysql. 2. After the import to MySQL, if I insert new data into MySQL will the Access db be updated as well? Not automatically. If you want the databases each to be updated automatically, I don't think you can replicate between Access and Mysql. Either both dbs have to be Access or both Mysql. I am looking for a way to access live MS Access data from the web. Obviously, I could connect directly to the Access db itself but with limited connections, I would think it would be hard to share the data . My thought was when someone updated the Access db, the data is then exported to MySQL. I then could use php, JSP or whatever to access the data from the web without having to worry about odbc connections. We're doing the exact same thing. We have Access as a front-end, and myodbc installed with the client (on the front end). In Myodbc, you specify a data source with an IP address and database name. That connects to a Mysql server running on the same IP address. The only problem we're having is how to encrypt the data between the client (Access) and server (mysql). Mysql supports SSL but Access does not. We're looking at using stunnel (which uses openssl, www.openssl.org), which is like a gateway between Access and Mysql. Chuck - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL, MyODBC and MS Access
Chuck - I am looking for a way for both MS access and MySQL to share realtime data so that if someone where to insert (through a web based admin) or update table in a MySQL db you could open your Access db and see the same data. I don't know if it is possible but that way there wouldn't be an export to MySQL procedure. Does that make sense? I don't know much about Myodbc except it allows you to export your Access database to MySQL. My question is if MySQL gets updated does Access get updated too? I have a client who wants everyone in the office to have the ability to update and view data in an Access db. My understanding of how access works is only one person can be connected or update the db at a time.(depending on the # of odbc connection you have) My thought was if we got Access and MySQL talking, I could create a web based admin that would allow many folks to query and update the MySQL db and if MySQL and Access are talking then the data in the Access db would get updated as well. Really just trying to create a remote way to access and update from the Access db. Sorry for the long story but is this something you've done or is it not at all possible? Any ideas? Thanks Mark At Friday, 22 March 2002, Chuck Roberts [EMAIL PROTECTED] wrote: I'm doing something similar, with an Access front-end and mysql back-end. -Original Message- From: Mark Stringham [mailto:[EMAIL PROTECTED]] Sent: Friday, March 22, 2002 3:11 AM To: MySQL Subject: MySQL, MyODBC and MS Access I have a project where I may need to connect an MS ACCESS db and MySQL. I have read some pretty good documentation and know that is possible to take an existing Access db and export it to MYSQL. I have a few questions and wondered if someone might offer some insight. 1. Where must the Access db reside in order to talk with MySQL? It doesn't matter. As long as you install Myodbc on the client with Access, then myodbc can talk to mysql. 2. After the import to MySQL, if I insert new data into MySQL will the Access db be updated as well? Not automatically. If you want the databases each to be updated automatically, I don't think you can replicate between Access and Mysql. Either both dbs have to be Access or both Mysql. I am looking for a way to access live MS Access data from the web. Obviously, I could connect directly to the Access db itself but with limited connections, I would think it would be hard to share the data . My thought was when someone updated the Access db, the data is then exported to MySQL. I then could use php, JSP or whatever to access the data from the web without having to worry about odbc connections. We're doing the exact same thing. We have Access as a front-end, and myodbc installed with the client (on the front end). In Myodbc, you specify a data source with an IP address and database name. That connects to a Mysql server running on the same IP address. The only problem we're having is how to encrypt the data between the client (Access) and server (mysql). Mysql supports SSL but Access does not. We're looking at using stunnel (which uses openssl, www.openssl.org), which is like a gateway between Access and Mysql. Chuck - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-mstringham=edeasolutions. com@lists. mysql.com Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Regards, Mark Stringham 801.602.8791 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL, MyODBC and MS Access
Your client won't drop Access in favor of a faster, more secure database like mySQL? Why is your client stuck on Access? The data can be dumped and make the GUI web based to look just like the Access one... Stick in on a Intranet server, and lock it down... I would rather have that than a slow, unsecure, limited DB like Access... Back to a solution... The problem is that you would need to run a query to pull the data from the Access db to the mySQL one, which could be automated in Access. Just add a few extra lines in your code for each form and should work beautifully. A problem that may or may not occur is the fact that Access uses different kinds of datatypes in its tables. If these datatypes are in the Access DB tables, using a myODBC connection, all the data transferring will fail because mySQL doesn't recognize the datatypes -Original Message- From: Mark Stringham [mailto:[EMAIL PROTECTED]] Sent: Friday, March 22, 2002 1:51 PM To: Chuck Roberts Cc: Mysql; Mark Stringham Subject: RE: MySQL, MyODBC and MS Access Chuck - I am looking for a way for both MS access and MySQL to share realtime data so that if someone where to insert (through a web based admin) or update table in a MySQL db you could open your Access db and see the same data. I don't know if it is possible but that way there wouldn't be an export to MySQL procedure. Does that make sense? I don't know much about Myodbc except it allows you to export your Access database to MySQL. My question is if MySQL gets updated does Access get updated too? I have a client who wants everyone in the office to have the ability to update and view data in an Access db. My understanding of how access works is only one person can be connected or update the db at a time.(depending on the # of odbc connection you have) My thought was if we got Access and MySQL talking, I could create a web based admin that would allow many folks to query and update the MySQL db and if MySQL and Access are talking then the data in the Access db would get updated as well. Really just trying to create a remote way to access and update from the Access db. Sorry for the long story but is this something you've done or is it not at all possible? Any ideas? Thanks Mark At Friday, 22 March 2002, Chuck Roberts [EMAIL PROTECTED] wrote: I'm doing something similar, with an Access front-end and mysql back-end. -Original Message- From: Mark Stringham [mailto:[EMAIL PROTECTED]] Sent: Friday, March 22, 2002 3:11 AM To: MySQL Subject: MySQL, MyODBC and MS Access I have a project where I may need to connect an MS ACCESS db and MySQL. I have read some pretty good documentation and know that is possible to take an existing Access db and export it to MYSQL. I have a few questions and wondered if someone might offer some insight. 1. Where must the Access db reside in order to talk with MySQL? It doesn't matter. As long as you install Myodbc on the client with Access, then myodbc can talk to mysql. 2. After the import to MySQL, if I insert new data into MySQL will the Access db be updated as well? Not automatically. If you want the databases each to be updated automatically, I don't think you can replicate between Access and Mysql. Either both dbs have to be Access or both Mysql. I am looking for a way to access live MS Access data from the web. Obviously, I could connect directly to the Access db itself but with limited connections, I would think it would be hard to share the data . My thought was when someone updated the Access db, the data is then exported to MySQL. I then could use php, JSP or whatever to access the data from the web without having to worry about odbc connections. We're doing the exact same thing. We have Access as a front-end, and myodbc installed with the client (on the front end). In Myodbc, you specify a data source with an IP address and database name. That connects to a Mysql server running on the same IP address. The only problem we're having is how to encrypt the data between the client (Access) and server (mysql). Mysql supports SSL but Access does not. We're looking at using stunnel (which uses openssl, www.openssl.org), which is like a gateway between Access and Mysql. Chuck - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-mstringham=edeasolutions. com@lists. mysql.com Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Regards, Mark Stringham 801.602.8791 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL
