Very Computer

I am trying to create a database, its tables and fields from VB6, rather
than from Access.  Can anyone give me a good link or sample for doing so,
especially if using ADO?

You can use ADOX (Microsoft ADO Ext 2.x for DDL and Security) to do this:

Sub CreateAccessWithADOX()

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

'Engine Type=4 is Access 97 and a value of 5 is Access 2000
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=d:\My Documents\db1.mdb;" & _
                     "Jet OLEDB:Engine Type=4;"

tbl.NAME = "NewTable"
col.NAME = "DateField"
col.Type = adDate
tbl.Columns.Append col
Set col = New ADOX.Column
col.NAME = "Address2"
col.Type = adVarWChar
col.DefinedSize = 20
col.Attributes = adColNullable
tbl.Columns.Append col
Set col = New ADOX.Column
col.NAME = "Age"
col.Type = adInteger
col.Attributes = adColNullable
tbl.Columns.Append col
cat.Tables.Append tbl

tbl.Columns("Address2").Properties("Jet OLEDB:Allow Zero Length").Value = True

Set cat = Nothing

End Sub

Microsoft MVP (Visual Basic)

Quote:> I am trying to create a database, its tables and fields from VB6,

ADOX: Import the library and call the methods to build your database
http://support.microsoft.com/support/kb/Articles/q275/2/52.asp (I use this
if I need to set table and fields properties that cannot be done using SQL).

SQL: You can execute SQL commands to create Tables and fields the syntax
works on a range of databases:

"CREATE TABLE  myTest (
 field1 varchar(10),
 field2 varchar(20),
 primary key (field1)
)"

DMO: For SQL Server specifically.

--
Michael Collier
www.adoanywhere.com
ADO Inspection & Remote Data Access Tool

Quote:> I am trying to create a database, its tables and fields from VB6, rather
> than from Access.  Can anyone give me a good link or sample for doing so,
> especially if using ADO?

Thanks,

Orion

Quote:> You can use ADOX (Microsoft ADO Ext 2.x for DDL and Security) to do this:

> Sub CreateAccessWithADOX()

> Dim tbl As New ADOX.Table
> Dim col As New ADOX.Column
> Dim cat As New ADOX.Catalog

> 'Engine Type=4 is Access 97 and a value of 5 is Access 2000
> cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>                      "Data Source=d:\My Documents\db1.mdb;" & _
>                      "Jet OLEDB:Engine Type=4;"

> tbl.NAME = "NewTable"
> col.NAME = "DateField"
> col.Type = adDate
> tbl.Columns.Append col
> Set col = New ADOX.Column
> col.NAME = "Address2"
> col.Type = adVarWChar
> col.DefinedSize = 20
> col.Attributes = adColNullable
> tbl.Columns.Append col
> Set col = New ADOX.Column
> col.NAME = "Age"
> col.Type = adInteger
> col.Attributes = adColNullable
> tbl.Columns.Append col
> cat.Tables.Append tbl

> tbl.Columns("Address2").Properties("Jet OLEDB:Allow Zero Length").Value =
True

> Set cat = Nothing

> End Sub

What would be the equivalent of an operation like this in ODBC .NET?  Or
even in OLEDB .NET?

The code wouldn't change much. You would still need to use ADOX via COM interop.

    Private Sub CreateAccess_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles CreateAccess.Click

        Dim tbl As New ADOX.Table()
        Dim col As New ADOX.Column()
        Dim cat As New ADOX.Catalog()

        'Engine Type=4 is Access 97 and a value of 5 is Access 2000
        cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                             "Data Source=d:\My Documents\db10.mdb;" & _
                             "Jet OLEDB:Engine Type=4;")

        tbl.Name = "NewTable"
        col.Name = "DateField"
        col.Type = ADOX.DataTypeEnum.adDate
        tbl.Columns.Append(col)
        col = New ADOX.Column()
        col.Name = "Address2"
        col.Type = ADOX.DataTypeEnum.adVarWChar
        col.DefinedSize = 20
        col.Attributes = ADOX.ColumnAttributesEnum.adColNullable
        tbl.Columns.Append(col)
        col = New ADOX.Column()
        col.Name = "Age"
        col.Type = ADOX.DataTypeEnum.adInteger
        col.Attributes = ADOX.ColumnAttributesEnum.adColNullable
        tbl.Columns.Append(col)
        cat.Tables.Append(tbl)

        cat.Tables("NewTable").Columns("Address2").Properties("Jet OLEDB:Allow
Zero Length").Value = True

    End Sub

Microsoft MVP (Visual Basic)

    tbl.Columns.Append "FirstName", adVarWChar, 50
    tbl.Columns.Append "LastName", adVarWChar, 50
    tbl.Columns.Append "Address", adVarWChar, 50
    tbl.Columns.Append "City", adVarWChar, 50
    tbl.Columns.Append "State", adVarWChar, 50
    tbl.Columns.Append "Zip", adVarWChar, 50

I don't know how to do an autonumber field though.

Mike

There's a shorthand way to append columns to the table:

    tbl.Columns.Append "FirstName", adVarWChar, 50
    tbl.Columns.Append "LastName", adVarWChar, 50
    tbl.Columns.Append "Address", adVarWChar, 50
    tbl.Columns.Append "City", adVarWChar, 50
    tbl.Columns.Append "State", adVarWChar, 50
    tbl.Columns.Append "Zip", adVarWChar, 50

I don't know how to do an autonumber field though.

Yes it is less code if you don't need to set any other column properties (such as the Attributes
property in my example).

To make a column an autonumber field you set the Autoincrement property to True after appending the
Column object to the Columns collection.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q275252

Microsoft MVP (Visual Basic)

Thanks for a great code example.