phpMyAdmin (MySQL) Connection string(s) for OLEDB Connection (1 Viewer)

[Bilbo_Baggins_Esq]

I have an Access 2013 database I’ve built to assist managing membership for our club.
Currently, all tables (not large) are contained in the same database as the front-end forms.

Ultimately, this database will need to function on multiple laptops and/or tables so I’ve always known and planned to separate the tables from the FE and ultimately to host the tables in the same phpMyAdmin database which powers our website.

I’m familiar with ADODB connections but have always used them to connect to other Microsoft data sources (Access and Excel).

After having completed 99% of the form functionality, I’ve begun the process of migrating the code to use ADODB in a basic way to connect with the tables being in a separate Access DB.

On a parallel development path I MANUALLY added a basic table and some data to our website’s database which went fine.
This parallel objective is to use my new connection manager code to successfully connect to this phpMyAdmin database and pull/edit records to and from this test table.

I’ve studied a few threads found on this forum on the topic of using ADODB to connect to our phpMyAdmin database.

Perhaps in my old age, or inexperience with this type of connection, I may need things spelled out a little more plainly than I’m seeing.

First, what I understand.

1. I understand phpMyAdmin databases are actually MySQL databases.

2. I have visited my old friend https://www.connectionstrings.com/mysql/

3. I have attempted to use the connection string for “Specifying TCP port.”
Though I noticed none of the connection strings on that page include a “Provider:” line (or reference) as my trusty ACE.OLEDB connections.
Resulted in error:
-2147467259: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

4. But then I also find a MySQL OLEDB page on connectiontrings.com that does reference a Provider:
https://www.connectionstrings.com/mysql-oledb-mysqlprov/

5. In another thread (https://www.access-programmers.co.uk/forums/threads/connecting-to-mysql-net-connector.268629/) here (dated from 2014) I found a link to Cherry City Software’s MySQL provider.
Unfortunately, the originally linked page has “moved” and I’ve read the new page in detail
I and have downloaded and installed “MySQL OLE DB Provider (Win64)” provider (file named: MySQLPV(0x64).zip)
Though I get a successfully installed message, it throws an odd window (looks like a CMD Window) telling me to copy/paste a specific URL in a browser.
The URL is on the mysql.com website but throws “Page Not Found” error.
http://www.mysql.com/downloads/mirror.php?id=377979

6. I notice in the connection string referenced on the Cherry City Software page (https://cherrycitysoftware.com/Products/MySqlProvider), there is reference to needing an activation code.
Provider=MySQL Provider; Data Source=MySQLServerIP; User ID =MyID; Password=MyPassword; Initial Catalog=DatabaseName; Activation=TheActivationCode;

"TheActivationCode is a code you obtain from this website"

Though I confess to have become rather spoiled at being able to usually find free stuff of this nature, I’m not completely averse to paying for a license if it works.
However, according to the information on their website, their license is EXPENSIVE and VERY RESTRICTIVE.
Not to mention there doesn’t seem to be any identifiable way or means to select and or pay for any of their software.

7. Using the link on Cherry City’s home page (https://cherrycitysoftware.com/), I found what looks like a free DL for MySQL Provider 5.6 but it seems to be x86 (32-bit). This link downloads the file “mysqlpv_x86_.zip” (https://software.informer.com/search/mysql+OLE+DB+provider)

8. Interestingly, the “MySQL Provider” download page (https://mysql-provider.software.informer.com/) from Cherry City’s home page (referenced at #7) includes a couple of screenshots. One of which is the CMD window I referenced at #5 but has a difference URL to copy/paste (https://dev.mysql.com/downloads/file/?id=377977). This page actually does work and downloads the file “mysql-connector-c-noinsdtall-6.0.2-win32-vs2005.zip”

So I confess to being confused as hell.
Information overload.

I apologize for the length of the above but it also serves to help me sort out my notes and close a ton of seemingly random tabs on my browser window.

Am I missing something here?
Am I over complicating things (as I frequently do).
Do I really have pay for and use an activation code?
I realize this is all older technology. Have things changed since then?
Will the x86 Provider work on my x64 version of Windows 10 and support my x64 installation of Office 2013?

Anything else I need to know?

Please help and Thanks In Advance!

 

[cheekybuddha]

You have posted a lot, and I haven't read all of it thoroughly.

First, you don't necessarily need to convert to ADODB to connect to MySQL - DAO will connect fine.

You will need to get the MyODBC connector installed on every machine that will connect to the remote MySQL instance.

Make sure its bitness matches the bitness of your Access install, not that of the MySQL server.

So if you use 32bit Access, use a 32bit MyODBC connector (even if you are on 64bit Windows), if you use 64bit Access, use a 64bit MyODBC connector.

 

[cheekybuddha]

MyODBC downloads here

 

[cheekybuddha]

Your connection string should look something like:

ODBC;Provider=MSDASQL;DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=127.0.0.1;PORT=3306;DATABASE=cch_01;OPTION=16386;UID=bilbo;PWD=xyz
                                                                       ^             ^              ^                      ^        ^
                                                                       |             |              |                      |        |
                                                                Server address     Server       Database                username  password
                                                                                    port          name

 

[cheekybuddha]

Do I really have pay for and use an activation code?

No

 

[ComputerVersteher]

I presume your website and database are not hosted in your LAN!

Did you consider using a Remote Desktop?

Club membership doesn't sound like heavy multiple user usage, so if only one user at same time is sufficent, a standard Windows PC can be used locally. Connection to that PC can be done by using a VPN (connection from internet to LAN) and RDP.
That will require no changes of your application, "only" configuring a VPN (I can recommend IPFire as Host).

If you need multiple users at same time, you can set up a Terminal Server instead of a standard Windows PC.

Be aware that all services accessible online need to be secured!

 

[Bilbo_Baggins_Esq]

Thanks for the replies!
Sorry for the slow responses.
Apparently I wasn't receiving, or haven't seen, notifications for this thread.


Since this is for a club, all my time is volunteer and on an as-available basis.
I'll go through this and respond back later as or if needed.

Thanks Again!