Cannot delete tables from code

G

Guest

I cannot figure this out.

I create 4 temp tables in code when a form is opened and assign the names of
these tables to tbl1, tbl2, tbl3 & tbl4. I've actually used made them public
from the form and global at the module level in my testing (not at the same
time).

When an error occurs, I run a bunch of code and then I use DoCmd.Quit to
close the app.

I am trying to delete the tables from the DB at some point, but I cannot.

I've tried the following code in both the form Close and form Unload events
(not at the same time). For some reason, the tbl1 - tbl4 variables null out,
so the below DROP TABLE statements won't work. It happens on Close and
Unload.

Me.sfrmMappedSAPRoleChangesAddRoles.Form.RecordSource = ""
Me.sfrmMappedSAPRoleChangesDeleteRoles.Form.RecordSource = ""
Me.sfrmMappedSAPRoleChangesDeleteUser.Form.RecordSource = ""
Me.sfrmMappedSAPRoleChangesNewUsers.Form.RecordSource = ""

DoCmd.RunSQL "DROP TABLE " & Tbl1 & ";"
DoCmd.RunSQL "DROP TABLE " & Tbl2 & ";"
DoCmd.RunSQL "DROP TABLE " & Tbl3 & ";"
DoCmd.RunSQL "DROP TABLE " & Tbl4 & ";"

I've also tried moving this code up to the button click event that has the
error code in it. I try to execute this code before the DoCmd.Quit, 3 of the
tables get deleted, but I get a runtime 3211 error message on the 4th table
that says the DB engine could not lock the table "TABLENAME" because it is
already in use by another person or process. This table is only referred to
by 1 subform.

That is why I have the code to set my 4 subforms to "" before I try to
delete the tables. Each of my temp tables is assigned to 1 of my 4 subforms.

I try one way, and my variables get nulled out and I try another way and the
4th table won't delete for some reason.

Can anyone tell me how I can delete my 4 temp tables when my form is closing?

For reference, here is the code in my form's Open event that makes the
tables and assigns them to my subs:

'Create temp tables used in role mapping changes proc -
cmdCommitChanges_Click.

Randomize

RandomValue = Format(Int(Rnd() * 10000), "0000")

Tbl1 = "TempAddRoles_" & fOSUserName & "_" & RandomValue
DoCmd.RunSQL "CREATE TABLE " & Tbl1 & "([UserGroup] TEXT(20),
[ExportNationality] TEXT(50), [EID] TEXT(15), [LastName] TEXT(75),
[FirstName] TEXT(75), [BusinessRole] TEXT(200), BusinessRoleID INTEGER);"

Tbl2 = "TempDeleteRoles_" & fOSUserName & "_" & RandomValue
DoCmd.RunSQL "CREATE TABLE " & Tbl2 & "([UserGroup] TEXT(20), [EID]
TEXT(15), [LastName] TEXT(75), [FirstName] TEXT(75), [BusinessRole]
TEXT(200), BusinessRoleID INTEGER);"

Tbl3 = "TempDeleteUser_" & fOSUserName & "_" & RandomValue
DoCmd.RunSQL "CREATE TABLE " & Tbl3 & "([UserGroup] TEXT(20), [EID]
TEXT(15), [LastName] TEXT(75), [FirstName] TEXT(75), [Delete] YESNO);"

Tbl4 = "TempNewUsers_" & fOSUserName & "_" & RandomValue
DoCmd.RunSQL "CREATE TABLE " & Tbl4 & "([UserGroup] TEXT(20),
[ExportNationality] TEXT(50), [EID] TEXT(15), [LastName] TEXT(75),
[FirstName] TEXT(75), [TimeZone] TEXT(50), [BusinessRole] TEXT(200),
BusinessRoleID INTEGER, [Add] YESNO);"

RefreshDatabaseWindow

'Set record source for all 4 subforms to newly created temp tables.

