Delete records with VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jen - 14 May 2008 20:43 GMT

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?

Reply to this Message

Graham Mandeno - 14 May 2008 22:19 GMT

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?

Signature

Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

>I have a query that shows terminated employees that need to be deleted from
>a
[quoted text clipped - 9 lines]
> already but it didn't work because the tables it ran off of were not
> updatable... Help?

Reply to this Message

Jen - 15 May 2008 16:03 GMT

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
>
[quoted text clipped - 29 lines]
> > already but it didn't work because the tables it ran off of were not
> > updatable... Help?

Reply to this Message

John W. Vinson - 16 May 2008 21:39 GMT

>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.

Signature

John W. Vinson [MVP]

Reply to this Message