How can I use Excel to interact with MySQL and show result of an SQL query in a cell, after I enter SQL table id in another cell?

Question

I am trying to figure out how to use Excel as an interactive front end to MySQL. It will be my first VBA experience with a database.

My scenario is I want to enter an order number into one cell, and upon completing the input, I want an SQL query to be ran, like SELECT field1, field2 FROM table WHERE order_number = ?, and then display the return result of field1 in a cell. I may use field2 in other cells.

I see there is some code here that may be useful, but I don't know where to enter that code, and how to make that code work after I enter an order number into the cell. I have already made an ODBC Driver connection to where I am able to connect to a database using Excel Database functions. I don't yet know how to use VBA do make a database connection or run interactive queries.

Can you help get me to the point where I can enter an order number in one cell, and see field1 show up in another cell, where field1 will be a value from an SQL query, like the above?

I have never done that myself. But maybe the following link will help you as a first approach: connectionstrings.com — Zwenn
– Zwenn, Commented Mar 19, 2021 at 14:55

CDP1802 · Accepted Answer · 2021-03-19 17:40:22Z

1

Put code on worksheet where you enter the order number. This uses a DSN created using ODBC Data Source Administrator.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim ar As Variant
    If Target.Address = "$B$2" Then
        ar = GetOrder(Target.Value)
        Range("B4") = ar(0)
        Range("B5") = ar(1)
    End If

End Sub

Function GetOrder(OrderNo As Long) As Variant

    Const CONN = "DSN=***;UID=***;PWD=***;"
 
    Const SQL = " SELECT Field1,Field2" & _
                " FROM table1 " & _
                " WHERE OrderNo = ?"
  
    Dim dbConn As ADODB.Connection, dbCmd As ADODB.Command
    Dim rs As ADODB.Recordset
    Dim param As ADODB.Parameter, n As Long

    Set dbConn = New ADODB.Connection
    dbConn.Open CONN

    Set dbCmd = New ADODB.Command
    With dbCmd
        .ActiveConnection = dbConn
        .CommandType = adCmdText
        .CommandText = SQL
        Set param = .CreateParameter("P1", adInteger, adParamInput, 0)
        .Parameters.Append param
    End With
    
    Set rs = dbCmd.Execute(n, OrderNo)
    If Not rs.EOF Then
       GetOrder = Array(rs(0).Value, rs(1).Value)
    Else
       GetOrder = Array("#N/A", "#N/A")
       MsgBox "Could not find " & OrderNo, vbExclamation, "Error"
    End If
   
    dbConn.Close

End Function

answered Mar 19, 2021 at 17:40

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dennis Over a year ago

Thanks! How would I add multiple items to the list? I'm having trouble figuring that out. I was able to populate GetOrder by using GetOrder = rs.GetRows, but having trouble figuring out how to populate the list. Do I use a For loop or is there a better way?

CDP1802 Over a year ago

@dennis What do you mean, which list ? If you want the results on a sheet there is the copyfromrecordset method

Dennis Over a year ago

oh I apologize! I mixed your answer up with another answer. I am trying to populate a ComboBox (a drop-down list) with results received from rs

CDP1802 Over a year ago

@dennis If it's a single field then Me.ComboBox1.List = Application.Transpose(rs.GetRows)

Collectives™ on Stack Overflow

How can I use Excel to interact with MySQL and show result of an SQL query in a cell, after I enter SQL table id in another cell?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related