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

Perform data operations through DAO in VBA and VB6.

Although queries should be considered the primary mechanism for working with data in Access, there are many places where complex needs eliminate queries as an option.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the modDataOperations module.
BrowseTableIndexOrder Procedure Process the records in a table in the order of the specified index. The purpose of this function is to illustrate how to move through a table in index order.
CanOpenTable Procedure Determine if the named table can be opened by attempting to open a Recordset object on the table. Use this function to test the availability of a table before attempting to open it. For example, the table may be attached incorrectly, or may be exclusively locked by another user.
ChangeColumnCase Procedure Change the case of a field for all records in the specified table.
ChangeDataSequential Procedure Search for data in the specified field and change it to the specified value.
ChangeValueInKeyedTable Procedure Change a value in a table with a primary key using the Index property of a DAO recordset and the Seek method to find the record. If found, it changes the value in the field to the specified value. This method can only be used on recordsets of table type which is the only type that supports using indexes and the DAO Seek method. For information on the Seek method on linked (attached) tables, see the SeekLinkedTable method in this module.
DataToTextFile Procedure Copy data from the named table or query in the current database to the named text file. This function uses the Access TransferText action to copy the contents of the named text or query to a text file. The fDelimited parameter specifies the format of the output. If this parameter is set to True, the text file is written with field delimiters. If set to False, the text file is written using Microsoft Word's Merge file format. If the file specified in the strFile parameter already exists, it is overwritten without confirmation.
DomainAverage Procedure Get the arithmetic mean of a set of values in a specified set of records. This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
DomainCount Procedure Get the number of records in a specified set of records. This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
DomainFirst Procedure Get the first occurrence field value in a specified set of records. This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
DomainLast Procedure Get the last occurrence field value in a specified set of records. This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
DomainLookup Procedure Get a field value in a specified set of records. This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
DomainMax Procedure Get the maximum value of a field in a specified set of records. This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
DomainMin Procedure Get the minimum value of a field in a specified set of records. This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
DomainStDev Procedure Get the standard deviation of a field in a specified set of records. This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
DomainSum Procedure Get the Sum of a field in a specified set of records. This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
DomainVar Procedure Get the variance of a field in a specified set of records. This procedure is functionally identical to the intrinsic function in Microsoft Access by the same name.
EmptyAllTables Procedure Delete all records from all tables in the current open database. System tables (such as the MSYS... tables) are not emptied.

*** CAUTION: this deletes all data from the database! ***

Remember that space used by deleted records is not automatically reclaimed by Access. You should periodically compact the database to reclaim this space.
EmptyTable Procedure Delete all records from the named table. This procedure works by running a SQL Delete query on the specified table. Note that mass changes on table data, such as deleting all records, leaves databases in a fragmented state. Be sure to run the Jet Engine's Compact method after such changes for optimal performance.
ExportTableToText Procedure Export the data from a table to a delimited text file, with one line per record.
FindDuplicates Procedure Create a table with all the duplicate values in a field of a table or query. This procedure creates a table called 'Duplicates in xxx' where xxx is the name of the table specified. This table contains one record for each duplicate value in the specified table. Use the strField parameter to specify which field is to be searched for duplicates. For example, if you have a Customers table with a field called LastName, specify the LastName field as the strField parameter to find all records with duplicate last names. Before running this method, ensure that the output table does not already exist in the database.
FindRandom Procedure Return the value of the specified field from a randomly selected record. FindRandom works by generating a random number to match the table's record number. It does this by multiplying the number of records in the table by the random number, adding one, and then converting the resulting value to a long integer to truncate decimals. It then uses the Move method to move to that random record.
GetTableSummary Procedure Calculate the summary specified for a field in a table. This procedure is similar to the Domain... procedures, but it wraps the most common functionality into one method.
IsRecordLocked Procedure Determine if the current record in the passed recordset is locked. This procedure works by checking the recordset's LockEdits property. If True, the recordset is using Pessimistic locking which means the record is locked as soon as Edit mode is invoked. If LockEdits is False, the recordset will not be locked until the Update is issued, in which case we can't determine the locked status of a record. It then checks the EditMode property to see if an edit is in progress, which means the record is locked.
IsTableEmpty Procedure Determine if the named table is empty (no records) by opening a dynaset-type recordset on the specified table, and then checking EOF and BOF to determine if the table contains any records.
RankTable Procedure Assign the rank of each record in a double field for that record. Tied records are averaged. For instance, if records 3, 4, 5 and 6 are tied, their rank is 4.5.
RecordCount Procedure Return the number of records in a table or returned by a query. This procedure uses a SELECT COUNT query to count the total records in the specified data source. The data source can be a table or query. If a query is specified, the query must be row-returning. Note the use of the SELECT COUNT (*) SQL syntax. This form tells the engine to consider all fields in all records which allows for certain internal optimizations that make the query run fast.
SearchAllFields Procedure Return the number of occurrences of the specified text in all fields of all records of the specified table or query. This procedure opens a dynaset-type recordset on the named table or row-returning query and loops through all fields in all records searching for the specified value.
SeekLinkedTable Procedure Use Seek on an index for a linked table. Using the DAO Seek method is generally the fastest way to find a specific value in a table because it uses the tables index(s) to search on. Unfortunately, the Seek method is only available on Table-type dynasets. And since you cannot open a table-type dynaset on a linked/attached table, there is no obvious way to use the Seek method on linked/attached tables. This procedure shows how to get around the problem. It does the following:
  1. Interrogates the Connect property of the specified linked table.
  2. Determines the path and name of the database where the linked table actually resides.
  3. Opens a Database object on that database.
  4. Opens a table-type recordset on the desired table through the newly opened database. Since the database object is open directly to the database where the linked table actually resides, the database object doesn't consider the table to be linked.
  5. Sets the recordset's Index property to the name of the index to search on.
  6. Uses the Seek method to find the specified value.
  7. Returns a True or False value depending on whether or not the specified value was found. Note that this procedure really doesn't do anything useful other than search for a value and illustrate the general technique. In real applications, you should modify this code to find the record(s) you want, and then perform an operation such as retrieving a field value, editing the record, etc.
' 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

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