Creating a database from VB6

Creating a database from VB6

Post by 2xfran » Fri, 14 Jun 2002 13:31:11



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?
 
 
 

Creating a database from VB6

Post by Paul Clemen » Fri, 14 Jun 2002 22:00:04


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)

 
 
 

Creating a database from VB6

Post by Mike Collie » Fri, 14 Jun 2002 22:05:16


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

There are a few ways:

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?

 
 
 

Creating a database from VB6

Post by Orion Carrie » Fri, 21 Jun 2002 09:47:53


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

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

 
 
 

Creating a database from VB6

Post by Paul Clemen » Sat, 22 Jun 2002 02:05:21




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)

 
 
 

Creating a database from VB6

Post by Mike Scirocc » Sat, 22 Jun 2002 04:32:34


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.

Mike




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

 
 
 

Creating a database from VB6

Post by Paul Clemen » Sat, 22 Jun 2002 23:41:05


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)

 
 
 

Creating a database from VB6

Post by Mike Scirocc » Sun, 23 Jun 2002 05:19:34


Paul,

Thanks for a great code example.


> There's a shorthand way to append columns to the table:
>
>     tbl.Columns.Append "FirstName", 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)