[X]My Assistant

[a_ud]

Some advice needed here. My question is about systems architecture.

My company has a mySQL (large) database on a server. Unfortunately it's a small company and we don't have a company network, only an Internet connexion to the server, i.e. users with rights have access to the server IP through mySQL workbench.

I would like to implement a front-end that connects Access (version 2016) to mySQL server. I'm experienced about linking Access tables (in a network) to SQL Server, using ODBC or similar.

Questions:
-Is it possible to do the same, linking Access 2016 to mySQL database (in the server) using an IP address?

-Does this imply using ODBC (which seems easier to me), a different way to connect, or some other technology (which probably would be a no for me, unless it's very straight forward)?

-If yes, could you summarise the basics steps to set the connexion? I'm no expert in web technologies (Javascripts, html) so clarity is very much appreciated.

Thanks in advance, p.

[cheekybuddha]

-Is it possible to do the same, linking Access 2016 to mySQL database (in the server) using an IP address?
Yes

-Does this imply using ODBC (which seems easier to me), a different way to connect, or some other technology (which probably would be a no for me, unless it's very straight forward)?
Yes, ODBC is what you will use to connect. Download and install MyODBC Connector. If you use 32bit Access make sure you get the 32bit version (even if the machine is 64bit).

-If yes, could you summarise the basics steps to set the connexion? I'm no expert in web technologies (Javascripts, html) so clarity is very much appreciated.
You have 2 methods for creating a connection:
1. Using a DSN.
2. The DSN-less method by specifying the connection parameters in a connection string.

With a DSN, you must set it up it on every computer that will use the Access FE. If this will be a relatively small number, then using a DSN is probably easier. Instructions here

If you will distribute the FE to lots of users then you might find it easier to go DSN-less.

I'm a bit short on time right at this second, but I'll try and post some basic code later.

hth,

d

[a_ud]

Thanks David, that's exactly what I was looking for. And very clear as well.

I downloaded the mySQL ODBC driver, tried to install the 64 bit version (my system is 64) and run into a error 1918.

Obviously nothing to do with Access itself but it the installation didn't complete, even after installing the latest version of Visual C++ distribution (vc_redist.x64.exe).

I'll keep trying but you've pointed me in the right direction.

[cheekybuddha]

Are you using 64bit Access?

[a_ud]

I just found out this morning that it is Access 2016 MSO, 32 bits version. I had installed the 64-bits ones.

I guess this means I need to install the 32 bit mySQL ODBC connector, but no need to uninstall the other one.

Thanks in any case.