+ Reply to Thread
Results 1 to 2 of 2

Newbie to connecting to Mariadb through excel VBA

  1. #1
    Registered User
    Join Date
    08-05-2016
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    9

    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

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,070

    Re: Newbie to connecting to Mariadb through excel VBA

    If you want to just pull the data (not update it), then MS-Query should work for you. MS-Query should recognize any ODBC connection. I recommend turning off the Wizard, it only seems to get in the way.

    I also recommend starting with a very basic query: one that runs quickly and returns no more than a moderate amount of data. For example, select a table in the database drag down one column and maybe put a filter on it for yesterday's date.

    Once you have the connection established, you now have a "template" for that database. You can write the most complicated queries you can think of and copy and paste them into the command string. I usually use something like TOAD or WinSQL to work out the query and then copy and paste it into the command string.

    No coding required.

    If the query is simple enough, it can be presented graphically and you can pass parameters at run time either by prompt or reading it from a cell on the spreadsheet. If the query is complicated and cannot be presented graphically, then you can't pass parameters at run time, however, I have a "system" to rewrite the SQL code "on the fly."

    If you need more help on MS-Query. Give me a shout.

    Here is an article that will give you an idea of how MS-Query works: http://www.utteraccess.com/wiki/index.php/MS_Query
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Connecting Avature CRM to Excel
    By runthehalls in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-22-2017, 03:14 PM
  2. Connecting Excel with IE
    By Learning88 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-17-2014, 07:47 AM
  3. Connecting SAP and Sharepoint From Excel
    By reachharry in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-21-2013, 02:59 AM
  4. VPN Connecting Excel
    By omnipotent in forum Excel General
    Replies: 1
    Last Post: 02-10-2009, 05:23 AM
  5. [SOLVED] Connecting To a Excel Database
    By NOODLES101487 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-14-2005, 10:05 AM
  6. Excel connecting to Teradata
    By Bill in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-31-2005, 04:06 PM
  7. Connecting to SQL from Excel
    By Keith La Force in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-02-2005, 10:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1