7/6/10

Query to show Access Database Objects

SELECT IIf([ParentID]=-2147483647,'Form',IIf([ParentID]=-2147483646,'Module',IIf([ParentID]=-2147483645,'Report',IIf([ParentID]=251658241 And [Type]=4,'Table Linked',IIf([ParentID]=251658241 And [Type]=5 And [Flags]<>3,'Query',IIf([ParentID]=251658241 And [Type]=1 And [Flags]=0,'Table Local',IIf([ParentID]=251658241 And [Type]=6,'Table Direct',""))))))) AS ObjectType, MSysObjects.Name, MSysObjects.DateCreate, MSysObjects.DateUpdate, MSysObjects.Connect, MSysObjects.Database, InStr([Connect],"WSID") AS Expr1, Len([Connect]) AS Expr2, IIf(Len([Database])>2,"",Left(Right([Connect],Len([Connect])-InStr([Connect],"WSID")-4),InStr(Right([Connect],Len([Connect])-InStr([Connect],"WSID")-5),";"))) AS CreatedFromMachine, IIf(Len([Database])>2,"",Left(Right([Connect],Len([Connect])-InStr([Connect],"DATABASE=")-8),InStr(Right([Connect],Len([Connect])-InStr([Connect],"DATABASE=")-9),";"))) AS SQLDataBase

FROM MSysObjects

WHERE (((IIf([ParentID]=-2147483647,'Form',IIf([ParentID]=-2147483646,'Module',IIf([ParentID]=-2147483645,'Report',IIf([ParentID]=251658241 And [Type]=4,'Table Linked',IIf([ParentID]=251658241 And [Type]=5 And [Flags]<>3,'Query',IIf([ParentID]=251658241 And [Type]=1 And [Flags]=0,'Table Local',IIf([ParentID]=251658241 And [Type]=6,'Table Direct',""))))))))<>"") AND ((InStr([Connect],"WSID"))<>""))
ORDER BY IIf([ParentID]=-2147483647,'Form',IIf([ParentID]=-2147483646,'Module',IIf([ParentID]=-2147483645,'Report',IIf([ParentID]=251658241 And [Type]=4,'Table Linked',IIf([ParentID]=251658241 And [Type]=5 And [Flags]<>3,'Query',IIf([ParentID]=251658241 And [Type]=1 And [Flags]=0,'Table Local',IIf([ParentID]=251658241 And [Type]=6,'Table Direct',""))))))), MSysObjects.Name, MSysObjects.DateCreate;

No comments: