Trying to Delete all tables (with VBA)

[Anthony]

What is the syntax to loop through all the tables in my database and delete
them?

I tried this:

for i = 0 to db.tabledefs.count - 1

If Not LCase(Left(.TableDefs(i).Name, 4)) = "msys" Then

docmd.deleteobject actable, .tabledefs(i).name

end if

next i

--- but because the table count is altered each time a table is deleted, it
whines about error 3265 and then of course there are still tables left over
and I have to run this thing about 3 or 4 more times.

There must be a simpler, easier, less complex, and a lot smoother and easier
way to do this.

thank you for your kind assistance, whoever you are.

 

[Jeff Boyce]

Anthony

You've posted a "how do I" question, but provided no information about
"what". If you'll explain a bit more what you hope to accomplish by
deleting all the tables, the newsgroup readers may be able to offer you an
alternate solution.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Baz]

Maybe this:

Dim tbl As TableDef
For Each tbl In db.TableDefs
If Not LCase(Left(tbl.Name, 4)) = "msys" Then _
DoCmd.DeleteObject acTable, tbl.Name
Next

 

[George Nicholson]

loop backwards

for i = db.tabledefs.count to 1 Step - 1

This way you will evaluate each table in the db once: 15, 14, 13, 12...
Whether table 15 gets deleted or not, the next loop will look at table 14.

This would be true for any collection.

HTH,

 

[George Nicholson]

Correction (for zero based index):

for i = db.TableDefs.Count -1 to 0 Step - 1

 

[Anthony]

thanks

Correction (for zero based index):

for i = db.TableDefs.Count -1 to 0 Step - 1

 

[Robert Morley]

Now that you've got the looping problem solved, it might also be a bit
faster to use:

.TableDefs.Delete .TableDefs(i).Name

....instead of the DeleteObject command. It's probably not really a huge
deal, but I would expect it to be at least slightly faster.



Rob

 

[Stefan Hoffmann]

hi Anthony,

before deleting all tables with a backward loop, delete the relations first.


mfG
--> stefan <--

 

[Jamie Collins]

before deleting all tables with a backward loop, delete the relations first.

I was taught tables and relations are the same thing (the wacky world
of DAO, eh <g>?)

Jamie.

--

 

[Tony Toews]

I was taught tables and relations are the same thing (the wacky world
of DAO, eh <g>?)

Umm, no, not in Access.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tim Ferguson]

What is the syntax to loop through all the tables in my database and
delete them?

I'm afraid all of these will fail as soon as you meet your first system
table, which you won't have permission to delete.

What about the old-fashioned DOS method?

c:\data > DEL MyData.mdb

c:\data >


HTH


Tim F

 

[George Nicholson]

The OP's original code skipped deletion of tables whose names started with
"Msys".

 

[Jamie Collins]

Umm, no, not in Access.

Actually, I think 'Relations' it is a DAO misnomer rather than it being
the case that the concept of relations is absent from the Access
product i.e. Access is built on Jet, Jet is a SQL product, and the SQL
language is built on relational principles (to some degree).

Jamie.

--

 

[Baz]

Quite right. Even Access VBA help says:

"You can use the Relation object to create new relationships and examine
existing relationships in your database."

i.e. Relation is just a stupid name mistakenly used for a DAO object which
actually represents a *relationship*, which is a quite different thing to a
*relation*.

 

[Tony Toews]

Actually, I think 'Relations' it is a DAO misnomer rather than it being
the case that the concept of relations is absent from the Access
product i.e. Access is built on Jet, Jet is a SQL product, and the SQL
language is built on relational principles (to some degree).

I have no idea what you are trying to say here.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Jamie Collins]

I have no idea what you are trying to say here.

I was assuming that as an Access MVP you would have at least heard of
the main features of relational theory. After all, you did contribute
to the thread, "Is MS Access a relational database system?":

http://groups.google.com/group/comp.databases.ms-access/msg/54decb6f78fcbcf7

And I note you're not beyond flippant comments yourself <g>.

OK, assuming no prior knowledge, consider this quote:

http://en.wikipedia.org/wiki/Relational_database

A relation is defined as a set of tuples that all have the same
attributes. This is usually represented by a table, which is data
organized in rows and columns.

Knowing a relation is analogous to a table, don't you think it a little
odd that the DAO object model has a collection named 'Relations' that
does not comprise tables?

Jamie.

--

 

[Tim Ferguson]

The OP's original code skipped deletion of tables whose names started
with "Msys".

Oops: must remember to read more carefully!

Still reckon the easiest answer is to kill the mdb though... <g>

B Wishes


Tim F

 

[Tony Toews]

Knowing a relation is analogous to a table, don't you think it a little
odd that the DAO object model has a collection named 'Relations' that
does not comprise tables?

Ah, a semantic question. <shrug> No idea.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[James A. Fortune]

Ah, a semantic question. <shrug> No idea.

Tony

I also think the Relations collection is a case of poor naming by
Microsoft. In mathematics, a relation has a precise definition that is
similar to a function, but a function (also called a mapping, but I
consider them a subset of mappings) is only allowed one range value for
each domain value. Relations are more general because they don't have
that restriction and are usually represented by pairs (for the 2D case)
of values in a way that is equivalent to a lookup table. In Access,
relationships are based on the idea of subsets through the use of
foreign keys to identify the subset members. That makes the terminology
even more confusing because both functions ("constrained" mappings) and
relationships ("unconstrained" mappings) can be one-to-one, onto,
injective, etc., basically descriptions of set " relationships ."
I consider the relationships in Access to be quite limited compared to
all the set relationships available in mathematics. For instance, you
can only discover if a mapping is onto by running an unmatched query and
getting no results. Note that an unmatched query is run on tables
containing similar information. The many-to-many Access relationship is
shoehorned into the subset idea by saying that combinations from both
tables are subsets of the cartesian product table (all the join table
possibilities) which allows for general mappings (mathematical
relations). Plus, a table with a key in other tables doesn't contain
any of its members! I'd better give an example of what I mean:

tblEmployees
EID AutoNumber
FirstName Text
LastName Text

tblBenefits
BID AutoNumber
EID Long (FK)
BenefitName Text

tblBenefits contains all the employee benefits given to all the
employees in tblEmployees, but tblEmployees doesn't contain a single
benefit. In set theory, a subset is a set of elements from another set.
O.K., to get slightly more technical: If x is a member of set A
implies that x is a member of set B then A is a subset of B. In Access,
the key in the main table is a pointer to a partition of the set of all
employee benefits. So, unlike the set mappings in mathematics about
which terms like "onto" are used, the set mappings in Access aren't
really between tblEmployees and tblBenefits as the relationship diagrams
seem to suggest, and aren't really set mappings in the mathematical
sense either. They are partitions of a table within itself using the
foreign key to identify which partition members belong together. So the
set relation terminology used by Microsoft is being applied to
"subdivisions" . Therefore, the set relationship terminology of
mathematics does not correlate to the SQL-speak relationships in spite
of the fact that similar terms are often used for both.

I'm just starting to put all these ideas together for the first time so
I may have made some mistakes but my intuition tells me that
mathematical set relationships are different than the relationships used
in SQL. If they are somehow analogous to each other in a way that I
don't see yet, I would appreciate some enlightenment.

James A. Fortune
(e-mail address removed)

 

[Dirk Goldgar]

Still reckon the easiest answer is to kill the mdb though... <g>

Maybe it contains forms, reports, or code modules that Anthony wants to
preserve.