Why is Strdel (#) = "Table_Name" not deleting that table?

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

Guest

The small Access application I have runs a function as as event from a
command button that deletes tables created by a Make Table Queries to clean
up and prepare the application for new data to be imported. The function
successfully deletes all but one table named in the function, yet when I
re-open the application, go to Modules in the Database view, and then
"manually" run the function from there, the table is successfully deleted.
The table name in this issue is "ACTIVITY_CODES", and the function is written
as follows:

Function Clean_Up()

Dim Strdel(0 To 50) As String

Strdel(1) = "ASCH"
Strdel(2) = "SGAB"
Strdel(3) = "COLLECTED_DATA"
Strdel(4) = "INCLUSION_SEGMENTS"
Strdel(5) = "ACTIVITY_CODES"

Set dbcurrent = CurrentDb()
On Error Resume Next

For B = 1 To 5

dbcurrent.TableDefs.Delete Strdel(B)
DoEvents

Next B

Set dbcurrent = Nothing

End Function

What should I look for?
Thanks,
RC
 
RC said:
What should I look for?
Click to expand...

At the top of your module look for this:

Option Explicit

If it's not there, paste it in this module and all other modules which are
missing it, then go to Tools | Options and make sure Require Variable
Declarations is checked. Now save and compile your code and fix the errors
until it compiles successfully.

Next look for this:

On Error Resume Next

It means "I don't want to know if there's an error so don't tell me."
Replace it with an error handler in your code and then change the loop code
to this:

For B = 1 To 5
CurrentDb.Execute "DROP TABLE " & Strdel(B), dbFailOnError
DoEvents
Next B

Now when it fails, it'll tell you why. It's probably because that table is
still locked (or records in that table) when it executes, so by the time you
manually run the code that lock has been released and the deletion is
successful.
 
Tom said:
To add just a little to Granny's answer, you will need to include a reference
to the "Microsoft DAO 3.x Object Library", when you include the optional
dbFailOnError parameter.
Click to expand...

Drats! I forgot that. Thanks for the assist, Tom.

(And the DAO library is there by default in Access 97 and 2003, so it's 2000
and 2002 that usually need fixing.)
 
Thanks Granny, it was like you said the table was locked by a form. Although
I had the command button run a macro that would close the form before running
the code that would delete the table, I forgot that a macro seems to run
through a lot faster than a form can close. This should now be easy to work
around.
Thanks again,
RC
 
RC said:
Thanks Granny, it was like you said the table was locked by a form.
Click to expand...

You're welcome, RC. I'm pleased you found the problem and can fix it.
 
Back
Top