none
Displaying SQL Results RRS feed

  • Question

  • This may be very simple but I am more of a SQL report writer than anything else so please humor me and keep it fairly simple.  

    I have an old excel spreadsheet that is pulling specific pieces of data and putting it is specific places (which is great) when a button is clicked. 

    I need this spreadsheet to instead dump all SQL results into the workbook.  I want all the rows and all the columns from my report to display instead of just selected parts.  Below is the code that is found in Visual Basic.  Please help!  

    Again, I have a feeling that what I am looking for is a lot simpler than what I have below but I am so unfamiliar with this coding that I can't seem to divine the answer.  Thanks!  

     

    Private Sub cmdGetData_Click()

        Dim c As ADODB.Connection

        Dim r As ADODB.Recordset

        Dim sq As String

        Set c = New ADODB.Connection

        Set r = New ADODB.Recordset

     

        c.Open "Provider=SQLOLEDB;Server=XXXXXXX;Database=XXXX;Uid=XXXXXX;Pwd=XXXXXX;"

     

        sq = "select * from vmfg..EMPLOYEE"

        Set r = c.Execute(sq)

     

        If Not r.EOF Then

            Sheet1.Unprotect

            Sheet1.Cells(2, 2) = r!FIRST_NAME

            Sheet1.Cells(3, 2) = r!LAST_NAME

            Sheet1.Cells(4, 2) = r!DEPARTMENT_ID

            Sheet1.Cells(5, 2) = r!Active

            Sheet1.Cells(1, 5) = r!BASE_PAY_RATE

            Sheet1.Protect

        End If

     

    End Sub

    Wednesday, September 28, 2011 6:48 PM

Answers

All replies