This routine allows a hidden table to be appended to the Tables collection in an MDB file. I use the table name as a brand or stamp. During development, I change the brand when design changes are made to the MDB file. Access tables with Msys and Usys preambles are treated as hidden objects by the Jet database engine. The default setting is hidden. The brand starts with UsysBRAND.I prefer using the table name to using a record in a predefined table because it is more difficult for a casual user to delete a table than it is to delete a record from a table:
Public MyDB As DatabasePublic Const BrandString = "UsysBRAND"Dim Dummy As IntegerDim outputVariantSet MyDB = OpenDatabase("testjet3db")'Some test callsDummy = MDBrand("clearbrands")Dummy = MDBrand("stamp", "TheTestProject")If MDBrand("read", outputVariant) Then Debug.Print outputVariantEnd IfIf MDBrand("validate", "TheTestProject") Then Debug.Print "ProjectTest"End IfPublic Function MDBrand(ByVal whichAction As String, _ Optional aString) As IntegerDim MyTableDef As TableDef, _ MyField As FieldDim TempString As StringDim i% On Error GoTo MDBrandError 'Default MDBrand = False Select Case whichAction Case "clearbrands" For i% = MyDB.TableDefs.Count - 1 To 0 Step -1 If Mid$(LTrim$(MyDB._ TableDefs(i%).Name), _ 1, Len(BrandString)) _ = BrandString Then MyDB.TableDefs.Delete _ MyDB.TableDefs(i%).Name DoEvents DBEngine.Idle DoEvents MDBrand = True End If Next i% Case "read" For i% = MyDB.TableDefs._ Count - 1 To 0 Step -1 If Mid$(LTrim$(MyDB._ TableDefs(i%).Name), _ 1, Len(BrandString)) _ = BrandString Then MDBrand = True aString = Mid$(LTrim$(MyDB.TableDefs_ (i%).Name), Len(BrandString) + 1) End If Next i% Case "stamp" TempString = BrandString & aString & CStr(Now) If Len(TempString) > 40 Then TempString = Mid$(TempString, 1, 40) End If Set MyTableDef = MyDB.CreateTableDef_ (TempString) Set MyField = MyTableDef._ CreateField("MyDate", dbDate) MyTableDef.Fields.Append MyField MyDB.TableDefs.Append MyTableDef Set MyField = Nothing Set MyTableDef = Nothing MDBrand = True Case "validate" For i% = MyDB.TableDefs.Count - 1 To 0 Step -1 If Mid$(LTrim$(MyDB.TableDefs(i%)._ Name), 1, Len(BrandString)) = _ BrandString Then If InStr(MyDB.TableDefs(i%)._ Name, aString) Then MDBrand = True End If End If Next i% End SelectMDBrandErrorExit: Exit FunctionMDBrandError: MDBrand = False Resume MDBrandErrorExitEnd Function