T-SQL query with variable declarations in Excel VBA Code fail

Question

I got a problem with running SQL query with "declare" and "set" functions in VBA.

Sheets("Arkusz1").Select
connstring = _
"ODBC;DRIVER=SQL Server;SERVER=my_database_server;UID=user;PWD=password;APP=Microsoft Office 2010;WSID=some_id;DATABASE=mydatabase"

With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Worksheets("Arkusz1").Range("A1"), Sql:=Array( _
"declare @dzisiaj date" & Chr(13), _
"set @dzisiaj = getdate()" & Chr(13), _
"select @dzisiaj as dzisiaj"))

    .BackgroundQuery = False
    .Refresh
End With

In SQL Server 2012 that code works fine, but... when I embed it into it gives me a run-time error '1004'. Also VBA code works on other queries works well.

My full SQL query has about 90 lines with 2 variable declarations (one declaration is a value from another 30 line SQL query), so it's mandatory to include variable declarations :)

How to solve that problem?

kodi1911 · Accepted Answer · 2013-12-11 13:16:42Z

I figured it out. The key is to use ADODB connection to import data via SQL Query. Also necessary is to check Microsoft Active X Data Objects 2.0 library in Tools->References in Visual Basic Editor (Shortcut: Alt+F11 in Excel).

So, there is an example of my VBA code:

    Sub sql_query_import()

    ' Declarations
    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    ' Server connection settings
    Server_Name = "192.168.1.106\my_database" ' IP of server name
    Database_Name = "mydatabase" ' Database name
    User_ID = "myusername" ' User name
    Password = "mypassword" ' User password

    ' SQL Query
    SQLStr = "SET NOCOUNT ON " & Chr(13) ' it's mandatory if you don't want to get error 3704
    SQLStr = SQLStr & "declare @dzisiaj date " & Chr(13)
    SQLStr = SQLStr & "set @dzisiaj = getdate() " & Chr(13)
SQLStr = SQLStr & "select @dzisiaj as 'today'

    ' Connect to database
    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    ' Start connection
    rs.Open SQLStr, Cn, adOpenStatic

    ' Load data
    With rs
        For i = 1 To .Fields.Count
            Worksheets(1).Cells(1, i) = .Fields(i - 1).Name ' Include column name if not - delete it
        Next i
    End With
    Worksheets(1).Cells(2, 1).CopyFromRecordset rs ' Start loading data to Cell A2


    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
    End Sub

Using in SQL Query "SET NOCOUNT ON" is necessary if you don't want to get error 3704. Also, using

SQLStr = "SET NOCOUNT ON " & Chr(13) ' it's mandatory if you don't want to get error 3704
SQLStr = SQLStr & "declare @dzisiaj date " & Chr(13)

is more efficient way to include multi-line SQL Queries :)

Community · Accepted Answer · 2017-05-23 12:20:44Z

0

I'm still new to vb and vba and learning myself, but I know you can declare and write to variables in VB.net which can then feed into an embedded SQL script. I would think you can do the same thing in vba. Here's what I suggest.

Declare a vb string like SQL_Var_1
Insert the 30-line SQL query as a separate query before the main query.
Write the result of the 30-line query to the vb string SQL_Var_1.
Remove the declarations from the Main SQL query but leaving the references to those variables.
Reference SQL_Var_1 as an input parameter in the embedded main query using the exact same name you used in the main query (i.e., @dzisiaj), like here.

If you follow these steps for both SQL variables, you should be able to achieve the same result as if you were using the declared SQL variables.

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Dec 10, 2013 at 22:34

Christopher M. Brown

2,2301 gold badge14 silver badges19 bronze badges

Collectives™ on Stack Overflow

T-SQL query with variable declarations in Excel VBA Code fail

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related