MySQL connections

[Freshman]

Hi all,
I've been starting to move my tables over to MySQL and everything is working fine so far. The only question I have is around the steps I follow:

1. I installed the MySQL ODBC drives
2. Then I use the above ODBC to create the connection the the MySQL db
3. Then from within Access I link to the tables in VBA

My question is if I could skip steps 1 and 2 or at least step 2 by doing it in VBA?

As I said I have no problem connecting to the tables but I want to know if I can also create the link to the MySQL database if I provide the Server name etc in my code.

I had a look at DNS-less connections but I can't seem to get my head around creating the link to the MySQL database. None of the sample code seems to do much for me. Or maybe I'm missing a step.

Regards

 

[isladogs]

I would recommend persevering with DSN less connections as that will indeed allow you to skip step 2.
I've never used MySQL but I use it for all SQL Server links instead of ODBC. It also works well with linked Access tables etc
Have a look at https://www.connectionstrings.com/

 

[Freshman]

I would recommend persevering with DSN less connections as that will indeed allow you to skip step 2.
I've never used MySQL but I use it for all SQL Server links instead of ODBC. It also works well with linked Access tables etc
Have a look at https://www.connectionstrings.com/

Hi there,
Thanks for the ref but Google is also my friend - haha. So far I could easily get the samples of the connections strings I'm suppose to use but I can't seem to find code that will simply show me how to create the link.
At the moment I'm looking at writing code to inter the whole thing into the Windows Reg and it almost seems easier...
Still would like to hear from someone who is using MySQL and Access and has a piece of working code at hand instead of me trying to re-invent the wheel

 

[isladogs]

You said you had found plenty of code for DSN less connections so I didn't provide further links.

The code will work for any type of link as you get the connection string info correct. For that reason, I suggested what is widely recognised as the best site for connection string info.

Good luck with your project.

 

[Freshman]

You said you had found plenty of code for DSN less connections so I didn't provide further links.

The code will work for any type of link as you get the connection string info correct. For that reason, I suggested what is widely recognised as the best site for connection string info.

Good luck with your project.

Thanks - Maybe someone will still post some sample code but so far I created the link "manually" as described and then when to look at the Win Reg and created a similar entry in code then deleted the entry and the ODBC link in the MySQL Driver and then ran my code and it created the link very nicely and I could link to the tables. If this works for all versions of Windows (don't thing the Access version plays a roll) then it was a very simple solution indeed.
But still would like to have a look at the more conventional way as I tend to think outside the box...

Here is my code anyway for some comments:
Yes I know the code is still very rough but I only started to test - will clean up the duplication soon. I initially thought I had to first check if the entry exists to stop possible errors but it seems you can safely run it and it will simply re-write it if it exists...??

Public Function Test()
    Dim myWS As Object
    DBName = "002SCS"
    DBDriver = "C:\Program Files\MySQL\Connector ODBC 5.1\myodbc5.dll"
    DBServer = "myserver.net"
    DBPWD = "mypass"
    DBPort = "3306"
    DBSource = "MySQL ODBC 5.1 Driver"
    
    Set myWS = CreateObject("WScript.Shell")
    myWS.regwrite "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\002SCS\DATABASE", DBName, "REG_SZ"
    myWS.regwrite "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\002SCS\Driver", DBDriver, "REG_SZ"
    myWS.regwrite "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\002SCS\PORT", DBPort, "REG_SZ"
    myWS.regwrite "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\002SCS\PWD", DBPWD, "REG_SZ"
    myWS.regwrite "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\002SCS\SERVER", DBServer, "REG_SZ"
    myWS.regwrite "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\002SCS\UID", DBName, "REG_SZ"
    myWS.regwrite "HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources\" & DBName, DBSource, "REG_SZ"
End Function