• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

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.

Thanks,

Lisp
0
Lisp
Asked:
Lisp
  • 4
  • 3
1 Solution
 
Ryan ChongCommented:
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
    Next
   
    Set oTable = Nothing
    Set oCat = Nothing
End Sub

Hope this help
0
 
Ryan ChongCommented:
Use the oTable.Columns(item).Properties to set the Allow Zero Length Property..
0
 
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

Anthony
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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?
0
 
LispAuthor Commented:
The reference I have loaded is "Active X Data Objects 2.6"
0
 
LispAuthor Commented:
I don't have any of these objects.
0
 
Ryan ChongCommented:
Use M$ ADO Ext 2.5 for Dll and Security
0
 
LispAuthor Commented:
Thanks for your help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now