How do I execute a sql script from excel using VBA

Question

I would like to have macros that will run .sql scripts (that are saved in a folder), instead of having to put the SQL code in the Excel VBA script.

I have over 50 scripts that I'd like to automate - and these have many queries in each that are separated by a ;

I've tried

db.ExecuteImmediate ("\\c\sql_folder\1.0_Create_My Lovely_Table.sql")

but the error says 'Object Required'

The code below works but I'd like help to do the above. Thanks.

Sub CREATE_My_lovely_table()
Dim con As ADODB.Connection
Dim recset As ADODB.Recordset
Dim ConnectionString As String
Dim SQL_String As String

Set con = New ADODB.Connection
Set recset = New ADODB.Recordset

ConnectionString = "xxxxxx.1;User ID=XXXX_XXXX_XXXX;password=xxxxxxxx;Data Source=xxxxxxxx"

con.Open ConnectionString

SQL_String = "CREATE TABLE My_lovely_table (CASEID NUMBER,ACCOUNTNUMBER NUMBER,RESOLVEDDATE TIMESTAMP,RESOLUTION VARCHAR (50),RESOLVEDBY VARCHAR (100),ORDERID NUMBER)"
recset.Open SQL_String, con
    con.Close
End Sub

You can load the text into a string and execute it. For queries that don't return records you can use `[Connection Object].Execute` — , Jul 31 '19 at 13:51

score 2 · Answer 1 · answered Jul 31 '19 at 13:47

You could try executing SQL stored procedures from VBA. I can imagine it isn't very different from executing a SQL query.

Set mobjConn = New ADODB.Connection
mobjConn.Open "your connection string"
Set mobjCmd = New ADODB.Command
With mobjCmd
    .ActiveConnection = Me.Connection
    .CommandText = "your stored procedure"
    .CommandType = adCmdStoredProc
    .CommandTimeout = 0
    .Parameters.Append .CreateParameter("your parameter name", adInteger, adParamInput, , your parameter value)
    ' repeat as many times as you have parameters

    .Execute
End With

Found from The Dumb Radish Calling stored procedure VBA

Another option may be storing strings (SQL Queries) in a text file and loading them into a VBA array and executing them.

score 1 · Answer 2 · answered Jul 31 '19 at 15:01

Many thanks for the replies. I'm getting round it with:

Sub Run_my_sql()
Dim con As ADODB.Connection
Dim ConnectionString As String
Dim SQL_Commands As Variant
Dim SQL_String As Variant
Dim strFilename As String: strFilename = "C:\folder_name\my_sql_file.sql"
Dim iFile As Integer: iFile = FreeFile

Set con = New ADODB.Connection
Set recset = New ADODB.Recordset

ConnectionString = "xxxx.1;User ID=xxx_xxxxx_xxxx;password=xxxxxxxx;Data Source=xxxxxxx"

con.Open ConnectionString


Open strFilename For Input As #iFile
SQL_String = Input(LOF(iFile), iFile)
Close #iFile

SQL_Commands = Split(SQL_String, ";")
For Each SQL_String In SQL_Commands
    con.Execute SQL_String
Next SQL_String

con.Close
End Sub

at the moment.

score 0 · Answer 3 · answered Jul 31 '19 at 13:53

You could download SQL Server's "sqlcmd" Utility (read more on https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-2017)

With it, you can run *.sql scripts from command line...

Then, in your VBA Code, you could use the Shell procedure

current_query = Shell("sqlcmd -S <server Name> -U sa -P sapassword -i inputquery_file_name -o outputfile_name")

How do I execute a sql script from excel using VBA

3 Answers3