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

' 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

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

• List of All Modules in Total Visual SourceBook
• List of All Code Categories in Total Visual SourceBook
• Sample Module Code from Total Visual SourceBook (Example of Date, Time and Age Calculations)