Module: TableModifyDAO in Category Access/Jet Databases : Table and Records from Total Visual SourceBook

Modify Microsoft Access tables to add, delete and rename fields; set starting number of autonumber fields; set table property for performance; and set field properties using VBA and VB6.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the modTableModifyDAO module.
TableAddField Procedure Add a field to the table with many options.
TableDeleteField Procedure Delete a field from a table.
TableRenameField Procedure Rename a field in a table.
TableFieldSetAutoNumberValue Procedure The autonumber value for an Access/Jet table starts at 1. This procedure sets it to a higher value and assumes the table is empty. For more information on this technique, see Microsoft Access Tip: Setting an AutoNumber Field to Start with a Number Greater than 1.
SetFieldDefaults_AllTables Procedure Go through all the tables in the current database and set the default settings.
SetFieldDefaults_OneTable Procedure Go through all the fields of a table and set the appropriate default settings. Assign Attachment field captions, use CheckBox to display Yes/No fields, set AllowZeroLength strings for text fields.
TableSetSubDatasheetName Procedure Changes a table's SubDataSheetName property from [Auto] to [None]. [Auto] is the default setting. By changing it to [None], the speed in loading the table can be improved significantly.
ChangeObjectPropertyDAO Procedure Set the property value of an object if it doesn't exist or equals a value to replace.
SetObjectPropertyDAO Procedure Set the property value of an object and create it if it doesn't already exist. Used to set display type of a field, table properties, etc.
' Example of modTableModifyDAO
'
' To use this example, create a new module and paste this code into it.
' Then run the procedure by putting the cursor in the procedure and pressing:
'    F5 to run it, or
'    F8 to step through it line-by-line (see the Debug menu for more options)

Private Sub Example_modTableModifyDAO()
  ' Comments: Examples of using the modTableModifyDAO module to modify table fields and properties in VBA and VB6.

  Const cstrSampleDB As String = "C:\Total Visual SourceBook 2013\Samples\sample.mdb"
  Const cstrSampleTbl As String = "Categories"
  Const cstrSampleFld As String = "CategoryID"
  Const cstrNewField1 As String = "NewField"
  Const cstrNewField2 As String = "Status"
  Const cstrNewField3 As String = "Renamed Field"

  Dim strError As String
  Dim dbs As DAO.Database
  Dim varValue As Variant
  Dim tdf As DAO.TableDef
  Dim fOK As Boolean

  Set dbs = CurrentDb
  Set tdf = dbs.TableDefs("tblErrorLog")
  fOK = TableRenameField(tdf, "Email", "EmailAddress")

  Set dbs = DBEngine.OpenDatabase(cstrSampleDB)

  ' In these examples, the message boxes when the process succeeds do not appear since the next prompt appears.
  ' You can uncomment them if you want confirmation after each step.

  ' Use TableFieldSetAutoNumberValue to set a higher new value for an AutoNumber field
  If MsgBox("Do you want to change the AutoNumber value for the " & cstrSampleTbl & " table?", vbYesNo) = vbYes Then
    varValue = InputBox("New AutoNumber value (must be higher than the current new value):")
    If varValue <> "" Then
      If IsNumeric(varValue) Then
        If TableFieldSetAutoNumberValue(dbs, cstrSampleTbl, cstrSampleFld, CLng(varValue)) Then
          'MsgBox "AutoNumber value for " & cstrSampleTbl & " changed."
        Else
          MsgBox "AutoNumber value for " & cstrSampleTbl & " could not be changed."
        End If
      End If
    End If
  End If

  ' Use TableFieldSetAutoNumberValue to set a higher new value for an AutoNumber field
  If MsgBox("Do you want to add a field to the " & cstrSampleTbl & " table?", vbYesNo) = vbYes Then
    Set tdf = dbs.TableDefs(cstrSampleTbl)
    ' Add a text field
    fOK = TableAddField(tdf, cstrNewField1, dbText, 255, False, False, False)
    If fOK Then
      fOK = TableAddField(tdf, cstrNewField2, dbBoolean, 0, False, False, False)
    End If
    Set tdf = Nothing

    If fOK Then
      'MsgBox "Two fields were added to " & cstrSampleTbl
      If MsgBox("Do you want to rename one of the fields we just added to the " & cstrSampleTbl & " table?", vbYesNo) = vbYes Then
        fOK = TableRenameField(tdf, cstrNewField2, cstrNewField3)
        If fOK Then
          'MsgBox "TableRenameField succeeded"
        Else
          MsgBox "TableRenameField failed"
        End If
      End If

      If MsgBox("Do you want to delete the fields we added to the " & cstrSampleTbl & " table?", vbYesNo) = vbYes Then
        ' Delete the first field we added
        fOK = TableDeleteField(tdf, cstrNewField1)
        If fOK Then
          ' Delete the second field we added. That may or may not exist depending on whether we renamed it.
          ' Regardless, this routine will return True if the field isn't there.
          fOK = TableDeleteField(tdf, cstrNewField2)
          If fOK Then
            ' Now we'll delete the third field which may or may not exist depending on your earlier selection.
            fOK = TableDeleteField(tdf, cstrNewField3)
          End If
        End If
      End If
      Set tdf = Nothing

    Else
      MsgBox "TableAddField failed when adding fields to " & cstrSampleTbl
    End If
  End If

  ' Use TableSetSubDatasheetName to change the SubDataSheetName property for a table which can significantly improve how quickly the table loads
  If MsgBox("Do you want to clear the SubDataSheetName for the " & cstrSampleTbl & " table?", vbYesNo) = vbYes Then
    strError = TableSetSubDatasheetName(dbs, cstrSampleTbl)
    If strError = "" Then
      'MsgBox "SubDataSheetName for " & cstrSampleTbl & " cleared."
    Else
      MsgBox "SubDataSheetName for " & cstrSampleTbl & " could not be cleared: " & vbCrLf & strError
    End If
  End If

  ' Set the default field properties for one table: assign Attachment field captions, use CheckBox to display Yes/No fields, set AllowZeroLength strings for text fields
  If MsgBox("Do you want to set default field properties to your " & cstrSampleTbl & " table?", vbYesNo) = vbYes Then
    Set tdf = dbs.TableDefs(cstrSampleTbl)
    strError = SetFieldDefaults_OneTable(tdf)
    Set tdf = Nothing

    If strError = "" Then
      'MsgBox "SetFieldDefaults_OneTable for " & cstrSampleTbl & " succeeded."
    Else
      MsgBox "SetFieldDefaults_OneTable for " & cstrSampleTbl & " failed: " & vbCrLf & strError
    End If
  End If

  ' Set the default field properties for all the non-linked and non-system tables in the database
  If MsgBox("Do you want to set the default field properties for all your local, non-system tables in your database?", vbYesNo) = vbYes Then
    strError = SetFieldDefaults_AllTables(dbs)
    If strError = "" Then
      'MsgBox "SetFieldDefaults_AllTables for " & dbs.name & " succeeded."
    Else
      MsgBox "SetFieldDefaults_AllTables for " & dbs.name & " failed: " & vbCrLf & strError
    End If
  End If

  ' Clean up
  dbs.Close
  Set dbs = Nothing

End Sub

Total Visual SourceBook The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!


View all FMS products for Microsoft Access All Our Microsoft Access Products

Reviews

Reader Choice Award for MS Access Source Code Library
Reader Choice

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

SourceBook Info

Additional Info

Question

 

 

Free Product Catalog from FMS