reading database information

I want to read with a .net application information on an access database.
So far I have read out the tables including their columns by using

GetOleDbSchemaTable. this works fine

I wanted to read out existing relation by reading the table msysrelationships.
Unfortunately this table does not only hold existing relations but also
lookup information. there is no way to differentiate both records from each
other.

Can anybody point me to information where access systemtables are documented
Or Can anybody tell me which is the best way of acquiring
lookup and relation infomation on an access 2003 and upwards database.

thanks
markus

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 a

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

*
(: have an awesome day
*




Markus Ohlenroth wrote:
I want to read with a .net application information on an access database.
So far I have read out the tables including their columns by using

GetOleDbSchemaTable. this works fine

I wanted to read out existing relation by reading the table msysrelationships.
Unfortunately this table does not only hold existing relations but also
lookup information. there is no way to differentiate both records from each
other.

Can anybody point me to information where access systemtables are documented
Or Can anybody tell me which is the best way of acquiring
lookup and relation infomation on an access 2003 and upwards database.

thanks
markus

On Thu, 15 Jan 2009 03:54:01 -0800, Markus Ohlenroth
wrote:

I wanted to read out existing relation by reading the table msysrelationships.
Unfortunately this table does not only hold existing relations but also
lookup information. there is no way to differentiate both records from each
other.

That's because they're not really different. A Lookup Field is just one way to
create a relationship; once it's created, it's just a relationship like one
created in the relationships window or in code.
--

John W. Vinson [MVP]

Markus,

adding on to what John said...
.... and if you don't use Lookup fields in your table design, you won't
get these relationships

The Evils of Lookup Fields in Tables
http://www.mvps.org/access/lookupfields.htm


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day
*




John W. Vinson wrote:
On Thu, 15 Jan 2009 03:54:01 -0800, Markus Ohlenroth
wrote:

I wanted to read out existing relation by reading the table msysrelationships.
Unfortunately this table does not only hold existing relations but also
lookup information. there is no way to differentiate both records from each
other.

That's because they're not really different. A Lookup Field is just one way to
create a relationship; once it's created, it's just a relationship like one
created in the relationships window or in code.

John and Crystal
thank you very much for your help.

That's because they're not really different. A Lookup Field is just one way to
create a relationship; once it's created, it's just a relationship like one
created in the relationships window or in code.

Let me do the following.

Create a lookupfield and you get a another relationsship in you
relationdesigner
Then delete the relation in you designer.

You no longer have the possibility to expand on the "+" button.

But the lookupfunction for the field you defined it - "offering a dropdown
combo" with the values of the lookuptable - this function still remains.

To me it seems, that once you define a lookup Access stores 2 different
informations : one in mmsysrelationship and the other in ???

Now where does access store this lookupinformation?

Do you have any ideas?

Pardon me for jumping into this thred, seem there are some experts here.
Anyone know where I can find a definition of the numbers in the RelType
field?

TIA,
Ragnar

analyzing Relationships
~~~


Hi Ragnar,

Relationship Type in MSysRelationships specified by grbit:

grbit corresponds to the Attributes of the relationship if you enumerate
the Relations container of the database

Sub EnumerateRelations()
Dim rtl As Relation
For Each rtl In CurrentDb.Relations
Debug.Print "Table : " & rtl.Table
Debug.Print "ForeignTable: " & rtl.ForeignTable
Debug.Print "Name :" & rtl.Name
Debug.Print "Attributes : " & rtl.Attributes
Debug.Print
Next rtl
End Sub

-- for example, here is some code from an analyzer that I wrote:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'~~~ this is part of a longer procedure ~~~
'-------------------------------------------------- RELATIONSHIPS
' crystal strive4peace2008 at yahoo.com
' Rel is the tablename I am writing to

Set r = dbCur.OpenRecordset("Rel", dbOpenDynaset)
For Each rtl In dbLink.Relations
i = 0
For Each Fld In rtl.Fields
i = i + 1
r.AddNew
r!RunID = mRunID
r!T = rtl.Table
r!fT = rtl.ForeignTable
r!RelName = rtl.Name
r!Attrib = rtl.Attributes
r!KeyNum = i
r!Key = Fld.Name
r!fKey = Fld.ForeignName
r!DateCreated = Now
UpdateRelAttribs r, rtl.Attributes
r.Update

Next Fld
Next rtl
r.Close

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub UpdateRelAttribs(r As dao.Recordset, ByVal pAttrib As Long)

' crystal strive4peace2008 at yahoo.com

Dim i As Integer _
, s As String

Dim aValue(1 To 7) As Long _
, aFldName(1 To 7) As String

aValue(1) = 1 'dbRelationUnique
aFldName(1) = "Uniq"

aValue(2) = 2 'dbRelationDontEnforce
aFldName(2) = "NoRI"

aValue(3) = 4 'dbRelationInherited
aFldName(3) = "Inher"

aValue(4) = 256 'dbRelationUpdateCascade
aFldName(4) = "CasUpdt"

aValue(5) = 4096 'dbRelationDeleteCascade
aFldName(5) = "CasDel"

aValue(6) = 16777216 'dbRelationLeft
aFldName(6) = "IsLeft"

aValue(7) = 33554432 'dbRelationRight
aFldName(7) = "Isright"

'write records

If pAttrib 0 Then
For i = 7 To 1 Step -1
If pAttrib 0 Then
r!AttribErr = True
End If
If pAttrib = 0 Then GoTo AttribDone
If pAttrib = aValue(i) Then
r(aFldName(i)) = True
pAttrib = pAttrib - aValue(i)
End If
Next i
AttribDone:
End If

End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub UpdateFldAttribs(r As dao.Recordset, ByVal pAttrib As Long, ByVal
pDatType As Long)

' crystal strive4peace2008 at yahoo.com

'set up Error Handler
On Error GoTo Proc_Err

Dim i As Integer _
, s As String

Dim aValue(1 To 6) As Long _
, aFldName(1 To 6) As String

aValue(1) = dbFixedField
aFldName(1) = "Fixd"

aValue(2) = dbVariableField ' -- text only
aFldName(2) = "Var"

aValue(3) = dbAutoIncrField
aFldName(3) = "Auto"

aValue(4) = dbUpdatableField
aFldName(4) = "Updt"

aValue(5) = dbSystemField
aFldName(5) = "Sys"

aValue(6) = dbHyperlinkField ' -- memo only
aFldName(6) = "Hyper"

'write records

If pAttrib 0 Then
For i = 6 To 1 Step -1
If pAttrib 0 Then
r!AttribErr = True
End If
If pAttrib = 0 Then GoTo AttribDone
If pAttrib = aValue(i) Then
r(aFldName(i)) = True
pAttrib = pAttrib - aValue(i)
End If
Next i
' r.Update
AttribDone:
End If

Proc_Exit:
On Error Resume Next
'close and release object variables
Exit Sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " UpdateFldAttribs"

Resume Proc_Exit
Resume

End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day
*




Ragnar Midtskogen wrote:
Pardon me for jumping into this thred, seem there are some experts here.
Anyone know where I can find a definition of the numbers in the RelType
field?

TIA,
Ragnar

ps
in order to compile and run the code listed in the previous message,
you will need a reference to a Microsoft DAO Object Library


strive4peace wrote:
analyzing Relationships
~~~


Hi Ragnar,

Relationship Type in MSysRelationships specified by grbit:

grbit corresponds to the Attributes of the relationship if you enumerate
the Relations container of the database

Sub EnumerateRelations()
Dim rtl As Relation
For Each rtl In CurrentDb.Relations
Debug.Print "Table : " & rtl.Table
Debug.Print "ForeignTable: " & rtl.ForeignTable
Debug.Print "Name :" & rtl.Name
Debug.Print "Attributes : " & rtl.Attributes
Debug.Print
Next rtl
End Sub

-- for example, here is some code from an analyzer that I wrote:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'~~~ this is part of a longer procedure ~~~
'-------------------------------------------------- RELATIONSHIPS
' crystal strive4peace2008 at yahoo.com
' Rel is the tablename I am writing to

Set r = dbCur.OpenRecordset("Rel", dbOpenDynaset)
For Each rtl In dbLink.Relations
i = 0
For Each Fld In rtl.Fields
i = i + 1
r.AddNew
r!RunID = mRunID
r!T = rtl.Table
r!fT = rtl.ForeignTable
r!RelName = rtl.Name
r!Attrib = rtl.Attributes
r!KeyNum = i
r!Key = Fld.Name
r!fKey = Fld.ForeignName
r!DateCreated = Now
UpdateRelAttribs r, rtl.Attributes
r.Update

Next Fld
Next rtl
r.Close

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub UpdateRelAttribs(r As dao.Recordset, ByVal pAttrib As Long)

' crystal strive4peace2008 at yahoo.com

Dim i As Integer _
, s As String

Dim aValue(1 To 7) As Long _
, aFldName(1 To 7) As String

aValue(1) = 1 'dbRelationUnique
aFldName(1) = "Uniq"

aValue(2) = 2 'dbRelationDontEnforce
aFldName(2) = "NoRI"

aValue(3) = 4 'dbRelationInherited
aFldName(3) = "Inher"

aValue(4) = 256 'dbRelationUpdateCascade
aFldName(4) = "CasUpdt"

aValue(5) = 4096 'dbRelationDeleteCascade
aFldName(5) = "CasDel"

aValue(6) = 16777216 'dbRelationLeft
aFldName(6) = "IsLeft"

aValue(7) = 33554432 'dbRelationRight
aFldName(7) = "Isright"

'write records

If pAttrib 0 Then
For i = 7 To 1 Step -1
If pAttrib 0 Then
r!AttribErr = True
End If
If pAttrib = 0 Then GoTo AttribDone
If pAttrib = aValue(i) Then
r(aFldName(i)) = True
pAttrib = pAttrib - aValue(i)
End If
Next i
AttribDone:
End If

End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub UpdateFldAttribs(r As dao.Recordset, ByVal pAttrib As Long, ByVal
pDatType As Long)

' crystal strive4peace2008 at yahoo.com

'set up Error Handler
On Error GoTo Proc_Err

Dim i As Integer _
, s As String

Dim aValue(1 To 6) As Long _
, aFldName(1 To 6) As String

aValue(1) = dbFixedField
aFldName(1) = "Fixd"

aValue(2) = dbVariableField ' -- text only
aFldName(2) = "Var"

aValue(3) = dbAutoIncrField
aFldName(3) = "Auto"

aValue(4) = dbUpdatableField
aFldName(4) = "Updt"

aValue(5) = dbSystemField
aFldName(5) = "Sys"

aValue(6) = dbHyperlinkField ' -- memo only
aFldName(6) = "Hyper"

'write records

If pAttrib 0 Then
For i = 6 To 1 Step -1
If pAttrib 0 Then
r!AttribErr = True
End If
If pAttrib = 0 Then GoTo AttribDone
If pAttrib = aValue(i) Then
r(aFldName(i)) = True
pAttrib = pAttrib - aValue(i)
End If
Next i
' r.Update
AttribDone:
End If

Proc_Exit:
On Error Resume Next
'close and release object variables
Exit Sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " UpdateFldAttribs"

Resume Proc_Exit
Resume

End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day
*




Ragnar Midtskogen wrote:
Pardon me for jumping into this thred, seem there are some experts here.
Anyone know where I can find a definition of the numbers in the RelType
field?

TIA,
Ragnar

Thank you Crystal,

I will try out your code as soon as I get a chance.
I need to merge two back-end DBs that have extensive relationships
established, one to many, many to one as well as many to many. To do the
merge I have to remove many of the reationships, so I need to be able to
reestablish them exactly as they are.

Ragnar

you're welcome, Ragnar happy to help

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day
*




Ragnar Midtskogen wrote:
Thank you Crystal,

I will try out your code as soon as I get a chance.
I need to merge two back-end DBs that have extensive relationships
established, one to many, many to one as well as many to many. To do the
merge I have to remove many of the reationships, so I need to be able to
reestablish them exactly as they are.

Ragnar