Add a field to a table using VBA

Posted on 2006-04-10
Last Modified: 2013-04-10
Dear All,

How can I add a field to the end of my table using a VBA script?

Question by:saulius88
    LVL 17

    Expert Comment

    you can use a  DDL string


    Dim strSQL as String
    strSQL = "ALTER TABLE MyTable ADD Column MyColumn Text(20)"
    Currentdb.Execute strSQL

    Where MyTable is the name of your table and MyColumn is the name of your field
    You will have to change Text(20) depending on the data type and size of the field you want

    Author Comment

    Thanks for the post. Is there any way I could also check if the field already exists?
    LVL 3

    Accepted Solution

    This should do it!

    Dim db as database
    dim found as boolean

    set db = currentdb

    i = 0
    while not i = db.tabledefs("MyTable").fields.count - 1
      if  db.tabledefs("MyTable").fields(i) = "MyColumn" then
        found = true
        i = db.tabledefs("MyTable").fields.count - 1
        found = false
        i = i + 1
      end if

    if found <> true then
      strSQL = "ALTER TABLE MyTable ADD Column MyColumn Text(20)"
      Currentdb.Execute strSQL
    end if

    Author Comment

    Thanks a bunch.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Suggested Solutions

    Title # Comments Views Activity
    query error in access 2010 dao 3.6 5 27
    MsgBox 2 27
    Show Balance 3 15
    message box formulas 3 16
    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now