Me.sfrmMappedSAPRoleChangesAddRoles.Form.RecordSource = Tbl1
Me.sfrmMappedSAPRoleChangesDeleteRoles.Form.RecordSource = Tbl2
Me.sfrmMappedSAPRoleChangesDeleteUser.Form.RecordSource = Tbl3
Me.sfrmMappedSAPRoleChangesNewUsers.Form.RecordSource = Tbl4



Much thanks!
Clint Herman
 
D

Dirk Goldgar

cherman said:
I cannot figure this out.

I create 4 temp tables in code when a form is opened and assign the
names of these tables to tbl1, tbl2, tbl3 & tbl4. I've actually used
made them public from the form and global at the module level in my
testing (not at the same time).

When an error occurs, I run a bunch of code and then I use DoCmd.Quit
to close the app.

I am trying to delete the tables from the DB at some point, but I
cannot.

I've tried the following code in both the form Close and form Unload
events (not at the same time). For some reason, the tbl1 - tbl4
variables null out, so the below DROP TABLE statements won't work. It
happens on Close and Unload.

Me.sfrmMappedSAPRoleChangesAddRoles.Form.RecordSource = ""
Me.sfrmMappedSAPRoleChangesDeleteRoles.Form.RecordSource = ""
Me.sfrmMappedSAPRoleChangesDeleteUser.Form.RecordSource = ""
Me.sfrmMappedSAPRoleChangesNewUsers.Form.RecordSource = ""

DoCmd.RunSQL "DROP TABLE " & Tbl1 & ";"
DoCmd.RunSQL "DROP TABLE " & Tbl2 & ";"
DoCmd.RunSQL "DROP TABLE " & Tbl3 & ";"
DoCmd.RunSQL "DROP TABLE " & Tbl4 & ";"

I've also tried moving this code up to the button click event that
has the error code in it. I try to execute this code before the
DoCmd.Quit, 3 of the tables get deleted, but I get a runtime 3211
error message on the 4th table that says the DB engine could not lock
the table "TABLENAME" because it is already in use by another person
or process. This table is only referred to by 1 subform.

That is why I have the code to set my 4 subforms to "" before I try to
delete the tables. Each of my temp tables is assigned to 1 of my 4
subforms.

I try one way, and my variables get nulled out and I try another way
and the 4th table won't delete for some reason.

Can anyone tell me how I can delete my 4 temp tables when my form is
closing?
Click to expand...

This is just a guess, but it may be a timing problem. Running the code
from the button's Click event, so that the variables haven't been
cleared yet, insert a DoEvents statement -- or even two -- between
clearing the recordsources and dropping the tables; e.g.,

Me.sfrmMappedSAPRoleChangesAddRoles.Form.RecordSource = ""
Me.sfrmMappedSAPRoleChangesDeleteRoles.Form.RecordSource = ""
Me.sfrmMappedSAPRoleChangesDeleteUser.Form.RecordSource = ""
Me.sfrmMappedSAPRoleChangesNewUsers.Form.RecordSource = ""

DoEvents

DoCmd.RunSQL "DROP TABLE " & Tbl1 & ";"
DoCmd.RunSQL "DROP TABLE " & Tbl2 & ";"
DoCmd.RunSQL "DROP TABLE " & Tbl3 & ";"
DoCmd.RunSQL "DROP TABLE " & Tbl4 & ";"
 
G

Guest

Hi Cherman,

try this DoCmd.DeleteObject acTable, "tbl1"...

Hope that help

Violette


cherman said:
I cannot figure this out.

I create 4 temp tables in code when a form is opened and assign the names of
these tables to tbl1, tbl2, tbl3 & tbl4. I've actually used made them public
from the form and global at the module level in my testing (not at the same
time).

When an error occurs, I run a bunch of code and then I use DoCmd.Quit to
close the app.

I am trying to delete the tables from the DB at some point, but I cannot.

