Deleting created tables

[Guest]

Hi!

I'm using Access 2003. In my database I make tables from sum queries so that
I have a sum query table for every working day of a month. Once a month I
make a backup of my database (backend file), and after making the backup I
would like to delete the sum tables from the original backend. I name the
tables as "Sum_yyyy_mm_dd", so that every table always has the "Sum" in its
name.

My problem is that I don't know how to see if a table's name starts with
"Sum". This is what I have:

Dim i As Integer
Dim strTablename As String
Dim db As Database
Set db = OpenDatabase("here is the path to the backend")

With db.TableDefs
For i = 0 To (.Count - 1)
strTableName = ? 'I'd like to have the name of the table to
strTablename
If strTablename Like "Sum*" Then
.Delete strTablename
End If
Next i
End With

I'd be glad if someone could point me to the right direction!

- Beginner -

 

[Guest]

Hi!

And thanks for the info, but I'm afraid it doesn't work in my program.. I
get an error "Method or data member not found".

I tried:
strTablename = db.TableDefs(i).Name
But it errors me too:
"Item not found in this collection"
It deletes some of the Sum-tables, but not all. If I put it into msgbox like
Msgbox db.tabledefs(i).name
I get the names of my tables and also some other names, so I don't know why
it doesn't find the same tables when I do strTablename =
db.TableDefs(i).Name..

What could be wrong?

- Beginner -

 

[Bill Manville]

strTableName = .Name


Bill Manville
MVP - Microsoft Excel, Oxford, England

 

[Guest]

Hi Beginner,

Try in this way:

Dim i As Integer
Dim strTablename As String
Dim db As Database
Set db = OpenDatabase("here is the path to the backend")

With db.TableDefs
For i = 0 To (.Count - 1)
strTableName = .item(i).name
If strTablename Like "Sum*" Then
.Delete strTablename
End If
Next i
End With

I'm pretty sure it'll work.
Regards Paolo

 

[Marshall Barton]

I'm using Access 2003. In my database I make tables from sum queries so that
I have a sum query table for every working day of a month. Once a month I
make a backup of my database (backend file), and after making the backup I
would like to delete the sum tables from the original backend. I name the
tables as "Sum_yyyy_mm_dd", so that every table always has the "Sum" in its
name.

My problem is that I don't know how to see if a table's name starts with
"Sum". This is what I have:

Dim i As Integer
Dim strTablename As String
Dim db As Database
Set db = OpenDatabase("here is the path to the backend")

With db.TableDefs
For i = 0 To (.Count - 1)
strTableName = ? 'I'd like to have the name of the table to
strTablename
If strTablename Like "Sum*" Then
.Delete strTablename
End If
Next i
End With

You can not delete an member of a collection and move to the
next member because the collection has been rearranged. You
need to loop from the last member of the collection to the
first one.

Dim i As Integer
Dim strTablename As String
Dim db As Database
Set db = OpenDatabase("here is the path to the backend")

With db.TableDefs
For i = .Count - 1 To 0
strTablename = .Item(i).Name
If strTablename Like "Sum*" Then
.Delete strTablename
End If
Next i
End With

I do have to warn you that the whole idea of having a table
for every day is extemely odd. Why can't you just
recalulate the totals as needed?

 

[John W. Vinson]

With db.TableDefs
For i = 0 To (.Count - 1)
strTableName = .item(i).name
If strTablename Like "Sum*" Then
.Delete strTablename
End If
Next i
End With

I'm pretty sure it'll work.

Very close... but it will miss some! When you delete table 1, it will reorder
the tables so the old table 2 becomes 1, 3 becomes 2 and so on; the loop will
go on to delete every other table.

Do the loop backwards to avoid this problem:

For i = (.count - 1) To 0 Step -1


John W. Vinson [MVP]

 

[John W. Vinson]

I'm using Access 2003. In my database I make tables from sum queries so that
I have a sum query table for every working day of a month.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If the sum queries take an inordinately long time to run and must be run many
times you *might* have a good reason to store all these temp tables... but I'd
really suggest using the totals queries directly as the recordsource for your
reports.

John W. Vinson [MVP]

 

[Guest]

Hi everyone! And thanks for the answers!
This is what worked:

Dim i As Integer
Dim strTablename As String
Dim db As Database
Set db = OpenDatabase("path")
With db.TableDefs
For i = (.Count - 1) To 0 Step -1
strTablename = .Item(i).Name
If strTablename Like "Sum*" Then
.Delete strTablename
End If
Next i
End With

- Beginner -

p.s. I need to store sum queries because reporting is made in a different
system partially based on them.

 

[John W. Vinson]

Hi everyone! And thanks for the answers!
This is what worked:

Dim i As Integer
Dim strTablename As String
Dim db As Database
Set db = OpenDatabase("path")
With db.TableDefs
For i = (.Count - 1) To 0 Step -1
strTablename = .Item(i).Name
If strTablename Like "Sum*" Then
.Delete strTablename
End If
Next i
End With

- Beginner -

p.s. I need to store sum queries because reporting is made in a different
system partially based on them.

And the other system cannot link to a stored Query, only to a Table? Ouch.

If so, be sure to Compact your database regularly. You may also want to
consider creating and using a "scratch" .mdb file; rather than creating and
deleting local tables, cluttering up your production database, you can create
a new .mdb file with the CreateDatabase method; put the temp tables into it;
and delete the entire database when you're done with it.

John W. Vinson [MVP]

 

[Guest]

Hi!

Sounds really good, I just coded compact and repair, and I'll definately
start thinking about making a new .mdb.

- Beginner -