A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Errors when deleteing table using VBA



 
 
Thread Tools Display Modes
  #1  
Old November 19th, 2008, 09:28 PM posted to microsoft.public.access.tablesdbdesign
Timothy M. Herrick
external usenet poster
 
Posts: 1
Default 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  
Old November 19th, 2008, 09:52 PM posted to microsoft.public.access.tablesdbdesign
boblarson
external usenet poster
 
Posts: 886
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:35 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.