Go Back   Access World Forums > Apps and Windows > Web Design and Development > PHP & MySQL

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 01-27-2010, 08:04 AM   #1
AJordan
Newly Registered User
 
Join Date: Mar 2008
Posts: 73
Thanks: 2
Thanked 1 Time in 1 Post
AJordan will become famous soon enough
How to use Access with mysql

Ive searched the web and this site and have found out its possible to connect a MS Access front end to a mysql back end. I am interested in taking my current database which is already split and change it to an access FE/ mysql BE.

Since I already have a split database, I downloaded a program that will convert my access database (Tables) to mysql. Now I am confused to where I can actually host my mysql database and access it remotely. My current web hosting company, does not allow remote connections to mysql databases, and a few others Ive researched do not allow it either.

If anyone knows of a hosting service that allows for remote connections (free or paid) I would be greatly appreciative. Currently I have not been able to able test this format out because I dont know where I can host my mysql database and I have no server. I am interested in this setup (Access FE/ mysql BE) so that I can access the same data wherever the FE is installed and not just on a local network. Thanks!

AJordan is offline   Reply With Quote
Old 01-27-2010, 08:13 AM   #2
Banana
split with a cherry atop.
 
Join Date: Sep 2005
Posts: 6,315
Thanks: 0
Thanked 90 Times in 72 Posts
Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all
Re: How to use Access with mysql

Just so you are aware- you don't have to have an actual server - if the demand is small, you could get away using a computer somewhere in your company to run the MySQL locally. Just an option.

One possible hosting company I'd look at would be Rackspace.
__________________
If relation-valued attributes and arbitrarily complex types are wrong, then I don't wanna to be right!
Founder of 'Blame the Developers First' crowd.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Banana is offline   Reply With Quote
Old 01-27-2010, 08:26 AM   #3
AJordan
Newly Registered User
 
Join Date: Mar 2008
Posts: 73
Thanks: 2
Thanked 1 Time in 1 Post
AJordan will become famous soon enough
Re: How to use Access with mysql

Well, my partners and I travel a lot during the year and actually spend a lot of time away from our small office. The biggest problem is that our ISP does not have an option for us to purchase a static IP address, so using vpn will not work for us. Tha is why mysql looks like the best option, currently our back end is on a network attached storage device (that we use inplace of an actual server). We have also tried Hamachi I believe, and although it works it's pretty slow and we have to keep a computer on at all times.

If anyone knows of a better solution I'm more than open, until then I will check out rackspace


Last edited by AJordan; 01-27-2010 at 08:38 AM.
AJordan is offline   Reply With Quote
Old 12-23-2011, 11:26 AM   #4
icemonster
Newly Registered User
 
Join Date: Jan 2010
Posts: 502
Thanks: 48
Thanked 1 Time in 1 Post
icemonster is on a distinguished road
Re: How to use Access with mysql

this may be a year late but justhost allows remote mysql connections you just need to specify the ip address.
icemonster is offline   Reply With Quote
Old 12-04-2012, 10:52 PM   #5
bparker1084
Newly Registered User
 
Join Date: Oct 2012
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
bparker1084 is on a distinguished road
Re: How to use Access with mysql

The MySQL database can be used as an import source, an export source, or as a linked table for direct use within an Access application, so you can use Access as the front-end interface to a MySQL database.
bparker1084 is offline   Reply With Quote
Old 02-22-2013, 01:00 PM   #6
speakers_86
I am jack's comment.
 
speakers_86's Avatar
 
Join Date: May 2007
Location: JBLM, Wa
Posts: 1,919
Thanks: 11
Thanked 160 Times in 119 Posts
speakers_86 will become famous soon enough
Re: How to use Access with mysql

I revisited this forum and stumbled upon this thread again. I've made a program in Access that is working great, but I am thinking of converting it to a more commercial type of software.

As far as I see, I have two options. The first is to rebuild my entire application in a purely HTML and PHP format. This has some obvious benefits, but I would have to teach myself an awful lot.

I was all geared up and ready for the first approach, and then I read this thread again. Now, I am wondering if it is better to simply move my tables into MySQL, and simply keep the Access front end. This is simpler, could be online if so desired, but I'm worried customer's would not be able to get it up and running initially. I'm also not sure how to package all the different things that would be required (Access Runtime, ODBC driver, MySQL). I also rely heavily on the treeview v6 activex control.

How should I tackle this?
__________________
If you look, you can find anything.
Google is your friend.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


~~~~~~~~~~~~~~~~~~~~~~~~~~~
Access 2010 screw this! I went back to 2007
Windows 7
speakers_86 is offline   Reply With Quote
Old 05-05-2013, 11:22 PM   #7
preetisoft2
Newly Registered User
 
Join Date: May 2013
Posts: 14
Thanks: 0
Thanked 3 Times in 1 Post
preetisoft2 is on a distinguished road
Re: How to use Access with mysql

Nice post.

