How to write WHERE clause when importing data from MySQL using VBA

Brutusar

Board Regular
Joined
Nov 23, 2019
Messages
166
Office Version
  1. 365
Platform
  1. Windows
Hi, I am using VBA to connect to a MySQL database and import data. The data is used as variables in further VBA processing. However, I am not able to figure out how to write "WHERE" clauses in the VBA query. I need 4 different WHERE clauses to get the correct data.

Does anyone have any idea how to do this?

This is the VBA code I am using:



VBA Code:
Public Sub GetData_from_SQLServerDatabase_with_VBA_Excel()
 Dim strConnectionString As String, strQuery As String, strMessage As String
 Dim objConn As ADODB.Connection
 Dim rsData As ADODB.Recordset

 Dim varArrayReader As Variant
 Dim intLBoundColumn As Integer, intUBoundColumn As Integer ' this variable is meant to hold the lower and upper indexes of the 1st dimension of the Array (columns)
 Dim intLBoundRow As Integer, intUBoundRow As Integer  ' this variable is meant to hold the lower and upper indexes of the 2nd dimension of the Array (rows)
 
 Dim i As Integer, j As Integer
 
 strConnectionString = "Provider=SQLOLEDB;Data Source=your-database-server;Initial Catalog=your-initial-catalog;User ID=your-database-user;Password=your-database-password"
        
    strQuery = ""
    strQuery = strQuery & "SELECT " & vbCrLf
    strQuery = strQuery & " 'yourschema.tableid.column_1' AS [First column], " & vbCrLf
    strQuery = strQuery & " " & vbCrLf
    strQuery = strQuery & " 'yourschema.tableid.column_2' AS [Second column], " & vbCrLf
    strQuery = strQuery & " " & vbCrLf
    strQuery = strQuery & " CASE 'yourschema.tableid.column_3' " & vbCrLf
    strQuery = strQuery & "     WHEN '9' THEN 'whatever here' " & vbCrLf
    strQuery = strQuery & "     WHEN '8' THEN 'whatever here' " & vbCrLf
    strQuery = strQuery & "     WHEN '7' THEN 'whatever here' " & vbCrLf
    strQuery = strQuery & "     WHEN '6' THEN 'whatever here' " & vbCrLf
    strQuery = strQuery & "     WHEN '4' THEN 'whatever here' " & vbCrLf
    strQuery = strQuery & "     WHEN '3' THEN 'whatever here' " & vbCrLf
    strQuery = strQuery & "     WHEN '1' THEN 'whatever here' " & vbCrLf
    strQuery = strQuery & "     ELSE  'whatever here' " & vbCrLf
    strQuery = strQuery & " END AS [Third column], " & vbCrLf
    strQuery = strQuery & " " & vbCrLf
    strQuery = strQuery & " " & vbCrLf
    strQuery = strQuery & " 'yourschema.tableid.column_4' AS [Fourth column], " & vbCrLf
    strQuery = strQuery & " " & vbCrLf
    strQuery = strQuery & " 'yourschema.tableid.column_5' AS [Fifth column], " & vbCrLf
    strQuery = strQuery & " " & vbCrLf
    strQuery = strQuery & "  'yourschema.tableid.column_6' AS [Sixth column] " & vbCrLf
    strQuery = strQuery & " FROM  'yourschema.tableid" & vbCrLf
  
    
    Set objConn = New ADODB.Connection
    objConn.Open strConnectionString
    Set rsData = New ADODB.Recordset
    rsData.Open strQuery, objConn
    
    varArrayReader = rsData.GetRows
    intLBoundColumn = LBound(varArrayReader, 1)
    intUBoundColumn = UBound(varArrayReader, 1)
    intLBoundRow = LBound(varArrayReader, 2)
    intUBoundRow = UBound(varArrayReader, 2)
    
    With ThisWorkbook.Sheets(1) 'Sheets(1) means the first Worksheet in the Excel Workbook
        .Activate
        'Rows("2:" & Rows.Count).ClearContents 'varianta in care Range-ul este convertit in Table. Durata mare de executie a extragerii de date din baza de date !!!! :(
        .Cells.Clear
    End With
    
    For i = 0 To intUBoundRow
       With ActiveSheet
         .Range("A" & i + 2).Value = varArrayReader(0, i)  'Display Column 1
         .Range("B" & i + 2).Value = varArrayReader(1, i)  'Display Column 2
         .Range("C" & i + 2).Value = varArrayReader(2, i) 'Display Column 3
         .Range("D" & i + 2).Value = varArrayReader(3, i) 'Display Column 4
         .Range("E" & i + 2).Value = varArrayReader(4, i) 'Display Column 5
         .Range("F" & i + 2).Value = varArrayReader(5, i) 'Display Column 6
       End With
    Next
    
    With ActiveSheet
        .Range("A1").Value = "Header Column 1"
        .Range("A1").Font.Bold = True
        .Range("A1").Font.Size = 12
        
        .Range("B1").Value = "Header Column 2"
        .Range("B1").Font.Bold = True
        .Range("B1").Font.Size = 12
        
        .Range("C1").Value = "Header Column 3"
        .Range("C1").Font.Bold = True
        .Range("C1").Font.Size = 12
        
        .Range("D1").Value = "Header Column 4"
        .Range("D1").Font.Bold = True
        .Range("D1").Font.Size = 12
        
        .Range("E1").Value = "Header Column 5"
        .Range("E1").Font.Bold = True
        .Range("E1").Font.Size = 12
        
        .Range("F1").Value = "Header Column 6"
        .Range("F1").Font.Bold = True
        .Range("F1").Font.Size = 12     
    End With
    
    
    strMessage = MsgBox("I have extracted the data from SQL Database", vbOKOnly, "Succes!")
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
The WHERE clause defines the criteria for which data to pull out of the tables. We cannot give you a WHERE clause without knowing the criteria you want to use.
 
Upvote 0
Hi, What I need to do is to run the macro I have pasted above to extract data from the database, and use the data as variables in the next macro. What I need to get from the database is:

Col A: CustomerId (WHERE CustomerID = 1234 )
Col B: Type (WHERE Type = AB )
Col C: IDNR (WHERE IDNR = 555 )

And so on.
 
Upvote 0
Where are the 1234/B/555 values coming from? I assume that since you are selecting from one table that these columns are all in that table. You are going to need something like

VBA Code:
    strQuery = strQuery & " WHERE CustomerID = " & custid & " AND Type = '" & type & "' AND IDNR = " & IDNR

where custid, type, and IDNR are variables
 
Upvote 0
Solution

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top