reading database information

M

Markus Ohlenroth

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
 
S

strive4peace

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

*
:) have an awesome day :)
*
 
J

John W. Vinson

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.
Click to expand...

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.
 
M

Markus Ohlenroth

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.
Click to expand...

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?
 
R

Ragnar Midtskogen

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
 
S

strive4peace

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 :)
*
 
S

strive4peace

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
 
R

Ragnar Midtskogen

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"Free" ip address in asset database, not remove? 0
"free" ip number when I remove device from database, not delete? 1
adding course-information to a business database 1
Reading Word Forms with Access 0
Database Design help - Newer user 6
normalizing and creating fields or related tables 0
Creating Relationships between tables 8
Lookup column assignment 1

Top