• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4144
  • Last Modified:

Run .sql Script from Vba

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
montrof
Asked:
montrof
  • 7
  • 5
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
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
 
montrofAuthor Commented:
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
 
[ fanpages ]IT Services ConsultantCommented:
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
montrofAuthor Commented:
I would want to execute on the .sql file as my cn connection.
0
 
[ fanpages ]IT Services ConsultantCommented:
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
 
montrofAuthor Commented:
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
 
[ fanpages ]IT Services ConsultantCommented:
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
 
montrofAuthor Commented:
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
 
montrofAuthor Commented:
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
 
montrofAuthor Commented:
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
 
[ fanpages ]IT Services ConsultantCommented:
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
 
montrofAuthor Commented:
I was able to figure it out with a little bit of direction.  thank you for the help.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now