locked
Pass Through Query to SQL server DB RRS feed

  • Question

  • Hi

    I am using excel2007 for reporting & SQL Server express as back end-

    In access I can run a "pass through query" to run a procedure in the sql db-(it imports data- manupliates it and summarises it).

     I would prefer if this could be run from excel where the final data is displayed-

     Does anyone know if this is possible

     

    Thanks

    Saturday, January 7, 2012 10:52 AM

Answers

  • You need a reference to this:

    Microsoft ActiveX Data Objects 2.8 Library

     

    Also, make sure your SProc is spelled correct; i.e., it may contain this:  dbo.

    "Exec dbo.CustOrdersOrders"

     

    • Marked as answer by David_1234 Monday, January 16, 2012 2:12 PM
    Monday, January 9, 2012 6:37 PM
  • See if this works

     

    1) First add the reference library for Access to you Excel VBA Project from the VBA menu

    Tools - References Access Library

    Microsoft Access XX.X object Library  (use latest version on your PC, or older version if you want to work with older version of access)

    If you are using ADO method also add this reference library

    Microsoft ActiveX Data Object 2.8 Library

    2) Use the Execute method to run the query after make the connection to the access database.  I copied the code below from the Access VBA Help and it shoud run in Excel.  the code compiled with any errors in Excel VBA adding the two reference libraries.

     

    Sub test()
    
        Dim strCnn As String
        Dim con As ADODB.Connection
        Dim cmdChange As ADODB.Command
        Dim rstTitles As ADODB.Recordset
        Dim errLoop As ADODB.Error
    
    
        strCnn = "Provider=SQLOLEDB;Data Source=DAVIDMEEHAN\SQLEXPRESS;Initial Catalog=Summa_Project;Integrated Security=SSPI;"
    
        con.Open "Provider=SQLOLEDB;Data Source=DAVIDMEEHAN\SQLEXPRESS;Initial Catalog=Summa_Project;Integrated Security=SSPI;"
    
     
    
        Set rst = cnn1.Execute("exec dbo.TS_Import_Process")
    
         'Results of SProc are returned to Cell A1
         'ActiveSheet.Range("A1").CopyFromRecordset rst
    
        rst.Close
        con.Close
    
    
    
    End Sub
    
    

     


    jdweng
    • Marked as answer by David_1234 Monday, January 16, 2012 2:12 PM
    Monday, January 16, 2012 1:05 PM

