If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Errors when deleteing table using VBA
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. |
#2
|
|||
|
|||
Errors when deleteing table using VBA
Why delete the table and not just the data and then append the new data?
-- Bob Larson Access MVP Free Tutorials and Samples at http://www.btabdevelopment.com __________________________________ "Timothy M. Herrick" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|