Module: DataOperations in Category Microsoft Access : Objects from Total Visual SourceBook

' Example of modDataOperations
'
' 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_modDataOperations()
  ' Comments: Example of the modDataOperations module to perform data operations through DAO in VBA and VB6.
  '           See results in the Immediate Window.

  ' This example assumes that the sample files are located in the folder named by the following constant.
  Const cstrSamplePath As String = "C:\Total Visual SourceBook 2013\Samples\"
  Const cstrOutputFile As String = cstrSamplePath & "DAOTEST.TXT"
  Const cstrSampleTable As String = "DataOperations_Table1"

  Dim rst As DAO.Recordset
  Dim lngRecords As Long
  Dim fReturn As Boolean
  Dim varValue As Variant

  ' Clean up any temp files from a previous run of this example
  On Error Resume Next
  DoCmd.DeleteObject acTable, cstrSampleTable
  Kill cstrOutputFile
  On Error GoTo 0

  ' Create the sample tables to use for this code
  CreateSampleTable cstrSampleTable

  ' Browse the table in the order of its PrimaryKey Index
  Debug.Print "BrowseTableIndexOrder: " & BrowseTableIndexOrder(CurrentDb, cstrSampleTable, "PrimaryKey")

  ' Change the ContactName field in the sample table to upper case
  Debug.Print "ChangeColumnCase: " & ChangeColumnCase(CurrentDb, cstrSampleTable, "Name", True) & " records changed."

  ' Change each occurrence of 'John' in the Name field to 'Johnathan'.
  Debug.Print "ChangeDataSequential: " & ChangeDataSequential(CurrentDb, cstrSampleTable, "Name", "'John'", "Name", "Johnathan", False) & " records changed."

  ' Find the record in the sample table whose PrimaryKey value is 1, and change the value of the Name field
  Debug.Print "ChangeValueInKeyedTable: " & ChangeValueInKeyedTable(CurrentDb, cstrSampleTable, "1", "Name", "Zoe")

  ' Get the average value of the Score field for records where the Score is >0.
  varValue = DomainAverage(CurrentDb, "Score", cstrSampleTable, "Score > 0")
  Debug.Print "DomainAverage: Average Score = " & varValue

  ' Get the count of records with a score > 0
  varValue = DomainCount(CurrentDb, "ID", cstrSampleTable, "Score>0")
  Debug.Print "DomainCount: Count of Scores > 0 = " & varValue

  ' Get the value of the Name field for the first non-blank record
  varValue = DomainFirst(CurrentDb, "Name", cstrSampleTable, "nz(name)>''")
  Debug.Print "DomainFirst: First Name Found = " & varValue

  ' Get the value of the Name field for the last non-blank record
  varValue = DomainLast(CurrentDb, "Name", cstrSampleTable, "nz(name)>''")
  Debug.Print "DomainLast: Last Name Found = " & varValue

  ' Find the record with ID of 2 and return the value for the Name field.
  varValue = DomainLookup(CurrentDb, "ID", cstrSampleTable, "ID=2")
  Debug.Print "DomainLookup: Name field for ID #2 = " & varValue

  ' Get the maximum of the Score field
  varValue = DomainMax(CurrentDb, "Score", cstrSampleTable, "")
  Debug.Print "DomainMax: Max score = " & varValue

  ' Get the minimum of the Score field
  varValue = DomainMin(CurrentDb, "Score", cstrSampleTable, "")
  Debug.Print "DomainMin: Min score = " & varValue

  ' Get the standard deviation of the Score field where the score > 0
  varValue = DomainStDev(CurrentDb, "Score", cstrSampleTable, "Score>0")
  Debug.Print "DomainStDev: Standard Deviation = " & varValue

  ' Get the Sum of the Score field
  varValue = DomainSum(CurrentDb, "Score", cstrSampleTable, "")
  Debug.Print "DomainSum: Sum of scores = " & varValue

  ' Get the variance of the Score field
  varValue = DomainVar(CurrentDb, "Score", cstrSampleTable, "")
  Debug.Print "DomainVar: Variance = " & varValue

  ' EmptyAllTables empties ALL TABLES in the current database.
  ' Only uncomment these lines if you have a current backup of your database.
  'If MsgBox("This will empty ALL TABLES in the database!!! Do you want to continue?", vbYesNo) = vbYes Then
  '  Call EmptyAllTables(CurrentDb)
  'End If

  ' Export the records in a sample table to a text file using a semicolon as the field delimiter.
  lngRecords = ExportTableToText(CurrentDb, cstrSampleTable, cstrOutputFile, ";")
  Debug.Print "ExportTableToText: " & lngRecords & " records from table " & "exported to file."

  ' Find duplicates in the sample table based on the Name field.
  ' This example creates a table called Duplicate Names. If this table already exists in the sample database, an error is returned.
  varValue = FindDuplicates(CurrentDb, cstrSampleTable, "Duplicate Names", "Name")
  Debug.Print "FindDuplicates: duplicates for table stored in " & varValue

  ' Pick a random Score from the sample table
  varValue = FindRandom(CurrentDb, cstrSampleTable, "Score")
  Debug.Print "FindRandom: returned " & varValue

  ' Get the average of scores using the GetTableSummary method.
  varValue = GetTableSummary(CurrentDb, tscmAverage, cstrSampleTable, "Score")
  Debug.Print "GetTableSummary: Average score = " & varValue

  ' Open a recordset and lock a record.
  ' Then call the IsRecordLocked method to test to see if it is locked.
  Set rst = CurrentDb.OpenRecordset(cstrSampleTable)
  With rst
    .MoveFirst
    .LockEdits = True
    .Edit
    ' Record is now locked
  End With
  fReturn = IsRecordLocked(rst)
  Debug.Print "IsRecordLocked: first record in sample table " & IIf(fReturn, "is locked.", "is not locked.")
  rst.Close

  ' See if the sample table is empty
  fReturn = IsTableEmpty(CurrentDb, cstrSampleTable)
  Debug.Print "IsTableEmpty: The sample table " & IIf(fReturn, "is", "is not") & " empty."

  ' Count the records in the sample table
  varValue = RecordCount(CurrentDb, cstrSampleTable)
  Debug.Print "RecordCount() There are " & varValue & " records in the sample " & "table. "

  Debug.Print "EmptyTable: " & EmptyTable(CurrentDb, cstrSampleTable)

  ' See if the sample table is empty
  fReturn = IsTableEmpty(CurrentDb, cstrSampleTable)
  Debug.Print "IsTableEmpty: The sample table " & IIf(fReturn, "is", "is not") & " empty."

  ' Clean up any temp files
  On Error Resume Next
  DoCmd.DeleteObject acTable, cstrSampleTable
  DoCmd.DeleteObject acTable, "Duplicate Names"
  Kill cstrOutputFile
  On Error GoTo 0

