Total Visual SourceBook

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 2007 and VB6

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

Separate version for Access/Office 2003, 2002, and 2000.

Separate version for:
Access 97/95


View all FMS products for Microsoft AccessAll Our Microsoft Access Products

SourceBook Info:

Why SourceBook?

 

"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

 

 

 

Microsoft Access, Office/VBA, VB6 Modules royalty-free source code libraryMicrosoft Access Modules royalty-free source code libraryMicrosoft Access VBA and VB6 Module Library Free trial of Total Visual SourceBook for Microsoft Access, Office, VB6 and VBA

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 List

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 http://www.fmsinc.com/MicrosoftAccess/autonumber-field/
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 Code for Using Module: TableModifyDAO

' 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

Microsoft Access Module LibraryOverview of 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.

Additional Resources