I am building a command string in VBA to be handed over to Microsoft SQL Server Management Studio which looks something like this:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
GO
CREATE TABLE #temp
However, the command string in VBA does not include line breaks required for the GO statment which causes the execution to fail. Any ideas?
When you are building your command string, use vbCrLf to create a new line.
Example:
Dim strMyCommand As String
strMyCommand = "IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp" & vbCrLf & _
"GO" & vbCrLf & _
"CREATE TABLE #temp"
Debug.Print strMyCommand
you cannot use GO in an sql string, even if separated by carriage returns and line feeds... this is a batch separator only known to SQLCMD and SQL Server Management Studio. For the above query you have to issue two calls to your database connection.
For instance, this works (on a local SQL Server instance) :
Sub test()
Dim oConn As ADODB.Connection
Set oConn = New Connection
oConn.ConnectionString = "Provider=SQLNCLI11;Server=(local);Integrated Security=SSPI;"
oConn.Open
oConn.Execute "IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp"
oConn.Execute "Create table #temp(somecolumn int)"
oConn.Close
End Sub
We can separate the SQL queries with a semicolon. The following should work without any issues and you only need to make one call to your database connection.
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;CREATE TABLE #temp(ID INT);
This works on a local SQL Server instance:
Sub MyTest()
Dim con As ADODB.Connection
Set con = New Connection
con.ConnectionString = "Provider=SQLNCLI11;Server=(local);Integrated Security=SSPI;"
con.Open
con.Execute "IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;Create table #temp(somecolumn int);"
con.Close
End Sub
