?
Solved

Run .sql Script from Vba

Posted on 2013-06-04
12
Medium Priority
?
3,707 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

718 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