SQL Named Variables in VBA

Question

I have a query that's run in Excel, with a variable parameter that can be changed in the workbook.

This is referenced several times throughout the SQL statement, and so I have declared a variable (@Days) before the Select, and then referenced this throughout the query.

This works fine in management studio.

If I try to execute this in VBA, I get Application-defined or object defined error.

If I uncomment my clipboard code, and paste the result into management studio, it executes no problem. If I stop the VBA before .Refresh, and manually refresh the query it refreshes fine.

VBA is below:

Sub Refresh()


Dim Days As String
Dim SQLStat As String
Dim RChar As Integer

Days = Sheets("Data").Range("A1").Value

' Gets current Query, and strips off declaration
SQLStat = ActiveWorkbook.Connections("Query").ODBCConnection.CommandText
SQLStat = Right(SQLStat, (Len(SQLStat) - (InStr(1, SQLStat, "Select") - 1)))

' Adds Declaration with new days value.
SQLStat = "Declare @Days integer set @Days = " & Days & " " & Chr(13) & SQLStat


' Puts SQL in clipboard for test purposes
'Dim clipboard As MSForms.DataObject
'Set clipboard = New MSForms.DataObject
'clipboard.SetText SQLStat
'clipboard.PutInClipboard


With ActiveWorkbook.Connections("Query").ODBCConnection
    .BackgroundQuery = False
    .CommandText = SQLStat
    .Refresh
End With

MsgBox "Refreshed"

End Sub

I have split this out into a test scenario. Test1 Fails. The query doesn't update. Test2 Refreshes as you'd expect.

Sub Test1()
Dim SqlStr As String
Dim Days As String

Days = Sheets("test").Range("A1").Value
SqlStr = "declare @Days int set @Days = " & Days & " select dateadd(day,@days,getdate())"

With ActiveWorkbook.Connections("Query").ODBCConnection
    .CommandText = SqlStr
    .Refresh
End With

End Sub


Sub Test2()
Dim SqlStr As String
Dim Days As String

Days = Sheets("test").Range("A1").Value
SqlStr = "select dateadd(day," & Days & ",getdate())"

With ActiveWorkbook.Connections("Query").ODBCConnection
    .CommandText = SqlStr
    .Refresh
End With

End Sub

You have Days defined as a string in VBA and an int in your sql. I don't think you can do this type of dynamic sql through VBA. Why not create a stored procedure with a parameter of @Days? Then you can just execute your procedure. — Sean Lange
– Sean Lange, Commented Mar 18, 2015 at 14:57
Makes no difference. I have just changed the Days VBA parameter to RepDays to be sure and it does the same. — SQLSam
– SQLSam, Commented Mar 18, 2015 at 15:01
Right because you should be doing this in a stored procedure. It isn't about the name, it is about how you are doing it. — Sean Lange
– Sean Lange, Commented Mar 18, 2015 at 15:04
I cannot modify the database to insert stored procedures. I have worked it currently by building up the SQL statement in VBA, with the parameters directly inserted in the code — SQLSam
– SQLSam, Commented Mar 18, 2015 at 15:23
I don't know what the actual sql is but building up sql and executing it without parameters is a recipe for sql injection. You should at the very least parameterize your sql. This would fix the problem. — Sean Lange
– Sean Lange, Commented Mar 18, 2015 at 15:29

FreeMan · Accepted Answer · 2015-03-18 17:10:09Z

I think the issue is that when you execute the query via ODBC, you cannot declare the temporary variable @Days, like you can when you paste your clipboard contents into SSMS, though I may be wrong there.

As Sean Lange mentioned, this isn't the ideal way of doing it, but you could build your entire query in VBA, then pass that to the server. Something like this:

Dim Days As String
Dim SQLStat As String
Dim RChar As Integer

Days = Sheets("Data").Range("A1").Value

' Gets current Query, and strips off declaration
SQLStat = ActiveWorkbook.Connections("Query").ODBCConnection.CommandText
SQLStat = Right(SQLStat, (Len(SQLStat) - (InStr(1, SQLStat, "Select") - 1)))

'Build the new query string using the actual value of Days
SQLStat = Replace(SQLStat, "@Days", Days)

This will build the entire query string in VBA, thus skipping the need to have the @Days local variable declared on the server. The Replace() does in VBA what the @Days would do on the server.

Collectives™ on Stack Overflow

SQL Named Variables in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related