View Single Post
Old January 13th, 2004, 07:02 AM
Imar's Avatar
Imar Imar is offline
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts

Hi Edgar,

There are two problems with the solution you have right now.

First of all, you need a reference to ADOX, so stuff like adInteger gets a proper definition. To add the reference, choose Project | References... and then select Microsoft ADO Ext. 2.x for DDL and Security from the list. With the reference, the constants like adInteger and adVarWChar you're using now work.

The second problem is that you're using a constant that doesn't exist. adText is not a valid type for a column. If you're trying to get a Memo field (Text in SQL Server), try adLongVarWChar. If you're trying to get a normal (short) text field, try adVarWChar. In relation to this, you can't use a text type, like adVarWChar, and then expect the Autoincrement property to function correctly. A field with Autoincrement set to True needs to be of a Numeric type.

There is also a third "issue" with the code. By directly converting your code from the ASP code, you're using Late Binding with Objects instead of early binding with strongly typed objects like ADOX.Table. Both options should work fine, but using strongly typed variables performs better and is considered a "better practice". I fixed the code here and there and pasted the new version below.

Hope this helps, and if you have any further questions, let me know.


Option Explicit
 Sub CreateAccessDatabase(sDatabaseToCreate)
    Dim catNewDB As ADOX.Catalog
    Set catNewDB = New ADOX.Catalog
    catNewDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sDatabaseToCreate & _
        ";Jet OLEDB:Engine Type=5;"
        ' Engine Type=5 = Access 2000 Database
        ' Engine Type=4 = Access 97 Database
   Set catNewDB = Nothing
End Sub

Sub CreateAccessTable(sDatabaseToCreate)

    Dim catDB As ADOX.Catalog
    Dim tblNew As ADOX.Table
    Set catDB = New ADOX.Catalog

    ' Open the catalog
    catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sDatabaseToCreate

    ' Create new Table
    Set tblNew = New ADOX.Table
    tblNew.Name = "Contacts"

    ' First Create an Autonumber column, called ID.
    ' This is just for demonstration purposes.
    ' We could have done this below with all the other
    ' columns as well
    Dim col  As ADOX.Column
    Set col = New ADOX.Column

    With col
        .ParentCatalog = catDB
        .Type = adInteger ' adText does not exist
        .Name = "ID"
        .Properties("Autoincrement") = True
        .Properties("Description") = "I am the Description " & _
            "for the column"
    End With
    tblNew.Columns.Append col

     ' Now add the rest of the columns
     With tblNew
         ' Create fields and append them to the
         ' Columns collection of the new Table object.
         With .Columns
             .Append "NumberColumn", adInteger
             .Append "FirstName", adVarWChar
             .Append "LastName", adVarWChar
             .Append "Phone", adVarWChar
             .Append "Notes", adLongVarWChar
             End With

             Dim adColNullable ' Is not defined in,
             ' so we need to define it here.
             ' The other option is adColFixed with a value of 1
             adColNullable = 2
             With .Columns("FirstName")
             .Attributes = adColNullable
         End With
     End With

     ' Add the new Table to the Tables collection of the database.
     catDB.Tables.Append tblNew
     Set col = Nothing
     Set tblNew = Nothing
     Set catDB = Nothing
End Sub

Private Sub Form_Load()
    Dim sDatabaseName As String
    sDatabaseName = "C:\MyNewDatabase.mdb"
    ' First call the Create Database method
    CreateAccessDatabase sDatabaseName
    ' Then add a table and columns to this database
    CreateAccessTable sDatabaseName
    MsgBox "Database has been created successfully!"
End Sub

Imar Spaanjaars
Everyone is unique, except for me.