Create a delete query macro?

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Hi, we use Access 2003 to hold our e-mail lists. Every month I have to
import new e-mail lists as tables the database, but before I can use those
tables, I must perform delete queries on them against our massive opt-out
table for customers who don't want our e-mails. Is there a way I could
create a macro that would allow me to simply highlight a table, push a
button, and perform the delete query using the highlighted table as
parameter?

I've searched the Net without success but I believe this must be something
fairly straightfoward. I'm hoping to see some examples. It would save me a
couple of hours each month and decrease the chance of errors. Thanks!
 
Hi, we use Access 2003 to hold our e-mail lists. Every month I have to
import new e-mail lists as tables the database, but before I can use those
tables, I must perform delete queries on them against our massive opt-out
table for customers who don't want our e-mails. Is there a way I could
create a macro that would allow me to simply highlight a table, push a
button, and perform the delete query using the highlighted table as
parameter?

I've searched the Net without success but I believe this must be something
fairly straightfoward. I'm hoping to see some examples. It would save me a
couple of hours each month and decrease the chance of errors. Thanks!
Click to expand...

A single Delete query joining the two tables would work:

DELETE Mailout.*
FROM Mailout INNER JOIN Optout ON Mailout.EMail = Optout.EMail;

should do the job, if EMail is the Primary Key of each table (or at
least has a unique Index on it).

If you're storing multiple identical tables - DON'T. Add another field
to indicate what subset of the mailing list you're using.

You cannot pass a tablename as a parameter; if you really want to have
tablenames as data, you must use VBA code to construct the Delete
query.

John W. Vinson[MVP]
 
Thanks, John. Your Query worked perfectly. The one I was using before was
much more complicated than was necessary. That definitely helps. I may look
into asking our programmer to create the VB code.

John Vinson said:
A single Delete query joining the two tables would work:

DELETE Mailout.*
FROM Mailout INNER JOIN Optout ON Mailout.EMail = Optout.EMail;

should do the job, if EMail is the Primary Key of each table (or at
least has a unique Index on it).
Click to expand...
<snip>
John W. Vinson[MVP]
 
Back
Top