Max Wanadoo
max.wanadoo at gmail.com
Mon Feb 22 02:26:51 CST 2010
Thanks AD,
I had already sorted the code out. The exporting code and importing code is
below.
The question I had was based on multiple relationships in the one table and
I cannot find any examples to see if my code works or not. EG, in the code
below it loops through the .Fields but I cannot find any examples where
there are more than one field being used to relate between the same two
tables.
Exporting:
For Each Rel In dbs.Relations
With Rel
Me!ExportingRelationships = .Name
sql = sql & .Name & "," & .Attributes & "," & .Table & "," &
.ForeignTable & ","
For Each Fld In .Fields
sql = sql & Fld.Name & ","
Next Fld
End With
sql = sql & vbCrLf
Next Rel
Importing:
pIntFile = FreeFile: sql = ""
Open strSourceFolder & strSourceFileName For Input As
pIntFile
Do While Not EOF(pIntFile)
Line Input #pIntFile, strIn
If Len(Trim(strIn)) > 0 Then
Me!ImportingReferences = strIn
appImport.References.AddFromFile strIn
End If
Loop
Close pIntFile
I don't see any need to import into a Table as you have done. References
are held as a collection by Access itself and .Reference.AddFromFile works
great, unless I am missing something. However I seldom, if ever, use
References so I have nothing to check my code against. The dummy ones I set
up seemed to work just fine.
BTW, RemoteEatBloat is entirely finished with regard to code I just need to
add some more hand-holding to explain why things are causing problems.
As I say, all the coding is done - just the help files remain.
Thanks for the input anyway.
Regards
Max
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D. Tejpal
Sent: 22 February 2010 05:24
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Relationships
Max,
Your existing code seems to be OK. You might like to incorporate the
ForeignName property as well. Sample function Fn_GetRelations(), based upon
your code, is placed below.
For storing the particulars in a table, sample procedure
P_FillRelationsTable() as given below, could be used. This will populate
table T_Relations having following fields:
(a) RelName, (b) RelAttributes
(c) TableName, (d) ForeignTableName
(e) FieldName, (f) ForeignFieldName
In case of composite key involving multiple fields, all such fields get
listed by the function as well as procedure mentioned above.
Best wishes,
A.D. Tejpal
------------
' Code in VBA module
'=================================
Function Fn_GetRelations() As String
Dim fd As DAO.Field
Dim rel As DAO.Relation
Dim Txt As String
Txt = ""
For Each rel In CurrentDb.Relations
With rel
Txt = Txt & "RelName: " & .Name & vbCrLf
Txt = Txt & "RelAttributes: " & _
.Attributes & vbCrLf
Txt = Txt & "Table: " & .Table & vbCrLf
Txt = Txt & "ForeignTable: " & _
.ForeignTable & vbCrLf
Txt = Txt & "Fields:" & vbCrLf
For Each fd In .Fields
Txt = Txt & "Field: " & fd.Name & _
", ForeignField: " & _
fd.ForeignName & vbCrLf
Next
End With
Txt = Txt & vbCrLf
Next
Fn_GetRelations = Txt
Set rel = Nothing
Set fd = Nothing
End Function
'-----------------------------------------------
Sub P_FillRelationsTable()
' Populates table T_Relations with particulars
' of all relations in the db
' This table has fields: RelName, RelAttributes,
' TableName, ForeignTableName,
' FieldName, ForeignFieldName
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fd As DAO.Field
Dim rel As DAO.Relation
Set db = CurrentDb
' Clear table T_Relations
db.Execute "DELETE FROM T_Relations;", _
dbFailOnError
Set rst = db.OpenRecordset("T_Relations")
For Each rel In db.Relations
' Populate table T_Relations with
' uptodate particulars
For Each fd In rel.Fields
With rst
.AddNew
.Fields("RelName") = rel.Name
.Fields("RelAttributes") = rel.Attributes
.Fields("TableName") = rel.Table
.Fields("ForeignTableName") = _
rel.ForeignTable
.Fields("FieldName") = fd.Name
.Fields("ForeignFieldName") = _
fd.ForeignName
.Update
End With
Next
Next
' Attributes value can be a combination of
' one or more of the following:
' dbRelationUnique = 1
' dbRelationDontEnforce = 2
' dbRelationInherited (Linked Table) = 4
' dbRelationUpdateCascade = 256
' dbRelationDeleteCascade = 4096
' dbRelationLeft = 16777216
' dbRelationRight = 33554432
' Examples:
' (a) Linked tables with UpdateCascade type
' relationship:
' Attributes = 4 +256 = 260
' (b) Local tables with dbRelationLeft +
' UpdateCascade type relationship:
' Attributes = 16777216 +256 = 16777472
' (c) Local tables with dbRelationRight +
' UpdateCascade type relationship:
' Attributes = 33554432 +256 = 33554688
rst.Close
Set rst = Nothing
Set rel = Nothing
Set fd = Nothing
Set db = Nothing
End Sub
'========================
----- Original Message -----
From: Max Wanadoo
To: 'Access Developers discussion and problem solving'
Sent: Friday, February 19, 2010 01:07
Subject: Re: [AccessD] Relationships
Hi Gustave,
Did you have a change to look at the relationship pic I sent?
I cannot seem to get the collection to see these other joins.
Any help or insights would be appreciated.
Thanks
Max
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 18 February 2010 14:53
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Relationships
Hi Max
So you have compound indexes (or indices)? If not, only one field belongs
to
the index that is tied to the relation.
/gustav
>>> max.wanadoo at gmail.com 18-02-2010 15:29 >>>
Can anybody help me with the following code. It doesn't seem to want to
give me anything other that the first field join.
Thanks
Max
For Each Rel In CurrentDb.Relations
With Rel
sql = sql & "Name: " & .Name & vbCrLf
sql = sql & "Attributes: " & .Attributes & vbCrLf
sql = sql & "Table: " & .Table & vbCrLf
sql = sql & "ForeignTable: " & .ForeignTable & vbCrLf
sql = sql & "Fields:" & vbCrLf
For Each Fld In .Fields
sql = sql & "Field: " & Fld.Name & vbCrLf
Next Fld
End With
sql = sql & vbCrLf
Next
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com