Handling Data Definition Language

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™ 2000 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. 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.

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 = "northwind"
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

Using ADOX

Microsoft® ActiveX® Data Objects Extensions for Data Definition Language and Security (ADOX) is an extension to the ADO objects and programming model. ADOX includes objects for schema creation and modification, as well as security. However, certain features of ADOX are not be supported by the Microsoft SQL Server OLE DB Provider (SQLOLEDB). For more information, see Provider Support for ADOX.