carlAlex
3/14/2017 - 12:39 PM

VBA SQL

VBA SQL

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String

    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=<server>;" & _
                  "Initial Catalog=<database>;" & _
                  "Integrated Security=SSPI;"

    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ' Open the connection and execute.
    conn.Open sConnString

    ' Note the single quotes around the SQL parameters
    my_query = "select * from db.table where date between '@start_date' and '@end_date'"

    ' Left this bit in for example, obviously it may not always apply.
    start_date = Worksheets("settings").range("A2").Value
    num_weeks = Worksheets("settings").range("B2").Value
    end_date = DateAdd("d", (num_weeks * 7) - 1, start_date)

    ' Will need to keep this date formatting, if you're using dates
    start_date = Format(start_date, "yyyy-mm-dd")
    end_date = Format(end_date, "yyyy-mm-dd")

    ' Replace the parameters
    my_query = Replace(my_query, "@start_date", start_date)
    my_query = Replace(my_query, "@end_date", end_date)
    
    ' Spit out the query. Optional for troubleshooting.
    Worksheets("settings").range("A25").Value = my_query

    Set rs = conn.Execute(my_query)
    
    Worksheets("sql_result").Cells.Clear
    
    ' Check we have data.
    If Not rs.EOF Then
        For intColIndex = 0 To rs.Fields.Count - 1
            Worksheets("sql_result").range("A1").Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
        Next

        ' Transfer result.
        Sheets("sql_result").range("A2").CopyFromRecordset rs
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing