SQL Query Excel 2010

[Turtleman10]

I've go this code that I think will get me part way to what I am trying to do but I need to do more. I need to look for a field in a coulumn and it looks like this code will give me everything in the column. cam I correct?

Sub GetDataFromSQLServer()

    Dim conn                    As Object
    Dim recS                    As Object
    Dim strQuery                As String
    Set conn = CreateObject("ADODB.Connection")
    Set recS = CreateObject("ADODB.Recordset")
    With conn
        .Provider = "sqloledb"
        .ConnectionString = "Data Source=CRV43;Initial Catalog=M2MData1;Integrated Security=SSPI;"
        .Open
    End With
    strQuery = "Orders"
    
    recS.Open strQuery, conn

    ActiveSheet.Range("A1").CopyFromRecordset (recS)
    
    recS.Close
    conn.Close
    Set recS = Nothing
    Set conn = Nothing
End Sub

 

[kjv1611]

So when you run this, are you getting the results from a SQL stored procedure?

If you're wanting to get specific results, you should write a SQL Query first that gives you what you want, and then plug that in place of Orders.

Also, if this is a straight-forward query, one that wouldn't need to be edited, you could do it another way without using VBA at all.. I personally use this method all the time:

[ol 1]
[li]On the ribbon. select Data tab[/li]
[li]Select From Other Sources[/li]
[li]Select From SQL Server[/li]
[li]Type the name of your SQL server, and click Next[/li]
[li]Select your database[/li]
[li][highlight]UNCHECK[/highlight] Connect to a specific table[/li]
[li]Click Next (don't worry about what table is selected)[/li]
[li]Click Next on the next screen unless you have a reason to change anything there - I never do[/li]
[li]Click Finish[/li]
[li]If you get a prompt, "The file..... already exists, do you want to replace the existing file?" Just clck Yes[/li]
[li]Select a table - any table, again, doesn't matter, and click OK[/li]
[li]Click Poperties[/li]
[li]Click the Definition tab[/li]
[li]Change Command type to SQL[/li]
[li]Clear out anything in the Command text box, and paste your SQL code here.[/li]
[li]Click OK[/li]
[li]Click Yes on next prompt[/li]
[li]Click OK when prompted for location unless you want to first specify a location other than the previously selected location[/li]
[/ol]

Now, you can save that, and whenever you need the data refreshed, just go to Data - Refresh All, and you'll be set.

I realize that may look like a lot, but it really can be done in a matter of seconds, once you already have your SQL code setup.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[vbajock]

If there is a table called "orders" you will get the table with all fields if that is what you are asking. If that is not what you want, you need to read up on the SQL "SELECT" statement and then add some more to your open statement, like this:



Dim conn As Object
Dim recS As Object
Dim strQuery As String
Set conn = CreateObject("ADODB.Connection")
Set recS = CreateObject("ADODB.Recordset")
With conn
.Provider = "sqloledb"
.ConnectionString = "Data Source=CRV43;Initial Catalog=M2MData1;Integrated Security=SSPI;"
.Open
End With


strQuery = "SELECT SomeFieldName, SomeOtherFieldName, EvenAnotherFieldName FROM Orders"

recS.Open strQuery, conn, adOpenDynamic, adLockOptimistic, adCmdText

ActiveSheet.Range("A1").CopyFromRecordset (recS)

recS.Close
conn.Close
Set recS = Nothing
Set conn = Nothing