Access (front-end) connecting to mySQL (back-end)

[a_ud]

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

My company has a mySQL large database that lives 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.

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 the 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.

 

[The_Doc_Man]

Is this a single-user case (perhaps so you can do some data mining) or were you planning to share your database front-end among multiple users? For the single-user case, you could edit your own machine's HOSTS file in the ...\etc\ folder where your network stack is kept and just add a local alias that points to the machine, then use a traditional connection string as shown in my first example below. For simple UNC pathing, the string might look like

\\servername.subdomain...domain\directory-share-name\sub-directory...\file

For the IP case for an in-house sub-net, syntax SHOULD be something like

\\192.168.nnn.nnn\directory-share-name\sub-directory...\file

ODBC is probably still OK. If you were familiar with SQL/ODBC from other experience, this should be quite similar.

However, a question does occur to me. Is this a true Internet connection or is it an INTRAnet connction? As long as the server is in-house and you can see it on your local sub-net, this won't be a problem.

If this server is NOT local to your in-house net, however, you face a more daunting task because external network connections to a server are problematic and subject to connection interruptions - which KILLS most Access apps dead on first loss.

 

[a_ud]

It's (mainly) for one user.

I've gone for ODBC, and seems to be working now that MS Visual C++ is installed, which is what I wanted.

Still I'll keep the details for the alternative method saved. Many Thanks !