Data definition language (DDL) statements are SQL statements that support the definition or declaration of database objects; for example, CREATE TABLE, DROP TABLE, and ALTER TABLE.
You can use the ADO Command object to issue DDL statements. To differentiate DDL statements from a table or stored procedure name, set the CommandType property of the Command object to adCmdText. Because executing DDL queries with this method does not generate any recordsets, there is no need for a Recordset object.
Microsoft® SQL Server™ provides a group of query processing options that can be specified by using the SET statement. These SET options do not generate result sets and can be treated as the same category of DDL queries.
This example shows the use of the Command object to turn off the SET NOCOUNT option of the Transact-SQL SET statement:
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
' If the ADOTestTable does not exist, go to AdoError.
On Error GoTo AdoError
' Connect using the SQLOLEDB provider.
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "MyServerName"
cn.Properties("Initial Catalog").Value = "pubs"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open
' Set up command object.
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "DROP TABLE ADOTestTable"
Cmd.CommandType = adCmdText
Cmd.Execute
Done:
Cmd.CommandText = "SET NOCOUNT ON"
Cmd.Execute
Cmd.CommandText = "CREATE TABLE ADOTestTable (id int, name char(100))"
Cmd.Execute
Cmd.CommandText = "INSERT INTO ADOTestTable values(1, 'Jane Doe')"
Cmd.Execute
Cn.Close
Exit Sub
AdoError:
Dim errLoop As Error
Dim strError As String
' Enumerate Errors collection and display properties of
' each Error object.
Set Errs1 = Cn.Errors
For Each errLoop In Errs1
Debug.Print errLoop.SQLState
Debug.Print errLoop.NativeError
Debug.Print errLoop.Description
Next
GoTo Done
End Sub
This example drops a table, creates a table, and then inserts data into the new table by using the Execute method of the Command object. Recordset objects are not created for this type of query. The ADOTestTable table may not exist in the database, so execution of DROP TABLE ADOTestTable may generate an error indicating the table does not exist in the database. Some error handling code is provided for this situation. The SET NOCOUNT ON SET option is also executed.