At work I have a Microsoft Access database of employees with fields such as employee's manager, employees email, and employee ID. My duties include updating this database and validating entries generated. Biweekly when I run the VBA script to validate records, I am faced with between 25K and 30K of employee records in the table. A typical validation function in VBA is below:
Public Function validateEmployeeManager(empID As String, empStatus as String, empManager As String) If ((empStatus = "Active") And isNull(empManager)) Then validateEmployeeManager = "Manager is invalid for employee" & " " & empID Else validateEmployeeManager = "Valid manager" End If
Calling the above in a calculated field in a query:
I have noticed code such as the above takes quite a bit of time to run and display the results, given the size of the database.
Is there a more efficient method of running this validation?