Add column to existing Access2000 table


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Add column to existing Access2000 table

  1. #1
    James Guest

    Add column to existing Access2000 table


    Hi All.

    I've been trying to add a column to an existing Access2000 table. I know
    how to do this if I'm building the table from scratch, but doing to an existing
    table appears to be another trick. Does anyone know how this can be done?

    Thanks

    James

  2. #2
    Michael Culley Guest

    Re: Add column to existing Access2000 table

    Best do it with an SQL statement

    ALTER TABLE MyTable APPEND COLUMN MyCol (Text 25)

    I'm probably way off base, but the syntax should be pretty close to this,
    just look in help under ALTER.

    --
    Michael Culley
    www.vbdotcom.com


    "James" <davidsonj@tni.ca> wrote in message
    news:3bfd3452$1@147.208.176.211...
    >
    > Hi All.
    >
    > I've been trying to add a column to an existing Access2000 table. I know
    > how to do this if I'm building the table from scratch, but doing to an

    existing
    > table appears to be another trick. Does anyone know how this can be done?
    >
    > Thanks
    >
    > James




  3. #3
    Paul Clement Guest

    Re: Add column to existing Access2000 table

    On 22 Nov 2001 17:22:26 GMT, "James" <davidsonj@tni.ca> wrote:


    Hi All.

    I've been trying to add a column to an existing Access2000 table. I know
    how to do this if I'm building the table from scratch, but doing to an existing
    table appears to be another trick. Does anyone know how this can be done?

    Are you using ADO or DAO?


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  4. #4
    James Guest

    Re: Add column to existing Access2000 table


    Hi Paul

    I'm using ADO 2.5 if that helps

    James

  5. #5
    Paul Clement Guest

    Re: Add column to existing Access2000 table

    On 26 Nov 2001 19:41:29 GMT, "James" <davidsonj@tni.ca> wrote:


    Hi Paul

    I'm using ADO 2.5 if that helps

    James

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

    Function AddNewFields()

    Dim tbl As ADOX.Table
    Dim col As New ADOX.Column
    Dim cat As New ADOX.Catalog
    Dim cnn As New ADODB.Connection

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=d:\My Documents\db1.mdb;" & _
    "Jet OLEDB:Engine Type=4;"

    Set cat.ActiveConnection = cnn
    Set tbl = cat.Tables("NewTable")
    col.NAME = "Name"
    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

    Set cat = Nothing
    cnn.Close
    Set cnn = Nothing

    End Function


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  6. #6
    Michael Culley Guest

    Re: Add column to existing Access2000 table

    Paul,

    When using ADOX the results should be checked fairly carefully because you
    don't always get what you expect. I do use it but find it does not work
    correctly for some data types.

    --
    Michael Culley
    www.vbdotcom.com


    "Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message
    news:ir870ussdt40skfs7lf9tpak4d23nt7nlu@4ax.com...
    > On 26 Nov 2001 19:41:29 GMT, "James" <davidsonj@tni.ca> wrote:
    >
    >
    > Hi Paul
    >
    > I'm using ADO 2.5 if that helps
    >
    > James
    >
    > You can use ADOX for this (Microsoft ADO Ext 2.x for DDL and Security):
    >
    > Function AddNewFields()
    >
    > Dim tbl As ADOX.Table
    > Dim col As New ADOX.Column
    > Dim cat As New ADOX.Catalog
    > Dim cnn As New ADODB.Connection
    >
    > cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=d:\My Documents\db1.mdb;" & _
    > "Jet OLEDB:Engine Type=4;"
    >
    > Set cat.ActiveConnection = cnn
    > Set tbl = cat.Tables("NewTable")
    > col.NAME = "Name"
    > 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
    >
    > Set cat = Nothing
    > cnn.Close
    > Set cnn = Nothing
    >
    > End Function
    >
    >
    > Paul ~~~ pclement@ameritech.net
    > Microsoft MVP (Visual Basic)




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center