Dropping unwanted tables from db

[Guest]

I'm trying to drop all tables from my database window except tblTemp. I have
tried the following code:
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData

For Each obj In dbs.AllTables
If obj.Name <> tblTemp Then
DoCmd.RunSQL "Drop Table " & obj.Name
End If
Next obj

But this code returns more than I want. Things like MSysAccessObjects, etc.
How can I loop through only the tables listed in my db window?

 

[Granny Spitz via AccessMonster.com]

How can I loop through only the tables listed in my db window?

Change If obj.Name <> tblTemp Then to:

If ((obj.Name <> "tblTemp") And (Left$(obj.Name, 4) <> "Msys")) Then

 

[Guest]

I assume then, that all of these unseen objects start with MSys.

I'm on a long-term self-taught learning curve and this is a new area for me.
Thanks.

 

[Guest]

What does the "$" after "Left" do?

 

[Guest]

Left$ or just Left is a string function that returns a portion of a string
specified in the first argument beginning with the first character and
returns the number of characters specificed in the second argument.
You can always get detailed info in VBA Help. See Also Right and Mid

 

[Guest]

I am getting a Type Mismatch error with the following code when obj.Name =
"MSysAccessObjects":

Dim obj As AccessObject, dbs As Object

Set dbs = Application.CurrentData

For Each obj In dbs.AllTables
If (obj.Name <> "tblTemp") And (Left$(obj.Name, 4) <> "MSys") Then
DoCmd.RunSQL "Drop Table " & obj.Name
End If
Next obj

I don't understand. It seems to me that obj.Name is a string and Left
returns a string.

 

[Guest]

Try this version, it is really a little better:

Dim tdfs As TableDefs
Dim tdf As TableDef

Set tdfs = CurrentDb.TableDefs
For Each tdf In tdfs
If tdf.Name <> "tbltemp" And Left(tdf.Name, 4) <> "Msys" Then
DoCmd.RunSQL "Drop Table " & tdf.Name
End If
Next tdf
Set tdfs = Nothing
Set tdf = Nothing

 

[Guest]

I get the following compile error: "User-defined type not defined" when I
try your code.

I do not have TableDef in the drop list when I dimension a variable.

If it makes a difference, I am using Access 2000 and I'm coding in a module
behind a form.

 

[Guest]

I am on 2003 in 2000 format on XP; however, I have run code using this method
in 2000 on 2000Pro, so being on 2000 should not make a difference.
Do you have Microsoft DAO 3.6 Object Library in your VBA References?

I have 2000 at home, so I will give it a try tonight, and post back Monday
morning.

 

[Guest]

I found MS DAO 3.6 Object Library in my VBA References but it was not
checked. When I checked it and restarted my application I no longer get the
compile error and the loop appears to be working.

There were a lot of refernces that were not checked. How can I know which
ones I should check. Would it cause problems if I checked them all? Also,
is this why some of my Help Topics are not available? I've installed the
entire Help section from the CD.

I appreciate you help. As I said in an earlier post of this thread, I'm on
a long-term self-taught learning curve.