current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 4 down vote favorite

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?

share|improve this question
2  
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
up vote 3 down vote

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
share|improve this answer
2  
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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