• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3558
  • Last Modified:

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.
0
Brandon
Asked:
Brandon
  • 2
2 Solutions
 
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
 
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now