Add field to Jet 4.0 Database

How do I add a new field to MSAccess 2000 database using ADO?

I have tried using rs.Fields.Append but I can't find a compatible data type.

I need to create a text field of 50 characters and allow zero length strings.


Who is Participating?
Ryan ChongConnect With a Mentor Commented:
Hi Lisp,

Here is an example:

Private Sub Command5_Click()
    Dim oCat As ADOX.Catalog
    Dim oTable As ADOX.Table
    Dim sTable As String
    Set oCat = New ADOX.Catalog
    Text1.Text = "D:\Project\Icon\Database\database.mdb"
    oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source=" & Text1.Text & ";"
    For Each oTable In oCat.Tables
        If oTable.Name = "POrder" Then
            oTable.Columns.Append "MyNewTextField", adWChar, 200
            oTable.Columns.Append "MyNewCurField", adCurrency
            oTable.Columns.Append "MyNewDateField", adDate
            oTable.Columns.Append "MyNewBolField", adBoolean
            oTable.Columns.Append "MyNewSmallIntField", adSmallInt
            Exit For
        End If
    Set oTable = Nothing
    Set oCat = Nothing
End Sub

Hope this help
Ryan ChongCommented:
Use the oTable.Columns(item).Properties to set the Allow Zero Length Property..
Anthony PerkinsCommented:
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cat = New ADOX.Catalog
With cat
    .ActiveConnection = "Your Jet connection goes here"
    Set tbl = .Tables("Table1")
    With tbl
        Set col = New ADOX.Column
        With col
            'This is probably the line you missed (I did!)
            'Unless you set the properties are not available when the column is created.
            .ParentCatalog = cat
            .Name = "ColumnName"
            .Type = adVarWChar
            .DefinedSize = 50
            .Properties("Jet OLEDB:Allow Zero Length").Value = True
        End With
        .Columns.Append col
    End With
End With
Set cat = Nothing

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LispAuthor Commented:
My system doesn't know what an ADOX is.  I'm guessing it is not the same thing as ADO.  Is there a different reference that I need?
LispAuthor Commented:
The reference I have loaded is "Active X Data Objects 2.6"
LispAuthor Commented:
I don't have any of these objects.
Ryan ChongCommented:
Use M$ ADO Ext 2.5 for Dll and Security
LispAuthor Commented:
Thanks for your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.