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