deleting tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to delete a table from a command button so that when user clicks
the command button, a table is delete and another table gets renamed the same
name as the deleted table. I have this database running an update query,
which populated 15 names with a given holiday based on the Holiday's date off
a lookup table. After the update query runs, then I have a union query
running to merge my main table with the holiday table. I also then have a
make table query running that creates a new table called temp. After temp is
created I need to delete the main table and rename temp to main table. I
want to do this so that when the update query runs again there will 15 more
entries than the last time it ran. This will done 12 times over until all 12
holidays are in the main table for each of the 15 names.

Matt
 
I don't like to delete tables(or use Make Table Queries), unless I have to.
I prefer to empty the table, then append to it.

Other than that, there sounds like there is room for improvement. Could be
a data structure issue, or the whole algorithm is askew.
 
I am trying to delete a table from a command button so that when user clicks
the command button, a table is delete and another table gets renamed the same
name as the deleted table. I have this database running an update query,
which populated 15 names with a given holiday based on the Holiday's date off
a lookup table. After the update query runs, then I have a union query
running to merge my main table with the holiday table. I also then have a
make table query running that creates a new table called temp. After temp is
created I need to delete the main table and rename temp to main table. I
want to do this so that when the update query runs again there will 15 more
entries than the last time it ran. This will done 12 times over until all 12
holidays are in the main table for each of the 15 names.
Click to expand...

Ummm... so you're running twelve MakeTable queries to do a job which
should be easily accomplished using a single Append query!?

MakeTable queries (and the corresponding need for deleting tables) are
rather rarely actually needed, and I cannot see why they would be
needed in this case.

I don't know the structure of your table, but a Cartesian join query
of the twelve holidays and fifteen names will give you all 180 rows
which can be appended in one shot, with no maketables or deletion or
code whatsoever:

INSERT INTO yourtable (datefield, EmployeeID)
SELECT Holidays.HolidayDate, Employees.EmployeeID
FROM Holidays, Employees
<WHERE Holidays.Year = Year(Date())>
<AND Employees.EmployeeID IN (<list of ID's>)>

with my speculative suggestions in <brackets>

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top