End Sub

Private Sub CreateSampleTable(ByVal strTableName As String)
  ' Comments: Create the sample tables to use for this code

  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Dim fld As DAO.Field
  Dim idx As DAO.Index
  Dim rst As DAO.Recordset

  Set dbs = CurrentDb
  Set tdf = dbs.CreateTableDef(strTableName)
  Set fld = tdf.CreateField("ID", dbLong)

  fld.Attributes = dbAutoIncrField
  tdf.Fields.Append fld

  Set fld = tdf.CreateField("Name", dbText, 50)
  tdf.Fields.Append fld

  Set fld = tdf.CreateField("Score", dbInteger, 50)
  tdf.Fields.Append fld

  dbs.TableDefs.Append tdf
  dbs.TableDefs.Refresh

  Set idx = tdf.CreateIndex("PrimaryKey")
  idx.Primary = True

  Set fld = idx.CreateField("ID", dbLong)
  idx.Fields.Append fld
  tdf.Indexes.Append idx

  ' Insert some rows into the sample table
  Set rst = dbs.OpenRecordset(strTableName, dbOpenDynaset)
  rst.AddNew
  rst!name = "Zoey"
  rst!Score = 93
  rst.Update
  rst.AddNew
  rst!name = "Andy"
  rst!Score = 81
  rst.Update
  rst.AddNew
  rst!name = "John"
  rst!Score = 0
  rst.Update
  rst.AddNew
  rst!name = "John"
  rst!Score = 88
  rst.Update

  rst.Close
  Set rst = Nothing
  Set idx = Nothing
  Set tdf = Nothing
  Set dbs = Nothing

  ' Copy the table to a new table name
  'DoCmd.CopyObject , cstrChangeTable1, acTable, cstrSampleTable

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)