Hiding Built-in Access tables

[Harry-Wishes]

I am trying to programmatically make a copy of each table I have created in
Access. However, I know there are built-in tables (hidden tables) that access
needs. If you run the test script below, you will see that those tables will
appear in the collection of tables (both built-in and the ones the user
actually created). I think you see my problem. I obviously want to make a
copy of the tables I created, not the ones that are built-in with Access. Is
there a way to restrict Access to copying user-defined tables? Perhaps there
is a count property that counts just user-defined tables in the AllTables
collection object. If so, I can't find it. I have listed the built-in
tables below. All I need is a way of filtering. I don't need the script for
copying. I know how to do that.

Thanks
HW

Private Sub cmdTables_Click()
Dim obj As AccessObject, dbs As Object

Set dbs = Application.CurrentData

' Check each object of the AllTables collection
For Each obj In dbs.AllTables
' When you find a table, display its name
MsgBox obj.Name
Next obj

End Sub

MSysAccessObjects
MSysAccessXML
MSysACEs
MSysObjects
MSysQueries
MSysRelationships

 

[kc-mass]

Hi Harry

Try something like this:

For Each obj In dbs.AllTables
'Avoid system tables
If Left(obj.name, 1) = "~" Or _
Left(obj.name, 4) = "MSYS" Then
Else
' When you find a table, display its name
MsgBox obj.Name
End IF
Next obj

Regards

Kevin

 

[Harry-Wishes]

Hi Kevin

I had not thought of the Left function. Unfortunately, I can not get it to
run in Access as I get a Compile error (Can't find project or library) which
is odd. It works automatically in Excel and Word without having to add the
reference to the function. I am not sure what that reference would be. There
are at least 50 of them listed in the reference library of VBA.

Harry Wishes

 

[John Spencer MVP]

Usually, I would use a test like:

If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then

Perhaps you can adapt that in your current code?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[kc-mass]

I believe the minimum references needed are Visual Basic for Applications
and Microsoft Access 11.0 Object Library. The number of the library
varies for version of Access. If you're using ADO you will need that also.

Regards

Kevin