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
    Posts
    3

    Using VB to add a field to an existing table

    Hello,

    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)?

    Thanks

    Here's the relevant part of the code:

    Dim WorkingTableStruct As TableDef
    Dim T1Name As String
    T1Name="Table1"
    '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
    Posts
    2,750
    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.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Phil Weber
    http://www.philweber.com

    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
    Posts
    3
    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
    Posts
    3
    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
    Posts
    2,750
    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
    Paul
    ~~~~
    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
 
 
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