# Speeding up Access query with a calculated field calling a VBA function

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:

validateEmployeeManager([Employee_Name],[Employee_Status],[Employee_Manager]


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?

• No enough for a full answer, but it does beg the question of why you don't add field constraints to the database itself... – Comintern Apr 17 '15 at 4:13

Why don't you just put it in the query:

Query for employees with invalid managers:

Select [Employee_Name],[Employee_Status],[Employee_Manager]
FROM employees
WHERE [Employee_Status] = 'Active' AND isNull([Employee_Manager])


Query for employees with valid managers:

Select [Employee_Name],[Employee_Status],[Employee_Manager]
FROM employees
WHERE NOT ([Employee_Status] = 'Active' AND isNull([Employee_Manager]))


Query with the column displaying if the manager is valid or invalid

Select [Employee_Name],[Employee_Status],[Employee_Manager],
Iif([Employee_Status] = 'Active' AND isNull([Employee_Manager]), 'Invalid', 'Valid') as [Manager for employee]
FROM employees

• Yup. This. Skip VBA all together. It's much slower than the DAO Engine for this kind of thing. – RubberDuck Apr 17 '15 at 12:53