DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Using VB to add a field to an existing table

  1. #1
    Join Date
    Aug 2006

    Using VB to add a field to an existing table


    I'm trying to run a function using visual basic that adds a field to an existing table. At the moment the bit of code I've written to do this uses a TableDef object to copy the table structure from an existing table (Table1), and then I've attempted to add a new field as shown below, unfortunately I get an error when it tries to add the new field (Runtime error 3420: Object invalid or no longer set), but I'm pretty sure the error's in the Set line.
    Can anyone help me make the code work or tell me an easier way of adding a field to an existing table using VB code (I can't seem to find much)?


    Here's the relevant part of the code:

    Dim WorkingTableStruct As TableDef
    Dim T1Name As String
    'Load table to update
    Set WorkingTableStruct = CurrentDb.TableDefs(T1Name)
    'Add extra field to
    With WorkingTableStruct
    .Fields.Append .CreateField("Authority ID", dbText, 20)
    End With
    'Shows new table in database
    CurrentDb.TableDefs.Append WorkingTableStruct

  2. #2
    Join Date
    Dec 2003
    I don't see a problem with the code until the very last statement. You're trying to add "Table1" to the database but it's already present.

    In any event, when an error occurs the line which generated the exception should be identified.
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Nov 2003
    Portland, OR
    Phil Weber

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  4. #4
    Join Date
    Aug 2006
    pclement- thanks for the help, but the error occurs on the line where I attempt to create the "Authority ID" Field, so it already stops before it gets to that last statement (not to say that there isn't a problem with that line)- perhaps I've made an error somewhere else in the coding I haven't shown. Also, I have actually been using a much longer table name than "Table1", so maybe there's a problem with that- I'll try it with Table1 as the name instead I think.

    Thanks Phil - I'll have a look at that site and see how it goes.

  5. #5
    Join Date
    Aug 2006
    Thanks Phil- I have changed the code so that I do not use the currentdb object directly now- and that stopped the original error

    Thanks PClement- The error you were talking about happened after I fixed the other problem, so I took that last line of code out and now it works exactly as I hoped- there's only one very minor problem now, and that is that the new field is shown at the end of the table (when I would like it to be the first field)- but it doesn't really affect any of the coding, so I'll probably leave that.

    Thanks again- that turned out to be easier than I expected.

  6. #6
    Join Date
    Dec 2003
    Looks like Phil had your answer, which I wouldn't have thought of since I haven't used CurrentDB in that scenario.

    I believe you can change the ordinal position of the column using the following code:

    .Fields("Authority ID").OrdinalPosition = 0 'first column in the table
    Microsoft MVP (Visual Basic)

Similar Threads

  1. Replies: 0
    Last Post: 05-30-2002, 03:07 PM
  2. Delete & Copy Access table thru VB
    By Sinni in forum VB Classic
    Replies: 0
    Last Post: 05-29-2002, 04:01 PM
  3. Replies: 2
    Last Post: 05-26-2002, 08:08 AM
  4. Truncate a field in table from VB
    By Dishna in forum Database
    Replies: 1
    Last Post: 01-30-2001, 06:11 AM
  5. Clearing Pivot table fields "memory" in VB?
    By Fred Giesen in forum authorevents.patrick
    Replies: 2
    Last Post: 09-06-2000, 05:17 PM

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
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

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