P
Peter
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.
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.