Solved

Run .sql Script from Vba

Posted on 2013-06-04
12
3,020 Views
Last Modified: 2013-06-10
Is there a way I can run a .sql script from vba. I would like it to work by me selecting the .sql file that would be run.  

Thanks,
Montrof
0
Comment
Question by:montrof
  • 7
  • 5
12 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39218799
Hi,

Yes.  Although, you will need to expand upon your requirements.

How are you intending to specify/confirm which database (server, or database connection) will be the recipient (host) of the SQL script, & if the script returns any results where will these be shown?

BFN,

fp.
0
 
LVL 1

Author Comment

by:montrof
ID: 39218810
I would use the ADODB connection like below

 
 Dim cn As New ADODB.Connection
    Dim comm As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim dbConnectStr As String
    Dim strSheet As String
    Dim strRange As String


    Application.ScreenUpdating = False

    ' Connection to SQL Server
    UID = "Name"
    Password = "pword"
    Srv = "server"



    ' Pass Connection String to SQL Server

    'dbConnectStr
    cn.ConnectionString = "Driver={SQL Server};" & _
                          "Server=" & Srv & ";" & _
                          "User Id=" & UID & ";" & _
                          "Password=" & Password
    cn.ConnectionTimeout = 0 

Open in new window

0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39218821
OK, I think we may have been talking at cross-purposes, or maybe I was thinking of something different to what you were proposing.

Would you like to be able to select a file with a ".sql" file extension, then for the contents to be read, & for the SQL statements within it to then be Executed using the 'cn' connection object (that, I noted, is not defined with your code sample)?

See: [ http://www.vb-helper.com/howto_sql_script.html ]

---

Or do you mean you wished to execute a SQL Server Stored Procedure?
0
 
LVL 1

Author Comment

by:montrof
ID: 39218839
I would want to execute on the .sql file as my cn connection.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39218866
I think you may be missing the salient points within my questions.

Please could you post a sample ".sql" file you are hoping to execute?
0
 
LVL 1

Author Comment

by:montrof
ID: 39219014
It should not really matter because it could be any .sql text file that would be run.  I would go and select that file and then it would run it.  I am attaching a sample .sql file but It could really be any .sql file
example.sql
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39219057
It should not really matter because it could be any .sql text file that would be run.  I would go and select that file and then it would run it.  I am attaching a sample .sql file but It could really be any .sql file
The nature of the content of the file will determine what options are available for one/more potential solutions, &/or how the SQL statement(s) should be passed to the underlying database.

--- "example.sql" contents ---

--1Tab
/**********************************************************/
SELECT a, b, c,d
        
FROM
      (SELECT a,b,c, d,
              
          FROM Table s (NOLOCK)                
       JOIN
       (SELECT a, b, c, d
            FROM Table b  (NOLOCK)                        
         WHERE d = 'value'
         GROUP BY a, a, c
        ) v
             ON s.a = v.a
            AND s.b  = v.b
            AND s.c = v.c
            AND s.d= v.d
        WHERE v.d = 'value'
      )x
  GROUP BY  a,b,c,d;


/**********************************************************/
-- tab2
/**********************************************************/
SELECT a,b,c,d
  FROM table s  (NOLOCK)                              --
 JOIN
(SELECT a, b,c
   FROM table v                             --
  WHERE d = 'value2'
  GROUP BY a,b,c
 ) s
    ON v.a = s.a
   AND v.b  = s.b
   AND v.c = s.c
   AND v.d = s.d
 WHERE v.d = 'value2'
   AND v.c IN (1, 10, 20);                        
/**********************************************************/
-- tab 3
/**********************************************************/
SELECT a, b, COUNT(c) AS c,
       SUM(d) AS d
 FROM
      (SELECT a, RTRIM(b) AS b, c,
              SUM(d) AS d
        FROM table2 v  (NOLOCK)                            
      JOIN
      (SELECT a, b, c, MAX(d)AS d
         FROM table3 (NOLOCK)                              
        WHERE [d] = 'value'
        GROUP BY a, b, c
      ) s
            ON v.a = s.a
         AND v.b  = s.b
         AND v.c = s.c
         AND v.d = s.d
       WHERE v[d] = 'value'
         AND v.c IN (1, 2)                                    
        GROUP BY a, b, s
      ) x
 GROUP BY  a, b
 ORDER BY a, b ;
--- end ---

What is the significance of the "-- tab..." divisions?

Also, as I asked above; where will the results of the SQL statement(s) be directed?
0
 
LVL 1

Author Comment

by:montrof
ID: 39219074
Sorry for the confusion, The tab signifigance is the different tabs that the results would be pasted to.  The results would be directed to an excel file that would be selected by the user.  so the user would select the .sql file and the excel file the results would go to.  

Hope that helps,
montrof
0
 
LVL 1

Accepted Solution

by:
montrof earned 0 total points
ID: 39219955
This is close to what I would want to do except that i need to be able to select the .sql file and the excel template.  Also I am not sure that the code is the best



Sub test()
 ExecuteSqlScript ("C:\test.sql")
End Sub
Sub ExecuteSqlScript(FilePath As String)
 
    Dim Script As String
    Dim FileNumber As Integer
    Dim Delimiter As String
    Dim aSubscript() As String
    Dim Subscript As String
    Dim i As Long
    Dim cn As New ADODB.Connection
    Dim comm As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim dbConnectStr As String
    Dim strSheet As String
    Dim strRange As String
 





    ' Connection to SQL Server
    UID = "Name"
    Password = "pword"
    Srv = "server"



    ' Pass Connection String to SQL Server

    'dbConnectStr
    cn.ConnectionString = "Driver={SQL Server};" & _
                          "Server=" & Srv & ";" & _
                          "User Id=" & UID & ";" & _
                          "Password=" & Password
    cn.ConnectionTimeout = 0
    
    
    On Error Resume Next
    cn.Open
    If Err.Number <> 0 Then
        MsgBox "Error "
        Exit Sub
    End If
    On Error GoTo 0
    

    Set comm.ActiveConnection = cn
    rs.ActiveConnection = cn

    
 
    Delimiter = ";"
    FileNumber = FreeFile
    Script = String(FileLen(FilePath), vbNullChar)
 
    ' Grab the scripts inside the file
    Open FilePath For Binary As #FileNumber
    Get #FileNumber, , Script
    Close #FileNumber
 
    ' Put the scripts into an array
    aSubscript = Split(Script, Delimiter)
 
    ' Run each script in the array
    For i = 0 To UBound(aSubscript) - 1
        aSubscript(i) = Trim(aSubscript(i))
        Subscript = aSubscript(i)
'        Debug.Print Subscript
        comm.CommandText = Subscript
        comm.CommandTimeout = 0
        rs.Open comm
        
        If i = 0 Then Sheets("1").Range("A2").CopyFromRecordset rs
        If i = 1 Then Sheets("2").Range("A2").CopyFromRecordset rs
        If i = 2 Then Sheets("3").Range("A2").CopyFromRecordset rs
    rs.Close
 
    Next i

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:montrof
ID: 39220172
The perfect thing would be if it could look in a directory and open the excel file and then take that name up until the  3rd "-" and then append that with "query.sql" and open that file name and run that as the .sql file.  So it would loop through the directory and match the excel file with the sql script and run it.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39223460
If I gave you any pointers, you were very welcome.

However, I don't think I did.

Either way, good luck with your project.
0
 
LVL 1

Author Closing Comment

by:montrof
ID: 39234113
I was able to figure it out with a little bit of direction.  thank you for the help.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now