create and drop table via vb

[yuliam]

Hi,
How can i create and drop table in MS SQL Server 2000 via VB6? I think I should use ADOX object, but I don't know exactly how....
The following code uses ADO connection object and returns with runtime error "incorrect syntax near AS":

Dim db as ADODB.Connection
'... open connection to database

Dim strCmd As String

strCmd = "CREATE TABLE tmp_tbl AS SELECT * FROM tbl"

db.Execute strCmd

Thank you in advance

[D12Bit]

you are mixin SQL statements ...

CREATE is one thing and
SELECT is completly other thing

both can't be used in the same command string

for better help type CREATE in one of the procedure and press F1 and select VB help it'll take you to the SQL procedures help (not inside MSDN)
but for this case this is an extract of it:

Creates a new table.

Note The Microsoft Jet database engine doesn't support the use of CREATE TABLE, or any of the DDL statements, with non-Microsoft Jet database engine databases. Use the DAO Create methods instead.

Syntax

CREATE TABLE table (field1 type [(size)] [NOT NULL] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

The CREATE TABLE statement has these parts:

Part Description
table The name of the table to be created.
field1, field2 The name of field or fields to be created in the new table. You must create at least one field.
type The data type of field in the new table.
size The field size in characters (Text and Binary fields only).
index1, index2 A CONSTRAINT clause defining a single-field index. See the CONSTRAINT clause topic for more information on how to create this index.
multifieldindex A CONSTRAINT clause defining a multiple-field index. See the CONSTRAINT clause topic for more information on how to create this index.
Remarks

Use the CREATE TABLE statement to define a new table and its fields and field constraints. If NOT NULL is specified for a field, then new records are required to have valid data in that field.
A CONSTRAINT clause establishes various restrictions on a field, and can be used to establish the primary key. You can also use the CREATE INDEX statement to create a primary key or additional indexes on existing tables.
You can use NOT NULL on a single field, or within a named CONSTRAINT clause that applies to either a single field or to a multiple-field named CONSTRAINT. However, you can apply the NOT NULL restriction only once to a field, or a run-time error occurs.

Example:

This example creates a new table called ThisTable with two Text fields.
 
Sub CreateTableX1()
 
    Dim dbs As Database
 
    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")
 
' Create a table with two text fields.
 
dbs.Execute "CREATE TABLE ThisTable " _
        & "(FirstName TEXT, LastName TEXT);"
 
    dbs.Close
 
End Sub
 
This example creates a new table called MyTable with two Text fields, a Date/Time field, and a unique index made up of all three fields.
 
Sub CreateTableX2()
 
    Dim dbs As Database
 
    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")
 
    ' Create a table with three fields and a unique
    ' index made up of all three fields.
    dbs.Execute "CREATE TABLE MyTable " _
        & "(FirstName TEXT, LastName TEXT, " _
        & "DateOfBirth DATETIME, " _
        & "CONSTRAINT MyTableConstraint UNIQUE " _
        & "(FirstName, LastName, DateOfBirth));"
 
dbs.Close
 
End Sub
 
This example creates a new table with two Text fields and an Integer field. The SSN field is the primary key.
 
Sub CreateTableX3()
 
    Dim dbs As Database
 
    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")
 
    ' Create a table with three fields and a primary
    ' key.
    dbs.Execute "CREATE TABLE NewTable " _
        & "(FirstName TEXT, LastName TEXT, " _
        & "SSN INTEGER CONSTRAINT MyFieldConstraint " _
        & "PRIMARY KEY);"
 
    dbs.Close
 
End Sub