getting table names of MY TABLES only!

[marco02]

Hi guys, is there a smarter way to retrieve the names of MY tables in my dtb with SKIPPING all those hidden and MSsystem tables ?

This usually works but it's dumb, you will agree ...

Set Dtb = CurrentDb
For Each Tdf In Dtb.TableDefs
If Left(Tdf.Name, 4) <> &quot;MSys&quot; And _
Left(Tdf.Name, 4) <> &quot;~TMP&quot; Then

MsgBox Tdf.Name

End If
Next Tdf

thanks

 

[KyleS]

As far as the code goes I'm not really sure. I know for a fact you can access the dB Schema through ADO but using the Tabledefs isn't a bad way to go. I would definitely change &quot;MsgBox tdf.Name&quot; to:

Debug.Print Tdf.Name so it will run and list them for you.

As far as the schema through aDO goes, here is a copy from MS Help slightly modified for your purposes:

Public Sub OpenSchemaX()

Dim cnn1 As ADODB.Connection
Dim rstSchema As ADODB.Recordset
Dim strCnn As String

Set cnn1 = CurrentProject.Connection

Set rstSchema = cnn1.OpenSchema(adSchemaTables)

Do Until rstSchema.EOF
Debug.Print &quot;Table name: &quot; & _
rstSchema!TABLE_NAME & vbCr & _
&quot;Table type: &quot; & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext
Loop
rstSchema.Close

cnn1.Close

End Sub
Kyle