Delete records with VBA

[Jen]

I have a query that shows terminated employees that need to be deleted from a
specific table in the database. This query runs off of three tables (two are
not updatable, one is updatable). I have a form that displays the names of
the employees to be deleted in case there are errors that need to be
corrected. On this form I have a command button that I would like to put code
on to delete the correct records. However, I have no idea how to actually
create this. The records would be determined by matching the ID # in the
query to the ID# in the CorporateEmployee table. I tried a delete query
already but it didn't work because the tables it ran off of were not
updatable... Help?

 

[Graham Mandeno]

Hi Jen

I assume the query that lists the employees to be deleted has selection
criteria based on fields in the CorporateEmployees table. (This selection
will be in the form of a WHERE clause in your query).

Your VBA needs to construct a SQL string which includes that same WHERE
clause, then use the Execute method against a database object:

Dim db as DAO.Database
Dim sSQL as string
Set db = CurrentDb
sSQL = "Delete * from CorporateEmployees where ... "
db.Execute sSQL, dbFailOnError
MsgBox db.RecordsAffected & " employees have been deleted"

Just a thought: do you really want to delete these records completely?
This will lose any history you have about these employees. Would it not be
better to add a yes/no field "Inactive" and use this field to filter out any
non-current employees from forms and reports?

 

[Jen]

Thank you Graham.

In answer to your question about permanently deleting the employees - this
particular table is a local work around to the way my company keeps employees
in the main employee table. The locations that they have them at are physical
locations instead of payroll designations. Unfortunately in my department, we
need to know the payroll designation of the employees so that we can
correctly sort their stats to the proper customer service manager (and two
groups share the same physical location - Corporate & Illinois). So, making a
short story long, this won't actually delete all of their information for the
entire company, it just takes them out of this specific table (which is nice
for when they transfer to a different department).

Thanks again for all the help!

Hi Jen

I assume the query that lists the employees to be deleted has selection
criteria based on fields in the CorporateEmployees table. (This selection
will be in the form of a WHERE clause in your query).

Your VBA needs to construct a SQL string which includes that same WHERE
clause, then use the Execute method against a database object:

Dim db as DAO.Database
Dim sSQL as string
Set db = CurrentDb
sSQL = "Delete * from CorporateEmployees where ... "
db.Execute sSQL, dbFailOnError
MsgBox db.RecordsAffected & " employees have been deleted"

Just a thought: do you really want to delete these records completely?
This will lose any history you have about these employees. Would it not be
better to add a yes/no field "Inactive" and use this field to filter out any
non-current employees from forms and reports?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a query that shows terminated employees that need to be deleted from
a
specific table in the database. This query runs off of three tables (two
are
not updatable, one is updatable). I have a form that displays the names of
the employees to be deleted in case there are errors that need to be
corrected. On this form I have a command button that I would like to put
code
on to delete the correct records. However, I have no idea how to actually
create this. The records would be determined by matching the ID # in the
query to the ID# in the CorporateEmployee table. I tried a delete query
already but it didn't work because the tables it ran off of were not
updatable... Help?

 

[John W. Vinson]

In answer to your question about permanently deleting the employees - this
particular table is a local work around to the way my company keeps employees
in the main employee table. The locations that they have them at are physical
locations instead of payroll designations. Unfortunately in my department, we
need to know the payroll designation of the employees so that we can
correctly sort their stats to the proper customer service manager

I would be inclined to handle this by - not copying the entire employee table
- but by adding a lookup table with EmployeeID and PayrollDesignation fields;
if you Join this table to the existing employee table, you won't need to
maintain the redundant employee data.