Execute SQL Query from excel

Question

I'm a bit stuck for a while in a small project to generate results from several sql queries in several excel Sheets, I am trying to use SQL Server 2008 and it's the first time I code VBA I tried this code (for a SQL single query) but I still have compilation problems

Sub New_Feuil1() 
    ThisWorkbook.Activate 

     'First clear the contents from the query
    Worksheets("Feuil1").Select 
    Range("A2").Select 
    Do Until ActiveCell = "" 
        ActiveCell.Offset(1).Select 
    Loop 
    Range("A4", ActiveCell.Offset(-1, 3)).ClearContents 

     'Get reporting date
    ReportingDate = ThisWorkbook.Sheets("Parameters").Range("D1") 

     'Format the value for use in the SQL query
    ReportingDateFor = Format(ReportingDate, "yyyy-mm-dd") 

    Worksheets("Feuil1").Select 
    Range("A1").Select 

    Dim cnn As New ADODB.Connection 
    Dim rst As New ADODB.Recordset 
    Dim StrQuery1 As String 
    Dim ConnectionString As String 

    ConnectionString ="ODBC;" & _
    "Driver={SQL Server Native Client 10.0};" & _
    "Server=187.125.254.231;" & _
    "Database=database;" & _
    "UID=sa; PWD=pwd"
    cnn.Open ConnectionString 
    cnn.CommandTimeout = 900 

     'Queries to be executed
    StrQuery1 = StrQuery1 & "Select Id from Users" 

    rst.Open StrQuery1, cnn, adOpenForwardOnly, adLockReadOnly 
    rst.Close 

    Debug.Print "StrQuery1:"; StrQuery1 
    cnn.Close 

    ThisWorkbook.Sheets("Central Dashboard").Select 
    Sheets("Feuil1").Range("A2").CopyFromRecordset rst

End Sub 

is there any other solution ?

Answer 1

it seems you are new to programming :).. before you use any variables please declare them this will help you to understand them quickly.

like:

Dim ReportingDate as Date
ReportingDate = ThisWorkbook.Sheets("Parameters").Range("D1")

Dim ReportingDateFor As String
ReportingDateFor = Format$(ReportingDate, "yyyy-mm-dd")

also check your connection string. try this connection string.

ConnectionString = "Driver={SQL Server Native Client 10.0};Server=187.125.254.231;Database=database;UID=sa; PWD=pwd"

Apart from that, looking at your code you are connecting to server, opening recordset, closing recordset and finally closing the connection AND THEN trying to retrieve the results. logically this will never work :) :)

try this:

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As ADODB.Command

Dim ConnectionString As String
ConnectionString = "Driver={SQL Server Native Client 10.0};Server=187.125.254.231;Database=database;UID=sa; PWD=pwd"

cnn.Open ConnectionString

'Queries to be executed
Dim StrQuery1 As String
StrQuery1 = StrQuery1 & "Select Id from Users"

'Prepare SQL execution
cmd.Name = "SelectUsers"
cmd.ActiveConnection = conn
cmd.CommandText = StrQuery1

Set rst = cmd.Execute
If Not rst.EOF Then
    With Sheets(1).Cells ' Enter your sheet name and range here
        .ClearContents ' clears the entire sheet
        .CopyFromRecordset rst ' copy the result
    End With
Else
    MsgBox "no records found.."
End If

'After work done close connection
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing