querying system tables
~~~
Hi Markus,
~~~
here is a query to show information from MSysRelationships
SELECT m.szReferencedObject AS Table_Parent, m.szObject AS Table_Child,
m.szReferencedColumn AS FieldName_Parent, m.szColumn AS FieldName_Child,
m.grbit AS RelType
FROM MSysRelationships AS m
ORDER BY m.szObject;
~~~
here is more information than you probably want to know about queries
SELECT mObj.Id
, mObj.Name AS Query
, mQry.Attribute AS Attr
, IIf([mQry].[Attribute]=1,[Name1],Null) AS InsertInto_
, IIf([mQry].[Attribute]<>6
AND [mQry].[Attribute]<>1,[Name1]," ") AS From_
, IIf([mQry].[Attribute]=7,[mQry].[Expression],"") AS On_
, IIf([mQry].[Attribute]=6,[Name1]," ") AS Field_Alias
, IIf([mQry].[attribute]=6,[Name2],IIf(([mQry].[attribute]<>7)
AND ([mQry].[attribute]<>8)
AND ([mQry].[attribute]<>11),[mQry].[Expression]," ")) AS Field_
, IIf([mQry].[attribute]=6,[mQry].[Expression],"") AS UpdateTo_
, IIf([mQry].[attribute]<>6,[Name2],Null) AS Reference
, IIf([mQry].[Attribute]=8,[mQry].[Expression],"") AS Where_
, IIf([mQry].[Attribute]=11,[mQry].[Expression],"") AS GroupBy_
, mQry.Flag
FROM MSysObjects AS mObj
INNER JOIN MSysQueries AS mQry
ON mObj.Id = mQry.ObjectId
WHERE (((mQry.Name1) Is Not Null)
AND ((Left([mObj].[Name],1))<>"~"))
OR (((Left([mObj].[Name],1))<>"~")
AND ((mQry.Name2) Is Not Null))
OR (((Left([mObj].[Name],1))<>"~")
AND ((mQry.Expression) Is Not Null))
ORDER BY mObj.Name
, mQry.Attribute;
~~~
here is the SQL to generate a list of object types and objects (you can
make a query and paste this into the SQL view)
SELECT GetObjectType([Type]) AS ObjectType, MSysObjects.Name
FROM MSysObjects
WHERE ( ((Left([Name],1))<>"~") AND ((Left([Name],4))<>"msys"))
ORDER BY GetObjectType([Type]), MSysObjects.Name;
this uses a function to give you the text version of type.
make a general module and paste this in before your run the query or SQL:
'~~~~~~~~~~~
Function GetObjectType(pType) As String
Select Case pType
Case 1: GetObjectType = "Table"
Case 5: GetObjectType = "Query"
Case -32768: GetObjectType = "Form"
Case -32764: GetObjectType = "Report"
Case -32766: GetObjectType = "Macro"
Case -32761: GetObjectType = "Module"
Case Else: GetObjectType = ""
End Select
End Function
~~~
The tables that store import specs are:
MsysIMEXspecs
MsysIMEXcolumns
~~~
well, here is a start for you
Warm Regards,
Crystal
remote programming and training
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
![Smile :) :)](/styles/default/custom/smilies/smile.gif)
have an awesome day
![Smile :) :)](/styles/default/custom/smilies/smile.gif)
*