Access 2013 to Remote MySQL connection

I have developed a database using MS Access 2013 front end with a MySQL back end on my Windows 7 Pro 64 bit PC and now want to set up the production version for 2 remote workers.

So far I've managed to upload the back end to the web server and can connect to it from my PC using MySQL Workbench but I'm mystified as to how I now go about setting up the users.

They will be running Windows 7 PC's where the  Access 2013 front ends will be located but what do I need to do to link the tables from their PC's to the remote server?

From what I can gather so far I think I need to set up a system DSN with the connection string, then link the tables? If that's the case then do I need to do this on each of the PC's and link the tables individually or is there a way of just having a common DSN?

Any help much appreciated.
redpoppyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
Each PC needs the DSN set up on that machine.  There isn't any way to share it.  Each PC also needs the MySQL ODBC connector so that Access can connect to MySQL.  See here: https://dev.mysql.com/downloads/connector/odbc/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
redpoppyAuthor Commented:
Hi Dave, Thanks for your reply.
So do I set up a "Machine" or "User" DSN on each PC?
I presume I then have to re-link the tables in each set up?
Is there a way to do this through VBA?
Thanks again for your help, much appreciated
0
Dave BaldwinFixer of ProblemsCommented:
So do I set up a "Machine" or "User" DSN on each PC?
I don't remember and I don't have a machine with Access on at the moment.  In any case, you have to install the MySQL ODBC driver first.  Do you have that set up on your own machine?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

redpoppyAuthor Commented:
Hi Dave, yes I do have it on my PC. I'll try replicating that on another machine and see what happens.
0
redpoppyAuthor Commented:
I've tested it out by creating DSN's for each PC and it works fine. Thanks a lot for your help
0
Dave BaldwinFixer of ProblemsCommented:
You're welcome, glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.