PDA

View Full Version : Using Microsoft Access 2002 to connect to mySQL database.



andy
03-19-2004, 06:49 AM
Does anyone know if it's possible to set up MS Access on home machine to connect to mySQL databases on Westhost servers? I have tried the ODBC with no luck. From what I've read you should be able to connect the two and even be able to update the mySQL database from Access. I was able to set up DreamWeaver MX to connect to mySQL, so I would think Access will work.

Also if this is not possible, what I'm trying to do is pull my order addresses from mySQL so that I can print the labels. I'm thinking that pulling them into access would be an easy way, but I'd rather not have to keep downloading csv files all the time if the connection is not possible. Is there a "plug in" or feature for mySQL that would allow for easy label printing? I just started using mySQL so any hints would be appreciated. Thanks.

jalal
03-19-2004, 10:26 AM
I find the easiest way to interact with MySQL is using PHP.
For instance, I have a form which I can use to upload a CSV file and import it into the database and another form with which I can dump from the database into a local CSV file.

For printing, I would just use PHP to access the data from MySQL and create an HTML page that you could then print.

andy
03-19-2004, 11:57 PM
Thanks Jalal for the info. I might be asking some more questions about your suggestions if no one answers me the question "Can I connect a local access database to a westhost mysl database." Please Please Please...it would make life so much easier!!!

jalal
03-20-2004, 01:49 AM
...if no one answers me the question "Can I connect a local access database to a westhost mysl database."

I dodged this question because I didn't really understand what you mean, to connect one database to another? Do you mean having external linked tables in your access database pointing to MySQL tables? In my experience externally linked tables are often problematic unless the database/tables are on the same computer.
If you did want to try it, you would need to use ODBC I think, that would mean installing an ODBC gateway on your Westhost server. Google for MySQL ODBC drivers.

HTH

andy
04-01-2004, 05:41 PM
Westhost said...

No it is not possible to do what you wish. It would have to be on a windows based machine running mysql server in order to do this.

But using PHP to view/change the database is probably the best way now that I'm getting used to it.

<-- R O B -->
04-04-2004, 08:22 AM
Hi Andy

I have got Access 2002 runing on windows XP to connect to a westhost mySQL database. The main requirement is that your account has a fixed IP address - if not you can not connect.

Once you have a fixed ip address, you can install the MyODBC driver on your pc and create a MySQL DSN (data source name) to connect to the database. The latest version of this driver allows you to perform a test connect which is handy.

Then in Access either import or link to the mySQL database by choosing the DSN you previously created.

Hope that helps
Rob C

andy
04-04-2004, 01:49 PM
Thanks Rob,
Unfortunately I have a shared IP. Maybe that's why WestHost said I couldn't do it. PHP has been working great, and the guys on this forum have been real helpful with my newbie questions about PHP.

FZ
04-04-2004, 04:00 PM
Well, you could pay the extra $1 monthly for the dedicated IP address... It seems in your case it would definitely be worth it!

andy
04-04-2004, 04:30 PM
I forgot that it was only $1 a month. Thanks for the reminder. At this point I'm happy with the way PHP is able to access my database so I don't really need the access database, but I am concerned about the speed that users are able to get to my site. Some times it's fast, sometimes slow. Does a dedicated ip speed up the time it takes for users to get to my site from say google? I not talking about the obvious like size of my site, users connection speed, etc, but about the way Westhost has shared IP's and the number of users using them compared to having a dedicated IP. Would it possibly save a second of time for the user? Or even half a second? There's probably not a right answer, but any thoughts?

FZ
04-04-2004, 04:36 PM
Highly unlikely! Even if it magically made a difference on your site's response and/or load time(s), it would be so nominal that you would not even notice it. A dedicated IP is ("technically") useful (and trendy!) but that's about it ;)

FZ
04-04-2004, 06:21 PM
andy,

I just came across something that might be helpful to you: http://channels.lockergnome.com/windows/backissues/20040331.phtml#20040331_2

It's a program called SenLab that will help you with your labelling...

andy
04-04-2004, 09:30 PM
Thanks FZ, I'll check it out.

andy
06-25-2004, 03:57 PM
Thanks ROB for your help. I got the ODBC connection working and it's great having MS Access to view/update my database.

Jalal, at one point you posted about using PHP to work with your database. If possible I would highly recommend setting up access and taking a look. It's great. It's so easy to move around your forms fields, set up calendar date entries, do reports and graphs, verify fields, just to many things that would take forever using PHP.

For those of you wanting to set up Access database to mySQL server you do need a dedicated IP. Before you set up the ODBC connection in XP control panel make sure you create a new user for your mySQL database that has a host of "any host". You can not use root account or your main westhost loggin account. That's what was killing me. Other then that it was a piece of cake.

andy
06-25-2004, 04:07 PM
If you want detailed instruction on setting this up, post a reply here, and I'll write some up. :D

jalal
06-26-2004, 02:38 AM
Hi Andy

I was an Access developer for years. But for web site work (or any kind of situation where there is a danger of more than one client at a time) it simply doesn't cut it. Using Access as a front end for MySQL is an interesting idea, but I've never found a need for it.
Using the mysql command line client or MyPhpAdmin if I want a graphical view, simply does everything that I need.

And, Access only runs under Windoze, which I don't have.

So, I'm a happy LAMP user...

vanleurth
01-13-2006, 04:17 PM
Hello,

I wonder if I could get some hints or even a manual on how to connect an Access Front end to a MySQL backend that lives in the internet.

Thank you,

V.

jhmisa
05-10-2006, 07:29 PM
If you want detailed instruction on setting this up, post a reply here, and I'll write some up. :D

Hello Andy

I am trying to connect MS Access to the MySQL in Westhost but I can't seem to do it. Since you were able to successfully do this, I would like to know if you can help me.

I already have an IP Based account (Static IP). I have already created a new account, which I read on the post and I used the following details to login in Access using the Link Table --> Connector/ODBC - I keep getting this error.
[MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on 'bytedrive.com' (10061)

What do I need to do to make it work? Do I need to open up a port? I hope you can make a detailed instruction as I'm just a beginner. Thanks in advance for your help