How to use Access with mysql (1 Viewer)

AJordan

Registered User.
Local time
Today, 05:24
Joined
Mar 25, 2008
Messages
73
I’ve searched the web and this site and have found out it’s 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 I’ve 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 don’t 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!
 

Banana

split with a cherry atop.
Local time
Today, 02:24
Joined
Sep 1, 2005
Messages
6,318
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.
 

AJordan

Registered User.
Local time
Today, 05:24
Joined
Mar 25, 2008
Messages
73
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:

icemonster

Registered User.
Local time
Today, 04:24
Joined
Jan 30, 2010
Messages
502
this may be a year late but justhost allows remote mysql connections you just need to specify the ip address.
 

bparker1084

New member
Local time
Today, 02:24
Joined
Oct 31, 2012
Messages
9
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.
 

speakers_86

Registered User.
Local time
Today, 05:24
Joined
May 17, 2007
Messages
1,919
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?
 

p.perez

Registered User.
Local time
Today, 02:24
Joined
Jan 29, 2018
Messages
12
Hi everyone,

I´m new in migrating and splitting databases. I´m trying to use postgres but i think that my question will aplly to mysql so i´m 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

Registered User.
Local time
Today, 02:24
Joined
Jan 29, 2018
Messages
12
I found and answer. It is to rename the "public_tbl_users" to "tbl_users". All the forms are working.
 

Jackal

New member
Local time
Today, 02:24
Joined
Feb 1, 2019
Messages
2
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

New member
Local time
Today, 02:24
Joined
Feb 1, 2019
Messages
2
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.
 

isladogs

MVP / VIP
Local time
Today, 09:24
Joined
Jan 14, 2017
Messages
18,186
Post #11 was moderated. This post is to trigger email notifications
 

Users who are viewing this thread

Top Bottom