I've tried the following code in both the form Close and form Unload events
(not at the same time). For some reason, the tbl1 - tbl4 variables null out,
so the below DROP TABLE statements won't work. It happens on Close and
Unload.

Me.sfrmMappedSAPRoleChangesAddRoles.Form.RecordSource = ""
Me.sfrmMappedSAPRoleChangesDeleteRoles.Form.RecordSource = ""
Me.sfrmMappedSAPRoleChangesDeleteUser.Form.RecordSource = ""
Me.sfrmMappedSAPRoleChangesNewUsers.Form.RecordSource = ""

DoCmd.RunSQL "DROP TABLE " & Tbl1 & ";"
DoCmd.RunSQL "DROP TABLE " & Tbl2 & ";"
DoCmd.RunSQL "DROP TABLE " & Tbl3 & ";"
DoCmd.RunSQL "DROP TABLE " & Tbl4 & ";"

I've also tried moving this code up to the button click event that has the
error code in it. I try to execute this code before the DoCmd.Quit, 3 of the
tables get deleted, but I get a runtime 3211 error message on the 4th table
that says the DB engine could not lock the table "TABLENAME" because it is
already in use by another person or process. This table is only referred to
by 1 subform.

That is why I have the code to set my 4 subforms to "" before I try to
delete the tables. Each of my temp tables is assigned to 1 of my 4 subforms.

I try one way, and my variables get nulled out and I try another way and the
4th table won't delete for some reason.

Can anyone tell me how I can delete my 4 temp tables when my form is closing?

For reference, here is the code in my form's Open event that makes the
tables and assigns them to my subs:

'Create temp tables used in role mapping changes proc -
cmdCommitChanges_Click.

Randomize

RandomValue = Format(Int(Rnd() * 10000), "0000")

Tbl1 = "TempAddRoles_" & fOSUserName & "_" & RandomValue
DoCmd.RunSQL "CREATE TABLE " & Tbl1 & "([UserGroup] TEXT(20),
[ExportNationality] TEXT(50), [EID] TEXT(15), [LastName] TEXT(75),
[FirstName] TEXT(75), [BusinessRole] TEXT(200), BusinessRoleID INTEGER);"

Tbl2 = "TempDeleteRoles_" & fOSUserName & "_" & RandomValue
DoCmd.RunSQL "CREATE TABLE " & Tbl2 & "([UserGroup] TEXT(20), [EID]
TEXT(15), [LastName] TEXT(75), [FirstName] TEXT(75), [BusinessRole]
TEXT(200), BusinessRoleID INTEGER);"

Tbl3 = "TempDeleteUser_" & fOSUserName & "_" & RandomValue
DoCmd.RunSQL "CREATE TABLE " & Tbl3 & "([UserGroup] TEXT(20), [EID]
TEXT(15), [LastName] TEXT(75), [FirstName] TEXT(75), [Delete] YESNO);"

Tbl4 = "TempNewUsers_" & fOSUserName & "_" & RandomValue
DoCmd.RunSQL "CREATE TABLE " & Tbl4 & "([UserGroup] TEXT(20),
[ExportNationality] TEXT(50), [EID] TEXT(15), [LastName] TEXT(75),
[FirstName] TEXT(75), [TimeZone] TEXT(50), [BusinessRole] TEXT(200),
BusinessRoleID INTEGER, [Add] YESNO);"

RefreshDatabaseWindow

'Set record source for all 4 subforms to newly created temp tables.

Me.sfrmMappedSAPRoleChangesAddRoles.Form.RecordSource = Tbl1
Me.sfrmMappedSAPRoleChangesDeleteRoles.Form.RecordSource = Tbl2
Me.sfrmMappedSAPRoleChangesDeleteUser.Form.RecordSource = Tbl3
Me.sfrmMappedSAPRoleChangesNewUsers.Form.RecordSource = Tbl4



Much thanks!
Clint Herman
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Database Design Help 7
cannot delete from specified tables 7

Top