connect to SSMS ide window from VBA

Question

I'm trying to get an SQL window open in SQL Server Management Studio to display the SQL statement used (This is to allow people to modify the statement directly)

I don't wish to run the statement, I can do that already.

I can start a new process easily enough: (current code)

Public Sub ShowSQL(strSQL As String)

Dim TempSQL As String
Dim FileNum As Long

strSQL = Replace(strSQL, ",", "," & vbCrLf)

TempSQL = Get_Temp_File_Name
FileNum = FreeFile()
Open TempSQL For Output As #FileNum
Print #FileNum, strSQL
Close #FileNum

Shell "ssms " & TempSQL

End Sub

but I don't know how to tell if SSMS is already open.
For Excel, I know I can use GetObject(, "Excel.Application") - what's the equivalent for SSMS?

(from a previous question, Shell "explorer " & TempSQL1 opens the file in the current instance, but fails if there is no SSMS instance open)

Why in the world are you even trying to use VBA to open SSMS??? This just screams of so many bad things... — Sean Lange, May 04 '15 at 19:56
I have an access frontend to an SQL database. Some users have expressed an interest in getting the SQL that is used. I would like to open the file in the correct program as a nicety to the user, rather than sending it to word/notepad/whatever. — SeanC, May 04 '15 at 19:58
But in order for that to happen you would have to install SSMS on your users machines. Why do users need the sql in the first place? If they don't have permission to view the sql there is generally a good reason, that is because users have no business looking at, or even worse modifying, any sql from an application. — Sean Lange, May 04 '15 at 20:01
most have it, as it's a standard part of the company install. They have the access to write their own SQL statements, and join in any way they feel. Some don't bother, and only use the front end. Some like the front end results, but would like to see the underlying query so they can edit it, or learn where the data comes from to write their own queries. — SeanC, May 04 '15 at 20:04
If this is just for users to view and modify, can't you just create a textbox in your VBA put the query there and where the users are done take the modifications and run the query if necessary with out the trying to control SSMS? — jradich1234, May 04 '15 at 20:04
I can, but i'm trying to be pretty (and expand my vba knowledge) — SeanC, May 04 '15 at 20:05
Wow how do even pretend to maintain any kind of data integrity if the entire company has access to the data directly? — Sean Lange, May 04 '15 at 20:13

score 1 · Accepted Answer · answered May 04 '15 at 20:11

You can use the following function to determine if management studio (ssms.exe) is currently running on your computer:

Function IsProcessRunning(ProcessName As String) As Boolean
    Dim Process, strObject
    strObject = "winmgmts://" & Environ("ComputerName")
    For Each Process In GetObject(strObject).InstancesOf("win32_process")
        If UCase(Process.Name) = UCase(ProcessName) Then
             IsProcessRunning = True
             Exit Function
        End If
    Next
    IsProcessRunning = False
End Function

in your code write:

if Not IsProcessRunning("ssms.exe") Then Shell "ssms " & TempSQL

connect to SSMS ide window from VBA

1 Answers1