Make Table Query

[Guest]

I have a make table query that I execute throught VBA, it is used to base a
report on and it runs fine, the table is okay and the data spot on.

Next time the report is run, the code searches for that particular table in
the Tabledefs collection to delete it prior to running the Make Table query
to prevent the user receiving an overwrite prompt.

The problem I have is that sometimes the table is deleted, and sometimes it
is not.
The code I use to remove the table is this

For Each Tbl In CurrentDb.TableDefs
If Tbl.Name Like "*Temp" Then
DoCmd.SetWarnings (0)
CurrentDb.TableDefs.Delete Tbl.Name
DoCmd.SetWarnings (-1)
Exit For
End If
Next

Stepping the code through (and printing a debug list of all the table names)
shows that the table created with the Make Table query does not always appear
in the Tabledefs collection which makes me wonder how the Make Table query
actually appends the table to the collection in the first place.

Yet - the table is definately there, you can see it in the database window
and the report that uses it is always showing the correct information.

Using Access 2003 Pro on Windows XP Pro, I am at the point of scrapping the
Make Table query and creating the table in code and then using an append
query to populate it, so if anyone can help - please save me some work.

 

[John Vinson]

For Each Tbl In CurrentDb.TableDefs
If Tbl.Name Like "*Temp" Then
DoCmd.SetWarnings (0)
CurrentDb.TableDefs.Delete Tbl.Name
DoCmd.SetWarnings (-1)
Exit For
End If
Next

You need to step through the tabledefs collection BACKWARDS - when you
delete a table, Access resequences the rest, and you end up skipping
tables in the loop (since the second table gets reset to be the first,
it never gets deleted).

Try

Dim iPos As Integer
Dim tbl As DAO.Tabledef
For iPos = Currentdb.TableDefs.Count - 1 To 0 Step -1
Set tbl = Currentdb.TableDefs(iPos)
If Right(tbl.Name, 4) = "Temp" Then
DoCmd.SetWarnings False
CurrentDb.TableDefs.Delete tbl.Name
End If
Next iPos


John W. Vinson[MVP]

 

[Guest]

Thanks for your reply - works like a charm.