T
Timothy M. Herrick
I am developing a database for sage grouse breeding areas, known as leks. I
am having trouble trying to delete a
table that has been populated with data using an update query. I have three
tables that I use in the following
manner:
T_SG_OBSERVATIONS - Permanently tores the data changes after a new survey of
a particular lek site.
T_ADD_SG_OBSERVATIONS - Acts as a temporary table for data entry referenced
by new data entry form.
T_ADD_TEMPLATE_SG_OBSERVATIONS - A blank table used to replace
T_ADD_SG_OBSERVATIONS after data entry.
The user opens a form with a command button located on a main switchboard,
this button closes the main switchboard
and runs an update query. The user enters a lek ID code to indicate the lek
site for which they wish to enter
survey data. The update query populates T_ADD_SG_OBSERVATIONS with
previously documented information about the
desired lek site. A data entry form opens displaying the data in
T_ADD_SG_OBSERVATIONS. The user modifies the data
as needed. They then click a save command button which runs VBA code to do
some calculations and append the new
record to T_SG_OBSERVATIONS. Then the code closes the form and attempts to
delete the table T_ADD_SG_OBSERVATIONS.
Then it is supposed to open the main swithcboard again and copy
T_ADD_TEMPLATE_SG_OBSERVATIONS in order to create a
new blank T_ADD_SG_OBSERVATIONS table. Basically it's a loop that creates a
temporary table to edit, saves changes,
then deletes and recreates again.
Originally I tried using macros to delete the table but got error 2950.
To avoid this I then used this line of VBA code (Microsoft Office Access
Class Objects):
DoCmd.DeleteObject acTable, "T_ADD_SG_OBSERVATIONS"
This executed after data entry was complete and the main switchboard was
opened again. I used the code for the On
Open property of the main switchboard and got error "3211 Couldn't lock
table 'Item'; currently in use."
I then removed this code from from the main switchboard On Open VBA
property, and placed it in the code for the On
Close properties for the data entry form.
Now for the same line of code I get "Run-time error '3326': This Recordset
is not updatable" when the data entry
form closes.
Does it have to do with the fact that the table I am trying to delete has
fields that use look up tables for data
entry or that it was populated with data from an update query?
Can I somehow supress the error and force the delete?
Can anybody help me figure out a way to delete this T_ADD_SG_OBSERVATIONS
table without getting an error?
Thank you for any help.
am having trouble trying to delete a
table that has been populated with data using an update query. I have three
tables that I use in the following
manner:
T_SG_OBSERVATIONS - Permanently tores the data changes after a new survey of
a particular lek site.
T_ADD_SG_OBSERVATIONS - Acts as a temporary table for data entry referenced
by new data entry form.
T_ADD_TEMPLATE_SG_OBSERVATIONS - A blank table used to replace
T_ADD_SG_OBSERVATIONS after data entry.
The user opens a form with a command button located on a main switchboard,
this button closes the main switchboard
and runs an update query. The user enters a lek ID code to indicate the lek
site for which they wish to enter
survey data. The update query populates T_ADD_SG_OBSERVATIONS with
previously documented information about the
desired lek site. A data entry form opens displaying the data in
T_ADD_SG_OBSERVATIONS. The user modifies the data
as needed. They then click a save command button which runs VBA code to do
some calculations and append the new
record to T_SG_OBSERVATIONS. Then the code closes the form and attempts to
delete the table T_ADD_SG_OBSERVATIONS.
Then it is supposed to open the main swithcboard again and copy
T_ADD_TEMPLATE_SG_OBSERVATIONS in order to create a
new blank T_ADD_SG_OBSERVATIONS table. Basically it's a loop that creates a
temporary table to edit, saves changes,
then deletes and recreates again.
Originally I tried using macros to delete the table but got error 2950.
To avoid this I then used this line of VBA code (Microsoft Office Access
Class Objects):
DoCmd.DeleteObject acTable, "T_ADD_SG_OBSERVATIONS"
This executed after data entry was complete and the main switchboard was
opened again. I used the code for the On
Open property of the main switchboard and got error "3211 Couldn't lock
table 'Item'; currently in use."
I then removed this code from from the main switchboard On Open VBA
property, and placed it in the code for the On
Close properties for the data entry form.
Now for the same line of code I get "Run-time error '3326': This Recordset
is not updatable" when the data entry
form closes.
Does it have to do with the fact that the table I am trying to delete has
fields that use look up tables for data
entry or that it was populated with data from an update query?
Can I somehow supress the error and force the delete?
Can anybody help me figure out a way to delete this T_ADD_SG_OBSERVATIONS
table without getting an error?
Thank you for any help.