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 — user11509084
– user11509084, Commented Jul 31, 2019 at 13:51

Keenan B · Accepted Answer · 2019-07-31 13:47:31Z

2

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.

answered Jul 31, 2019 at 13:47

Keenan B

557 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Roy Taylor · Accepted Answer · 2019-07-31 15:01:36Z

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.

Lodi · Accepted Answer · 2019-07-31 13:53:59Z

0

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")

answered Jul 31, 2019 at 13:53

Lodi

5804 silver badges16 bronze badges

Collectives™ on Stack Overflow

How do I execute a sql script from excel using VBA

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related