Solved

Run .sql Script from Vba

Posted on 2013-06-04
12
3,364 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

752 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