5
\$\begingroup\$

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?

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

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
\$\endgroup\$
  • 2
    \$\begingroup\$ Yup. This. Skip VBA all together. It's much slower than the DAO Engine for this kind of thing. \$\endgroup\$ – RubberDuck Apr 17 '15 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.