How to fix syntax error when extracting data from SQL Server into Excel?

[dekk]

When running the full macro (not shown), it delivers an error that '=' incorrect syntax.

The code is trying to extract data from SQL server into Excel

      objMyCmd.CommandText = "SELECT BU, JobDesc FROM Budget27MI_map" _
         & "WHERE BU =" & Worksheets("Sheet1").Range("A1").Value

Suggestions on syntax for parameter query to work?

[Stewart Ross]

You are missing a space between your table name and your WHERE clause (on the next line). The effect of this missing space is to make the WHERE keyword part of your tablename, followed by a dangling 'BU =' part:

... FROM Budget27MI_mapW HERE BU = ...

Add a space after the tablename and see what happens.

objMyCmd.CommandText = "SELECT BU, JobDesc FROM Budget27MI_map " _ 
         & "WHERE BU = " & Worksheets("Sheet1").Range("A1").Value

-Stewart

[dekk]

hI,

I have posted the full code below. I still have the issue after adjusting the syntax for the space between the TABLE and the WHERE clause.

The syntax issue lies at ROW19 when I perform a debug.

Suggestions?

Sub GetDataFromADO()
 
    'Declare variables'
        Dim objMyConn As ADODB.Connection
        Dim objMyCmd As ADODB.Command
        Dim objMyRecordset As ADODB.Recordset
 
        Set objMyConn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset
        
                            
    'Open Connection'
        objMyConn.ConnectionString = "[REMOVED]        objMyConn.Open
        
       'Set and Excecute SQL Command'
         Set objMyCmd.ActiveConnection = objMyConn
         objMyCmd.CommandText = "SELECT BU, JobDesc FROM Budget27MI_map " _
            & "WHERE BU =" & Worksheets("Sheet1").Range("A1").Value
         objMyCmd.CommandType = adCmdText

         
    'Open Recordset'
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open
    
    'Copy Data to Excel'
        Sheet2.Range("A6").CopyFromRecordset objMyRecordset
        For intColIndex = 0 To objMyRecordset.Fields.Count - 1
        Range("A5").Offset(0, intColIndex).Value = objMyRecordset.Fields(intColIndex).Name
        Next

        
              
    'Close Connection to SQL Server
        objMyConn.Close
        Set objMyCmd = Nothing
        Set objMyConn = Nothing
 
End Sub

[Stewart Ross]

What kind of value are you storing in cell A1 of your worksheet?

If there is no value at all at the time then your SQL statement will fail as it will end at the '='.

If the value is non-numeric it will fail, as text strings would need to be enclosed in single quotes in your SQL statement, like this:

& "WHERE BU = '" & Worksheets("Sheet1").Range("A1") & "'"

You could check that all is otherwise working by commenting out line 19 (returning all rows) to ensure that there are no syntax problems except for the WHERE clause.

-Stewart

PS I am assuming you are running this within Excel itself. If you are not, you will need to explicitly qualify all references to the implicit application object (for example when you use the Worksheets collection as in the line above).

[dekk]

Hi Stewart,

[DELETED]

Thanks for your assistance. Much appreciated