Open a SQL script with vba

Hello --

I am trying to create a button with a vba macro assigned to it that will open a SQL script in SQL server management studio when I click it. I want to use windows authentication in the code. I am able to execute ssms.exe, but I do not know how to write the vba that will have the button open a SQL script and login to SQL server with a chosen server and database.  The script does not need to be executed and I am using SQL server 2008 and office 2003.

Any help will be appreciated.

Thanks,

Wanderer(er)
WanderererAsked:
Who is Participating?
 
dbbishopConnect With a Mentor Commented:
If the path/filename contains spaces, you should do something like:
Public Sub Open_ModScript()
    Dim rc
    Dim myFile

    myFile = Chr(34) & "W:\My Test Folder\Test.sql" & chr(34)
    rc = Shell("ssms.exe -S WRTSA41276,15001 -d wandersden -E " & myFile, 1)
End Sub

Open in new window

0
 
arnoldCommented:
Why not use the VBA sql connection method?

Excel has an example of connecting to sql for queries.
0
 
dbbishopCommented:
A few clarifications please.

You state, "...will open a SQL script in SQL server management studio..." Does that mean you actually want the button to open SSMS with the script loaded?

You say you have a script. Is it SQL code in a file (which is a script), or is it a smithering of code you want to open.

I suspect, from your question, you want to open SSMS and have it load a file, with the proper database connection. If so, try:

ssms.exe -S server_name -d database -U user -P password -E [use windows authentication] [filename[, file_name]*] name(s) of file(s) to load

ssms.exe /? will bring up the list of parameters to use.

-noSplash will suppress the splash screen while SSMS is loading.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
WanderererAuthor Commented:
Thank you for the responses.  As you know, I am very new to VBA, as this is the first time that I have tried to implement it with SQL Server.  

dbbishop --  I tried the code you suggested, perhaps incorrectly written (the variables are dummy variables, but have similarities with my actual variables):

Public Sub Open_ModScript()
 
    ssms.exe -S WRTSA41276,15001 -d wandersden -E W:\Test\Scripts\Test.sql

End Sub

I get a 'Compile error: Expected: end of statement' at the Server name which is highlighted.  I have tried with and without the port: 15001.  This has usually been the issue when I have tried passing the server name.

Any suggestions?

Thanks,

wander
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
try with a : instead of the ,
And write it Together with the -S switch (applies to all switches), and inclose the scriptpath with " then it will understand spaces in the path, as i e
ssms.exe -SWRTSA41276:15001 -dwandersden -E "W:\Test\Scripts\Test.sql"

Regards Marten
0
 
dbbishopCommented:
Public Sub Open_ModScript()
    Dim rc
    rc = Shell("ssms.exe -S WRTSA41276,15001 -d wandersden -E W:\Test\Scripts\Test.sql", 1)
End Sub

Open in new window

0
 
WanderererAuthor Commented:
Thank you sir, works perfectly.
0
 
venkatesh ksCommented:
how to open sql server management studio by using sql server authentication by using vba??
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.