DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Adding columns to an existing table in Access

  1. #1
    Dave Guest

    Adding columns to an existing table in Access


    Hi all

    I'm trying to add some new columns to an existing Access database, but get
    a "multiple step operation generated errors" error each time I try to set
    the adColNullable property - the rest of the code works fine without this
    part and I know that it works fine for creating a new table too, but not
    in this case of adding columns to an existing table... anyone any ideas what
    I'm doing wrong? The code I'm using is below.

    Many thanks in advance,
    Dave

    Dim conn As New ADODB.Connection
    Dim cat As New adox.Catalog
    Dim tbl As New adox.Table
    Dim col As New adox.Column

    dbpath = PathToDatabase
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & dbpath & ";" & _
    "Persist Security Info=False"
    conn.Open
    Set cat.ActiveConnection = conn
    Set tbl = cat.Tables.Item("Sales")
    tbl.Columns.Append "Time_Created", adDate
    tbl.Columns.Append "Tran_Ref", adDouble
    tbl.Columns.Append "Product_Name", adVarWChar
    tbl.Columns.Append "Note", adVarWChar

    tbl.Columns("Time_Created").Attributes = adColNullable
    tbl.Columns("Tran_Ref").Attributes = adColNullable
    tbl.Columns("Product_Name").Attributes = adColNullable
    tbl.Columns("Note").Attributes = adColNullable

    tbl.Columns("Time_Created").Properties("Jet OLEDB:Allow Zero Length").Value
    = True
    tbl.Columns("Tran_Ref").Properties("Jet OLEDB:Allow Zero Length").Value =
    True
    tbl.Columns("Product_Name").Properties("Jet OLEDB:Allow Zero Length").Value
    = True
    tbl.Columns("Note").Properties("Jet OLEDB:Allow Zero Length").Value = True

    conn.Close
    Set conn = Nothing
    Set tbl = Nothing
    Set cat = Nothing


  2. #2
    Paul Clement Guest

    Re: Adding columns to an existing table in Access

    On 15 Apr 2002 11:35:47 -0800, "Dave" <vb.@127.0.0.1> wrote:


    Hi all

    I'm trying to add some new columns to an existing Access database, but get
    a "multiple step operation generated errors" error each time I try to set
    the adColNullable property - the rest of the code works fine without this
    part and I know that it works fine for creating a new table too, but not
    in this case of adding columns to an existing table... anyone any ideas what
    I'm doing wrong? The code I'm using is below.

    Many thanks in advance,
    Dave

    Dim conn As New ADODB.Connection
    Dim cat As New adox.Catalog
    Dim tbl As New adox.Table
    Dim col As New adox.Column

    dbpath = PathToDatabase
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & dbpath & ";" & _
    "Persist Security Info=False"
    conn.Open
    Set cat.ActiveConnection = conn
    Set tbl = cat.Tables.Item("Sales")
    tbl.Columns.Append "Time_Created", adDate
    tbl.Columns.Append "Tran_Ref", adDouble
    tbl.Columns.Append "Product_Name", adVarWChar
    tbl.Columns.Append "Note", adVarWChar

    tbl.Columns("Time_Created").Attributes = adColNullable
    tbl.Columns("Tran_Ref").Attributes = adColNullable
    tbl.Columns("Product_Name").Attributes = adColNullable
    tbl.Columns("Note").Attributes = adColNullable

    tbl.Columns("Time_Created").Properties("Jet OLEDB:Allow Zero Length").Value
    = True
    tbl.Columns("Tran_Ref").Properties("Jet OLEDB:Allow Zero Length").Value =
    True
    tbl.Columns("Product_Name").Properties("Jet OLEDB:Allow Zero Length").Value
    = True
    tbl.Columns("Note").Properties("Jet OLEDB:Allow Zero Length").Value = True

    conn.Close
    Set conn = Nothing
    Set tbl = Nothing
    Set cat = Nothing


    You need to set the adColNullable attribute prior to adding the column to the table.


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

  3. #3
    Roger Guest

    Re: Adding columns to an existing table in Access


    Dave,

    When I have gotten this error in the past it usually has dealt with my SELECT
    statement. Sometimes during devolopement I have either narrowed what fields
    I am looking at or used the * to temporarily get me through.

    Look at that first. Your code should work. I have used the same exact thing
    before, and it worked fine. Yours should to.

    Roger.

    "Dave" <vb.@127.0.0.1> wrote:
    >
    >Hi all
    >
    >I'm trying to add some new columns to an existing Access database, but get
    >a "multiple step operation generated errors" error each time I try to set
    >the adColNullable property - the rest of the code works fine without this
    >part and I know that it works fine for creating a new table too, but not
    >in this case of adding columns to an existing table... anyone any ideas

    what
    >I'm doing wrong? The code I'm using is below.
    >
    >Many thanks in advance,
    >Dave
    >
    >Dim conn As New ADODB.Connection
    >Dim cat As New adox.Catalog
    >Dim tbl As New adox.Table
    >Dim col As New adox.Column
    >
    >dbpath = PathToDatabase
    > Set conn = New ADODB.Connection
    > conn.ConnectionString = _
    > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=" & dbpath & ";" & _
    > "Persist Security Info=False"
    > conn.Open
    >Set cat.ActiveConnection = conn
    >Set tbl = cat.Tables.Item("Sales")
    >tbl.Columns.Append "Time_Created", adDate
    >tbl.Columns.Append "Tran_Ref", adDouble
    >tbl.Columns.Append "Product_Name", adVarWChar
    >tbl.Columns.Append "Note", adVarWChar
    >
    >tbl.Columns("Time_Created").Attributes = adColNullable
    >tbl.Columns("Tran_Ref").Attributes = adColNullable
    >tbl.Columns("Product_Name").Attributes = adColNullable
    >tbl.Columns("Note").Attributes = adColNullable
    >
    >tbl.Columns("Time_Created").Properties("Jet OLEDB:Allow Zero Length").Value
    >= True
    >tbl.Columns("Tran_Ref").Properties("Jet OLEDB:Allow Zero Length").Value

    =
    >True
    >tbl.Columns("Product_Name").Properties("Jet OLEDB:Allow Zero Length").Value
    >= True
    >tbl.Columns("Note").Properties("Jet OLEDB:Allow Zero Length").Value = True
    >
    >conn.Close
    >Set conn = Nothing
    >Set tbl = Nothing
    >Set cat = Nothing
    >



  4. #4
    Paul Clement Guest

    Re: Adding columns to an existing table in Access

    On 16 Apr 2002 10:16:08 -0800, "Dave" <vb.@127.0.0.1> wrote:


    Paul- Thanks for the response and sorry to be a pain, but how would I be able
    to set the adColNullable property before adding the column? I presume the
    syntax is quite different?


    Here is an example that should help:

    Sub AddColumnsWithADOX()

    Dim tbl As New 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("Table1")
    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


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

  5. #5
    Dave Guest

    Re: Adding columns to an existing table in Access


    Paul- Thanks for the response and sorry to be a pain, but how would I be able
    to set the adColNullable property before adding the column? I presume the
    syntax is quite different?

    Roger - Thanks for the response - it works fine for me when creating a new
    database too, but when I try to add the columns it fails...

    "Dave" <vb.@127.0.0.1> wrote:
    >
    >Hi all
    >
    >I'm trying to add some new columns to an existing Access database, but get
    >a "multiple step operation generated errors" error each time I try to set
    >the adColNullable property - the rest of the code works fine without this
    >part and I know that it works fine for creating a new table too, but not
    >in this case of adding columns to an existing table... anyone any ideas

    what
    >I'm doing wrong? The code I'm using is below.
    >
    >Many thanks in advance,
    >Dave
    >
    >Dim conn As New ADODB.Connection
    >Dim cat As New adox.Catalog
    >Dim tbl As New adox.Table
    >Dim col As New adox.Column
    >
    >dbpath = PathToDatabase
    > Set conn = New ADODB.Connection
    > conn.ConnectionString = _
    > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=" & dbpath & ";" & _
    > "Persist Security Info=False"
    > conn.Open
    >Set cat.ActiveConnection = conn
    >Set tbl = cat.Tables.Item("Sales")
    >tbl.Columns.Append "Time_Created", adDate
    >tbl.Columns.Append "Tran_Ref", adDouble
    >tbl.Columns.Append "Product_Name", adVarWChar
    >tbl.Columns.Append "Note", adVarWChar
    >
    >tbl.Columns("Time_Created").Attributes = adColNullable
    >tbl.Columns("Tran_Ref").Attributes = adColNullable
    >tbl.Columns("Product_Name").Attributes = adColNullable
    >tbl.Columns("Note").Attributes = adColNullable
    >
    >tbl.Columns("Time_Created").Properties("Jet OLEDB:Allow Zero Length").Value
    >= True
    >tbl.Columns("Tran_Ref").Properties("Jet OLEDB:Allow Zero Length").Value

    =
    >True
    >tbl.Columns("Product_Name").Properties("Jet OLEDB:Allow Zero Length").Value
    >= True
    >tbl.Columns("Note").Properties("Jet OLEDB:Allow Zero Length").Value = True
    >
    >conn.Close
    >Set conn = Nothing
    >Set tbl = Nothing
    >Set cat = Nothing
    >



  6. #6
    Dave Guest

    Re: Adding columns to an existing table in Access


    Thanks a million Paul - I'd found several methods on the web but all gave
    me errors, but this one works a treat.

    Thanks again,
    Dave


    Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
    >On 16 Apr 2002 10:16:08 -0800, "Dave" <vb.@127.0.0.1> wrote:
    >
    >
    > Paul- Thanks for the response and sorry to be a pain, but how would I

    be able
    > to set the adColNullable property before adding the column? I presume

    the
    > syntax is quite different?
    >
    >
    >Here is an example that should help:
    >
    >Sub AddColumnsWithADOX()
    >
    >Dim tbl As New 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("Table1")
    >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
    >
    >
    >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