create tables

[luisvb]

I would like to know how to create tables within VB Code. The user will be allowed to create tables within the application interface. I have been reading about ADO objects. you can use this object to create recorsets, connection, and commands but how can use it to create tables on an Access.MDB File.

[Toot]

Once you've opened a connection to the database e.g.

cn.Open ConnectionString, UID, PWD, Options

use cn.Execute to create the table with SQL - for example to create a table called "Toot" with fields "ID" (long int - primary key), and "Name" (String), use the following (untested)

cn.Execute "CREATE TABLE Toot (ID long NOT NULL, Name String NOT NULL, CONSTRAINT pk PRIMARY KEY(ID))"

Don't forget that if you want to make ID an auto-increment field it's an attribute but I can't remember exactly how to do it - search the database forum for autonumber and attribute if you're interested.

HTH,

Toot

[Chris]

Sure this the solution that you looking for.

Sub CreateTable()

    Dim tbl As New Table
    Dim cat As New ADOX.Catalog

'Open the catalog.
    ' Open the Catalog.
    cat.ActiveConnection = _ 
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\Program Files\Microsoft Office\" & _
        "Office\Samples\Northwind.mdb;"

    tbl.Name = "MyTable"
    tbl.Columns.Append "Column1", adInteger
    tbl.Columns.Append "Column2", adInteger
    tbl.Columns.Append "Column3", adVarWChar, 50
    cat.Tables.Append tbl

End Sub