Can't connect Access to MySQL

[GrandMasterTuck]

Hi folks. I signed up for a hosting account with Hostgator, and followed their instructions for setting up a MySQL database, enabling remote connections and adding my PC's IP address as an authorized 'host'. The Hostgator account is a linux server.

I have an access 2010 application (simple scheduling and payroll app) that is a split DB, and I want to move the tables from the scheduling_be.accdb file to this remote MySQL server, but I'm stumped on how to do it.

I created a new ODBC connection, and Hostgator said to supply the hosting account's IP address as the answer to the question "Which server do you want to connect to?"

I set the username and password fields to my database's username and database user's password.

I get errors. What am I doing wrong? I'm really new at this MySQL connecting stuff, so it would really help if somebody could kinda walk me through it once? Hostgator's tech support people are telling me I can't do what I'm trying to do with a linux hosting account, and they tell me to sign up for a more expensive Windows hosting account, and I am suspicious that this might be bullcrap. Any help would be greatly appreciated!

 

[Ranman256]

Do you have the latest ODBC driver for MySql?

 

[GrandMasterTuck]

Do you have the latest ODBC driver for MySql?

Oh boy... here's where my ignorance comes in. I have no idea. How can I find this out? Keep in mind, the MySQL database is on a hosting account, not on my PC, and not on any PC's or servers in my personal network at work.

 

[jdraw]

You may need some of this info re connector/MySQL

 

[Ranman256]

in the control panel, administrative tools, data sources (ODBC)
ADD

 

[GrandMasterTuck]

You may need some of this info re connector/MySQL

Oh no... is this something that I have to do on all the PC's that are going to connect to this MySQL database, or is it something I only have to do once to the front-end Access app, and then anyone that uses the app will have it?

If I have to install this for every PC that's going to connect, I might be in trouble, because these are work computers, and I might not have sufficient privileges to install software...

EDIT: I just did some reading, and found out that I have to install this ODBC connector on the PC's. My user account on the PC's doesn't have clearance to install applications, so I might be sunk... any other solutions? Like, is there another type of online database I can connect to that won't require installing software?

 

[kevlray]

From my experience using Microsoft SQL Migration Assistant for Access will move tables from Access to an MS-SQL instance (linking the tables to the FE) without the end user installing any other software. The only way that we have this working is by setting up users with Windows Authentication on the MS-SQL side and Active Directory on the client side. I know that we had some hiccups early on (I do not know the details) and this was done on a Novell network.

 

[GrandMasterTuck]

Would the Migration Assistant be able to connect to a remote database? It's not like the database I'm connecting to exists on my work's network, there isn't a server room around the corner where I can access the rack. This is a REMOTE server, somewhere out of state, probably, and all I have is an IP address, DB name, DB username, and password.