Deleting a db object

[ReportSmith]

I've looked at several posts, but can't seem to get exactly what I need.

I would like to delete any "*ImportErrors*" table that may get created after
a file is imported (doesn't matter what file type, etc). My thinking is to
search for any table object that ends with "ImportErrors" and try a
DeleteObject statement.

Currently, I have...

Dim dbCurr As DAO.Database
Dim intLoop As Integer
............
Set dbCurr = CurrentDb()
For intLoop = (dbCurr.TableDefs.Count - 1) To 0 Step -1
If Right(dbCurr.TableDefs(intLoop), 13) = "_ImportErrors" Then
dbCurr.TableDefs.Delete dbCurr.TableDefs(intLoop).Name
End If
Next intLoop
.............

I copied code from some other posts to get the one above. I also tried
(without success): DoCmd.DeleteObject dbCurr.TableDefs(intLoop).Name

Can anyone assist with the syntax?

Thanks in advance.

 

[Baz]

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb
For Each tdf In db.TableDefs
If Right(tdf.Name,13) = "_ImportErrors" Then
DoCmd.DeleteObject acTable, tdf.Name
End If
Next

n.b. when posting code problems it generally helps if you state:

1. What error you are getting (error number and error message)
2. What line the error occurs on

 

[Douglas J. Steele]

Actually, that code probably won't work as desired, Baz.

When you use the For Each tdf In db.TableDefs, you're going sequentially
from start to finish. When you delete a given table, the pointer to the
current table move to the next table (since you've deleted the table to
which it pointed). When you hit the Next statement, you move to the next
table. That means you may be missing some.

The approach of going from the end to the beginning is required. However,
the line of code

If Right(dbCurr.TableDefs(intLoop), 13) = "_ImportErrors" Then

should be

If Right(dbCurr.TableDefs(intLoop).Name, 13) = "_ImportErrors" Then

 

[Baz]

Not so, it works fine, try it.

Actually, that code probably won't work as desired, Baz.

When you use the For Each tdf In db.TableDefs, you're going sequentially
from start to finish. When you delete a given table, the pointer to the
current table move to the next table (since you've deleted the table to
which it pointed). When you hit the Next statement, you move to the next
table. That means you may be missing some.

The approach of going from the end to the beginning is required. However,
the line of code

If Right(dbCurr.TableDefs(intLoop), 13) = "_ImportErrors" Then

should be

If Right(dbCurr.TableDefs(intLoop).Name, 13) = "_ImportErrors" Then

 

[ReportSmith]

Douglas,
Thanks a lot - it worked. I think I copied a piece of one of your replies
to get the code you saw. Baz, you're right - after I sent the post, I
realized I didn't put the error message (I couldn't get where the error
occurred). Anyway, the message was something about Incorrect Data Type.

 

[Douglas J. Steele]

Hmm, you're right.

I'm very surprised: I know I've seen code fail when looping through the
TableDefs collection that way.

 

[Stuart McCall]

Hmm, you're right.

I'm very surprised: I know I've seen code fail when looping through the
TableDefs collection that way.

I may be mistaken, but I believe you're probably thinking of:

For Each tdf In CurrentDb.TableDefs

and the CurrentDb function returns a new instance each time round the loop.
But when you persist the db object:

Set db = CurrentDb

you're referring to the same instance each time.

At least I know I've got into the habit of persisting the object before
referring to its collections and I must have had a reason to start doing it
that way. Memory isn't what it used to be these days, but I'm fairly sure
that was it.

 

[Dirk Goldgar]

Hmm, you're right.

I'm very surprised: I know I've seen code fail when looping through the
TableDefs collection that way.

I suspect it's because Baz's code is using DoCmd.DeleteObject rather than
deleting the TableDef directly from the collection using
"dbCurr.TableDefs.Delete tdf.name".

 

[Baz]

I may be mistaken, but I believe you're probably thinking of:

For Each tdf In CurrentDb.TableDefs

and the CurrentDb function returns a new instance each time round the
loop. But when you persist the db object:

Set db = CurrentDb

you're referring to the same instance each time.

At least I know I've got into the habit of persisting the object before
referring to its collections and I must have had a reason to start doing
it that way. Memory isn't what it used to be these days, but I'm fairly
sure that was it.

Good point, that's probably it.