Run Query in Excel 2010 AND 2013 on remote MySQL db

Posted on 2014-08-01
Last Modified: 2014-08-05
ok, so i have a script that creates two tables in memory then populates those tables with a few items from other tables. Those two tables are then merged into one and the output is downloaded to csv then opened and saved to excel. Of course when i close the tool the two tables are removed from memory.

essentially it's a script I run at the end of every month to pull commission data for accounting/sales. I run this script in the MySQL Workbench>Query tool directly on the server. I then email that file to the payable clerk.

I was wondering if there was a way i could have this script run from within Excel on the clerk's computer (read only of course) so they can push a button and have the data instead of waiting for me to do it on the server.
Question by:Brandon
    LVL 19

    Assisted Solution

    by:Ken Butters
    Yes you definitely can....

    It will take some work to get everything set up... but here is a rough outline of the basic steps.

    Depending upon what is already installed on the Clerks computer, it's possible you would have to install a driver for the MySQL database on their machine. (and yours as well if you don't already have it).

    Here is a place to start for getting a mysql odbc driver installed.

    Then when you start writing your macro/VBA code, you will need to add references in the VBA Editor for Microsoft Activex Data Objects 2.8 Library in order to get access to the ADODB references. (In VBA editor goto Tools/References)  --- This step is not strictly required, but if you do it, then when you explicity pull in the  ADODB reference library, you'll get the context sensitve help when you use them.

    This page has good information on connection strings for mysql:

    In VBA within Excel, you can define

    An ADODB Connection to define the connection to your database
    An ADODB recordset to hold the result of your query

    Here is an example (obviously not a working copy w/o a DB to connect to etc)...  of how you might start testing a connection and getting data from your DB....

    Sub test()
        Dim myRecordSet As ADODB.recordset
        Dim conn As ADODB.Connection
        Dim ServerName As String
        Dim DatabaseName As String
        Dim UserID As String
        Dim Password As String
        Dim SqlQuery As String
        Dim ConnectionString As String
        '  All this information would be unique to your DB Connection
        ServerName = "myservername"
        DatabaseName = "myDatabaseName"
        UserID = "myUserID"
        Password = "MyPassword"
        '  define your connection string
        ConnectionString = "Driver={MySQL ODBC 5.3.4 Driver};" & _
                           "Server=" & ServerName & ";" & _
                           "Database=" & DatabaseName & ";" & _
                           "Uid=" & UserID & ";" & _
                           "Pwd=" & Password & ";"
        '  set up a query
        SqlQuery = "select * from sometable"
        Set myRecordSet = New ADODB.recordset
        Set conn = New ADODB.Connection
        '  open the conneciton to the DB
        conn.Open ConnectionString
        '  run your query... records are assigned to an ADODB recordset
        myRecordSet.Open SqlQuery, conn, adOpenStatic
        Dim Field1Value As String
        Dim Field2Value As String
        Do While Not myRecordSet.EOF
            Field1Value = myRecordSet!field1
            Field2Value = myRecordSet!field2
            '----- Here do something with your field values... put them in an excel table maybe?
        Set myRecordSet = Nothing
        Set conn = Nothing
    End Sub

    Open in new window

    LVL 24

    Accepted Solution


    I would suggest you put the script or it's functionality into a stored procedure which can be called from Excel VB stub.
    This would have everything being executed on the server sided and then send the resultset to the client and hence minimize the data flowing back and forth between client and server.

         Tomas Helgi
    LVL 3

    Author Comment

    Ok, so the CFO has decided NOT to allow the clerks to run these reports and would rather continue to have IT do it.

    Thanks for the information, i'm still going to try to get this setup on one of our support staff's computer so i'm not having to have the server opened and running.

    I'll try to split the points for your time.
    LVL 3

    Author Closing Comment

    I have not tested this yet since the plans changed at the last minute. But thank you for your time.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    If you get continual lockouts after changing your Active Directory password, there are several possible reasons.  Two of the most common are using other devices to access your email and stored passwords in the credential manager of windows.
    The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now