Run Query in Excel 2010 AND 2013 on remote MySQL db

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.
LVL 3
BrandonProject Manager, IT Systems and Software DesignAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ken ButtersCommented:
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.
http://dev.mysql.com/downloads/connector/odbc/

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: https://www.connectionstrings.com/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?
        
        myrecocordset.MoveNext
        
    Loop
    
    myRecorset.Close
    Set myRecordSet = Nothing
    conn.Close
    Set conn = Nothing

End Sub

Open in new window

0
Tomas Helgi JohannssonCommented:
Hi!

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.

http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

Regards,
     Tomas Helgi
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrandonProject Manager, IT Systems and Software DesignAuthor Commented:
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.
0
BrandonProject Manager, IT Systems and Software DesignAuthor Commented:
I have not tested this yet since the plans changed at the last minute. But thank you for your time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.