Setting a Table to Hidden via Code 1

[GGleason]

Does anyone know how to make a table hidden using VBA?

tia,
GGleason

 

[VivP]

Yeah, I think this will do it:

Public Sub SetHiddenTableAttribute(tblName As String)
Dim tbl As TableDef
Const cSYSTEM_OBJ = &H80000000
Const cSYSTEM_OBJ_ALT = 2

Set tbl = CurrentDb.TableDefs(tblName)
If ((tbl.Attributes And cSYSTEM_OBJ) <> cSYSTEM_OBJ) And _
((tbl.Attributes And cSYSTEM_OBJ_ALT) <> cSYSTEM_OBJ_ALT) Then
If ((tbl.Attributes And dbAttachedTable) = dbAttachedTable) Then
tbl.Attributes = (tbl.Attributes And &HFFFFFFF) Or dbHiddenObject
Else
tbl.Attributes = tbl.Attributes Or dbHiddenObject
End If
End If
Set tbl = Nothing
End Sub

Use this to find them again:

Public Sub UnhideTables()
Dim tbl As TableDef
Dim db As Database
Const cSYSTEM_OBJ = &H80000000
Const cSYSTEM_OBJ_USR = 2

Set db = CurrentDb()

For Each tbl In db.TableDefs
If ((tbl.Attributes And cSYSTEM_OBJ) <> cSYSTEM_OBJ) And _
((tbl.Attributes And cSYSTEM_OBJ_USR) <> cSYSTEM_OBJ_USR) Then
If ((tbl.Attributes And dbAttachedTable) = dbAttachedTable) Then
tbl.Attributes = (tbl.Attributes And &HFFFFFFF) And Not dbHiddenObject
Else
tbl.Attributes = tbl.Attributes And Not dbHiddenObject
End If
Debug.Print tbl.name & &quot; &quot; & tbl.Attributes
End If
Next tbl
End Sub

Viv

Courtesy of Bob Back Viv Paton
Access Developer

 

[GGleason]

Viv,

Thanks for the tip! I did have to make a minor modification to make it work for me. Here’s what I did (changes in bold text):
[tt]
Public Sub SetHiddenTableAttribute(tblName As String)
Dim tbl As TableDef, dbs As Database
Const cSYSTEM_OBJ = &H80000000
Const cSYSTEM_OBJ_ALT = 2

Set dbs = OpenDatabase(CurrentDb.Name)
Set tbl = dbs.TableDefs(tblName)
If ((tbl.Attributes And cSYSTEM_OBJ) <> cSYSTEM_OBJ) And _
((tbl.Attributes And cSYSTEM_OBJ_ALT) <> cSYSTEM_OBJ_ALT) Then
If ((tbl.Attributes And dbAttachedTable) = dbAttachedTable) Then
tbl.Attributes = (tbl.Attributes And &HFFFFFFF) Or dbHiddenObject
Else
tbl.Attributes = tbl.Attributes Or dbHiddenObject
End If
End If
Set tbl = Nothing
dbs.Close
Set dbs = Nothing
End Sub
[/tt]
Thanks again,
GGleason

 

[VivP]

Arse. Had actually fixed that, but forgot to paste the right bit. Hey ho.

Have fun.

Viv Viv Paton
Access Developer