title: Adding field to MS Access Table using VBA
body: <p>I need to add a calculated field to an existing table. I am aware of two ways to do this and I'm wondering if anyone has any input on which is best and how to make them work:</p>
  
  <ol>
  <li>Using TableDef.CreateField, then TableDef.Fields.Append</li>
  <li>Using a DDL Alter Table ADD COLUMN statement</li>
  </ol>
  
  <p>I tried using the first method, but I keep getting a 3211 error because Access could not lock the table. I don't have the table open. However, I am calling CreateField from a form that has accessed which fields currently exist in the table.</p>
  
  <p>Here's the code for calling CreateField:</p>
  
  <pre><code>`
  Public Sub AddFieldToTable(strTable As String, strField As String, nFieldType As     Integer)
  
      Dim db As DAO.Database
      Dim tdf As DAO.TableDef
      Dim fld As DAO.Field
  
      On Error GoTo ErrorHandler
  
      Set db = CurrentDb
      Set tdf = db.TableDefs(strTable)
      Set fld = tdf.CreateField(strField, nFieldType)
      tdf.Fields.Append fld
  
      MsgBox "The field named [" &amp; strField &amp; "] has been added to table [" &amp; strTable &amp; "]."
  
      Set tdf = Nothing
      Set db = Nothing
  
      Exit Sub
  
      ErrorHandler:
          MsgBox "An error has occurred. Number: " &amp; Err.Number &amp; ", description: " &amp;        Err.Description
          Exit Sub
  
  End Sub
  `
  </code></pre>
  
  <p>I get the error on the tdf.fields.append line. Would executing an ALTER TABLE statement be better? What are the tradeoffs?</p>