preetisoft2 is offline   Reply With Quote
Old 08-18-2018, 03:05 PM   #8
p.perez
Newly Registered User
 
Join Date: Jan 2018
Posts: 12
Thanks: 4
Thanked 0 Times in 0 Posts
p.perez is on a distinguished road
Re: How to use Access with mysql

Hi everyone,

Im new in migrating and splitting databases. Im trying to use postgres but i think that my question will aplly to mysql so im asking here:

In my access Database i linked via ODBC the tables froms postgres (should be the same as mysql) which were exported before from access. The question is how do i use the forms and vba already in my front end because all the "new tables" have the "public" suffix.

Should i write all the forms and vba and referrence to the tables with the new "public"?
For example "tbl_users" now is called "public. tbl_users" or there is another way?

Sorry if this post is confusing

Any help will be appreciated
p.perez is offline   Reply With Quote
Old 08-18-2018, 03:45 PM   #9
p.perez
Newly Registered User
 
Join Date: Jan 2018
Posts: 12
Thanks: 4
Thanked 0 Times in 0 Posts
p.perez is on a distinguished road
Re: How to use Access with mysql

I found and answer. It is to rename the "public_tbl_users" to "tbl_users". All the forms are working.
p.perez is offline   Reply With Quote
Old 01-04-2019, 11:30 AM   #10
abubasil
Newly Registered User
 
Join Date: Aug 2011
Posts: 27
Thanks: 2
Thanked 3 Times in 3 Posts
abubasil is on a distinguished road
Re: How to use Access with mysql

Mariadb is good alternative .. see this odbc connector https://downloads.mariadb.org/connector-odbc/3.0.5/


it works with both mysql and mariadb itself.
I tested it many times in both my internet website databases and over the lan network.
abubasil is offline   Reply With Quote
Old 02-01-2019, 02:13 PM   #11
Jackal
Newly Registered User
 
Join Date: Feb 2019
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Jackal is on a distinguished road
Exclamation Re: How to use Access with mysql

Thanks to this post as a starting point, I managed to connect my Access 2007 app directly to a back end MYSQL in the cloud via a secure SSL connection (not on top of SSH tunnel).

Here's the basics

Step 1: Generate and Enable SSL certs on the backend mysql server

See Reference here: lowendbox.com/blog/getting-started-with-mysql-over-ssl/

Step 2: Download and install the MYSQL odbc connector (dev.mysql.com/downloads/connector/odbc/]) Make you u download the 32 bit version (64 bit driver don't work on Access)

Step 3: Configure ODBC in windows
Goto Control Panel, Administrative Tools, ODBC Data Sources 64 Bit.
  1. a. Goto System DSN tab
  2. b. Add Mysql connector (Unicode) 32 bit
  3. c. Put in the client cert, client key and ca cert
  4. d. Give it a name (e.g. MySQL_backend)
Step 4: Connect Access to the MYSQL
In Access, Go to External Data, ODBC Database, choose Machine Data Source tab, and point to the name in 3d. above.


Viola!!


Extra notes:
i. When generating the CA cert in Step 1, make sure the Common Name is different from the ones used for the Server and Client certs
ii. To ensure your certs are ok, verify it has no errors, otherwise it will fail to connect in Step 3.
The command line to verify in linux:
Code:
openssl verify -CAfile /etc/mysql/ca-cert.pem /etc/mysql/server-cert.pem /etc/mysql/client-cert.pem
iii. You also need to ensure SSL for your MYSQL is enabled using this MYSQL command:
Code:
SHOW VARIABLES LIKE '%ssl%';
It should show ENABLED not DISABLED.

iv. If it is DISABLED, make sure the certs file owner and group are 'mysql'. Use
Code:
chown mysql *.pem
chgrp mysql *.pem
and restart your mysql service.



Hope this helps!
Jackal is offline   Reply With Quote
Old 04-24-2019, 10:11 AM   #12
Jackal
Newly Registered User
 
Join Date: Feb 2019
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Jackal is on a distinguished road
Re: How to use Access with mysql

I managed to connect to a remote mysql and perform processing using access client using combination of mysql connector and a secure SSL mysql installation. In case anyone needs an internet mysql connection solution.
Jackal is offline   Reply With Quote
Old 04-24-2019, 12:00 PM   #13
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,285
Thanks: 115
Thanked 3,089 Times in 2,807 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: How to use Access with mysql

Post #11 was moderated. This post is to trigger email notifications

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating An Executable (exe) Out Of Microsoft Access (mdb, mde) Tips and Tricks nIGHTmAYOR Modules & VBA 32 02-20-2012 04:27 PM
Access Limitations Vs SQL Server, Oracle Mike375 General 27 11-12-2010 07:07 AM
Command Line Switches KenHigg Access FAQs 0 07-22-2009 05:57 PM
Appending an Excel file to an Access table adrian.stock22 General 7 12-12-2008 02:47 PM
MySQL amd Access Robgould General 2 02-08-2006 04:03 AM




All times are GMT -8. The time now is 08:49 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World