Newbie to connecting to Mariadb through excel VBA

Hi,

This is my first post!

I'm at least an intermediate in Excel VBA and SQL scripting. I've manage to successfully set up a mariadb database on my Synology NAS. I've set up some tables on it and user and I've successfully connected to it via Heidi SQL and Set up an ODBC connection through excel to pull information through an excel Pivot table.

So I know my DB is working and I can connect to it.

Problem is I'm looking to find a way to update the DBs and create custom function to pull from the DB on the Synology NAS via excel (already have some spreadsheet tools built, just need to plumb in a data connection).

I'm open to suggestions on how best to accomplish this, in the past I have used a excel connection file in VBA which someone wrote for me. and written script to pull from a Microsoft SQL database I've google a bit and I can roughly follow the code I need to write, but I'm still kinda in the dark.

What would be my best option to automate some scraping queries and writing the into the DB?

Assuming I'm using excel and VBA the connection file I need will be along the lines below right?


Sub sbADOExample()
Dim sSQLSting As String
Dim ReturnArray
Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath As String, sconnect As String


DBPath ="C:\InputData.xlsx" <-- What is the correct path to the DB on the synology nas? is it "xxx.xxx.xxx.xxx:3306\DBName?"

sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';" <-- Are there multiple ways of achieving this? Google is telling me to use Microsoft.Ace.something, one requires the ActiveXControls 2.8 another does what are the pros/cons of each?


Conn.Open sconnect

sSQLSting = "SELECT * From [Sheet1$]" ' Your SQL Statement (Table Name= Sheet Name=[Sheet1$])

mrs.Open sSQLSting, Conn

'Load the Data into an array
ReturnArray = mrs.GetRows

mrs.Close
Conn.Close
End Sub