All replies

  • Most VBA code and Qeries can be moved from Access to Excel with little or no changes.  Normally you just have to use an Access Application.  You can also use the Excel worksheet menu Data - Import to add a query to your worksheet.

    1) First add the reference library for Access to you Excel VBA Project from the VBA menu

    Tools - References Access Library

    Microsoft Access XX.X object Library  (use latest version on your PC, or older version if you want to work with older version of access)

    If you are using ADO method also add this reference library

    Microsoft ActiveX Data Object 2.8 Library

    2) Use the Execute method to run the query after make the connection to the access database.  I copied the code below from the Access VBA Help and it shoud run in Excel.  the code compiled with any errors in Excel VBA adding the two reference libraries.

     

    Execute, Requery, and Clear Methods Example (VB)
    
    
    This example demonstrates the Execute method when run from both a Command object and a Connection object. It also uses the Requery method to retrieve current data in a Recordset, and the Clear method to clear the contents of the Errors collection. (The Errors collection is accessed via the Connection object of the ActiveConnection property of the Recordset.) The ExecuteCommand and PrintOutput procedures are required for this procedure to run.
    
    Public Sub ExecuteX()
    
        Dim strSQLChange As String
        Dim strSQLRestore As String
        Dim strCnn As String
        Dim cnn1 As ADODB.Connection
        Dim cmdChange As ADODB.Command
        Dim rstTitles As ADODB.Recordset
        Dim errLoop As ADODB.Error
    
        ' Define two SQL statements to execute as command text.
        strSQLChange = "UPDATE Titles SET Type = " & _
            "'self_help' WHERE Type = 'psychology'"
        strSQLRestore = "UPDATE Titles SET Type = " & _
            "'psychology' WHERE Type = 'self_help'"
    
        ' Open connection.
            strCnn = "Provider=sqloledb;" & _
            "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
        Set cnn1 = New ADODB.Connection
        cnn1.Open strCnn
    
        ' Create command object.
        Set cmdChange = New ADODB.Command
        Set cmdChange.ActiveConnection = cnn1
        cmdChange.CommandText = strSQLChange
        
        ' Open titles table.
        Set rstTitles = New ADODB.Recordset
        rstTitles.Open "titles", cnn1, , , adCmdTable
    
        ' Print report of original data.
        Debug.Print _
            "Data in Titles table before executing the query"
        PrintOutput rstTitles
    
        ' Clear extraneous errors from the Errors collection.
        cnn1.Errors.Clear
    
        ' Call the ExecuteCommand subroutine to execute cmdChange command.
        ExecuteCommand cmdChange, rstTitles
        
        ' Print report of new data.
        Debug.Print _
            "Data in Titles table after executing the query"
        PrintOutput rstTitles
    
        ' Use the Connection object's execute method to 
        ' execute SQL statement to restore data. Trap for 
        ' errors, checking the Errors collection if necessary.
        On Error GoTo Err_Execute
        cnn1.Execute strSQLRestore, , adExecuteNoRecords
        On Error GoTo 0
    
        ' Retrieve the current data by requerying the recordset.
        rstTitles.Requery
    
        ' Print report of restored data.
        Debug.Print "Data after executing the query " & _
            "to restore the original information"
        PrintOutput rstTitles
    
        rstTitles.Close
        cnn1.Close
        
        Exit Sub
        
    Err_Execute:
    
        ' Notify user of any errors that result from
        ' executing the query.
        If rstTitles.ActiveConnection.Errors.Count >= 0 Then
            For Each errLoop In rstTitles.ActiveConnection.Errors
                MsgBox "Error number: " & errLoop.Number & vbCr & _
                    errLoop.Description
            Next errLoop
        End If
        Resume Next
    
    End Sub
    
    Public Sub ExecuteCommand(cmdTemp As ADODB.Command, _
        rstTemp As ADODB.Recordset)
    
        Dim errLoop As Error
        
        ' Run the specified Command object. Trap for 
        ' errors, checking the Errors collection if necessary.
        On Error GoTo Err_Execute
        cmdTemp.Execute
        On Error GoTo 0
    
        ' Retrieve the current data by requerying the recordset.
        rstTemp.Requery
        
        Exit Sub
    
    Err_Execute:
    
        ' Notify user of any errors that result from
        ' executing the query.
        If rstTemp.ActiveConnection.Errors.Count > 0 Then
            For Each errLoop In Errors
                MsgBox "Error number: " & errLoop.Number & vbCr & _
                    errLoop.Description
            Next errLoop
        End If
        
        Resume Next
    
    End Sub
    
    Public Sub PrintOutput(rstTemp As ADODB.Recordset)
    
        ' Enumerate Recordset.
        Do While Not rstTemp.EOF
            Debug.Print "  " & rstTemp!Title & _
                ", " & rstTemp!Type
            rstTemp.MoveNext
        Loop
    
    End Sub
    
    
    

     

     

     

     


    jdweng
    Saturday, January 7, 2012 1:11 PM
  • If you want to control SQL Server Express from Excel, just run code like this (2 ways to run an S-Proc from Excel):

    Sub Working()

    Dim con As Connection
    Dim rst As Recordset

    Set con = New Connection
    con.Open "Provider=SQLOLEDB;Data Source=(local)\SQLExpress;Initial Catalog=Northwind;Integrated Security=SSPI;"

    Set rst = con.Execute("Exec dbo.[CustOrderHist]" & "'" & ActiveSheet.Range("E1").Text & "'")
    'Results of SProc are returned to Cell A1
    ActiveSheet.Range("A1").CopyFromRecordset rst

    rst.Close
    con.Close
    End Sub

    ********************************************************************

    Sub Working2()

    Dim con As Connection
    Dim rst As Recordset
    Dim strConn As String

    Set con = New Connection
    strConn = "Provider=SQLOLEDB;"
    strConn = strConn & "Data Source=(local)\SQLExpress;"
    strConn = strConn & "Initial Catalog=Northwind;"
    strConn = strConn & "Integrated Security=SSPI;"

    con.Open strConn

    'Put a country name in Cell E1
    Set rst = con.Execute("Exec dbo.CustOrdersOrders '" & ActiveSheet.Range("E1").Text & "'")

    'The total count of records is returned to Cell A5
    ActiveSheet.Range("A5").CopyFromRecordset rst

    rst.Close
    con.Close

    End Sub

    ********************************************************************

    Import data from Excel into SServer:

    Sub ADOExcelSQLServer()
        
        Dim Cn As ADODB.Connection
        Dim Server_Name As String
        Dim Database_Name As String
        Dim User_ID As String
        Dim Password As String
        Dim SQLStr As String
        Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset
        
        Server_Name = "LAPTOP\SQL_EXPRESS" ' Enter your server name here
        Database_Name = "Northwind" ' Enter your  database name here
        User_ID = "" ' enter your user ID here
        Password = "" ' Enter your password here
        SQLStr = "SELECT * FROM Orders" ' Enter your SQL here
        
        Set Cn = New ADODB.Connection
        Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
        ";Uid=" & User_ID & ";Pwd=" & Password & ";"
        
        rs.Open SQLStr, Cn, adOpenStatic
       
        With Worksheets("Sheet1").Range("A2:Z500")
            .ClearContents
            .CopyFromRecordset rs
        End With
       
        rs.Close
        Set rs = Nothing
        Cn.Close
        Set Cn = Nothing
    End Sub


     


    • Edited by ryguy72 Monday, January 9, 2012 12:18 AM
    Monday, January 9, 2012 12:17 AM
  • Thanks for the reply- I was trying the resonce from Ryguy72 but i'm coming up with a complie error- user defined type not defined on row 1

    for the statement below

    The procedure I want to run is called "Project_No_Source"- and as there isn't any output back to excel I put a ' infront of the active sheet range bit- and idea where I'm going wrong?

    Thanks again

     

    Dim con As Connection
    Dim rst As Recordset

    Set con = New Connection

    con.Open "Provider=SQLOLEDB;Data Source=DAVIDMEEHAN\SQLEXPRESS;Initial Catalog=Summa_Project;Integrated Security=SSPI;"

     

    Set rst = con.Execute("exec Project_No_Source")
    'Results of SProc are returned to Cell A1
    'ActiveSheet.Range("A1").CopyFromRecordset rst

    rst.Close
    con.Close
    End Sub

    Monday, January 9, 2012 3:02 PM
  • You need a reference to this:

    Microsoft ActiveX Data Objects 2.8 Library

     

    Also, make sure your SProc is spelled correct; i.e., it may contain this:  dbo.

    "Exec dbo.CustOrdersOrders"

     

    • Marked as answer by David_1234 Monday, January 16, 2012 2:12 PM
    Monday, January 9, 2012 6:37 PM
  • Ryguy72

    Really sorry about this- but I get an "Invalid Outside procedure" highlighting the "Set" command- any idea what I should do to be able to run it?

     

    Thanks ahain

    David

     

    Dim con As Connection
    Dim rst As Recordset

    Set con = New Connection

    con.Open "Provider=SQLOLEDB;Data Source=DAVIDMEEHAN\SQLEXPRESS;Initial Catalog=Summa_Project;Integrated Security=SSPI;"

     

    Set rst = con.Execute("exec dbo.TS_Import_Process")
    'Results of SProc are returned to Cell A1
    'ActiveSheet.Range("A1").CopyFromRecordset rst

    rst.Close
    con.Close
    End Sub

    Monday, January 16, 2012 12:42 PM
  • See if this works

     

    1) First add the reference library for Access to you Excel VBA Project from the VBA menu

    Tools - References Access Library

    Microsoft Access XX.X object Library  (use latest version on your PC, or older version if you want to work with older version of access)

    If you are using ADO method also add this reference library

    Microsoft ActiveX Data Object 2.8 Library

    2) Use the Execute method to run the query after make the connection to the access database.  I copied the code below from the Access VBA Help and it shoud run in Excel.  the code compiled with any errors in Excel VBA adding the two reference libraries.

     

    Sub test()
    
        Dim strCnn As String
        Dim con As ADODB.Connection
        Dim cmdChange As ADODB.Command
        Dim rstTitles As ADODB.Recordset
        Dim errLoop As ADODB.Error
    
    
        strCnn = "Provider=SQLOLEDB;Data Source=DAVIDMEEHAN\SQLEXPRESS;Initial Catalog=Summa_Project;Integrated Security=SSPI;"
    
        con.Open "Provider=SQLOLEDB;Data Source=DAVIDMEEHAN\SQLEXPRESS;Initial Catalog=Summa_Project;Integrated Security=SSPI;"
    
     
    
        Set rst = cnn1.Execute("exec dbo.TS_Import_Process")
    
         'Results of SProc are returned to Cell A1
         'ActiveSheet.Range("A1").CopyFromRecordset rst
    
        rst.Close
        con.Close
    
    
    
    End Sub
    
    

     


    jdweng
    • Marked as answer by David_1234 Monday, January 16, 2012 2:12 PM
    Monday, January 16, 2012 1:05 PM
  •  

    Ryguy72 / JDweng

    Thanks for your help- needed to ref the access library then the original code from Ryguy72 worked perfectly

    Thanks again- David

    Dim con As Connection
    Dim rst As Recordset

    Set con = New Connection

    con.Open "Provider=SQLOLEDB;Data Source=DAVIDMEEHAN\SQLEXPRESS;Initial Catalog=Summa_Project;Integrated Security=SSPI;"

     

    Set rst = con.Execute("exec dbo.TS_Import_Process")
    'Results of SProc are returned to Cell A1
    'ActiveSheet.Range("A1").CopyFromRecordset rst

    'rst.Close
    con.Close
    End Sub

    Monday, January 16, 2012 2:11 PM