use excel for sql query

[MetLife]

Hi,

I have a sql query, can this be run in excel? Any advice would be appreciated!!!

Drop table [V200_06302023]

Select [MPOLNO]
,[MREC]
,[MACTP]
,[MPRFT]
,[MCOMP]
,[MPOLCT]
,[MSTMIR]
,[MGPMIR]
,[PBR_INDICATOR]
Into [Axis].[dbo].[V200_06302023]
From [Axis].[dbo].[tgt_V200-BCVUL_MISC_V200-20230630-20230630154132]
Where MREC = 1

 

[rpaulson]

take the time to watch this. well worth it, i use it all the time.

VBA - working with data videos | Excel VBA Part 56.1 - Connect to SQL Server using ADO

www.wiseowl.co.uk

 

[Candyman8019]

Here's the script I typically use to run a SQL query in Excel. The actual query and database connection details are entered on the spreadsheet.

Private Sub RunSQLUpdate()
Dim constr As String
Dim conn As Object
Dim strSQL As String
Dim rs As Object
Sheet10.Range("A12:ZZ1048000").ClearContents
On Error Resume Next

If InStr(1, LCase(Sheet10.Range("A9").Value), "update") > 0 Then
    If MsgBox("Are you sure you want to update the database?", vbYesNo) = vbNo Then Exit Sub
End If

    ' OPEN CONNECTION '
    constr = "provider=sqloledb;Data Source=" & Sheet10.Range("DataSource").Value & ";initial catalog=" & Sheet10.Range("InitCat").Value & ";" & Sheet10.Range("DBSecurity").Value
      
    strSQL = Sheet10.Range("SQLStmt").Value
      
    Set conn = CreateObject("ADODB.Connection")
    conn.Open constr

    

    ' EXECUTE QUERY '
    'conn.Execute strSQL

    'Print result starting in cell C9
    
    Set rs = CreateObject("ADODB.RECORDSET")
    rs.activeconnection = conn
    rs.Open strSQL
      Sheet10.Range("A12").CopyFromRecordset rs

    ' CLOSE CONNECTION '
    conn.Close
    rs.Close
    Set conn = Nothing

    MsgBox "Transaction complete!", vbInformation
End Sub