So far our meta-data file contains some very basic information about the databases type, name, properties, and linked tables The next item on my agenda was relationships. Now I was of two minds about this, and I still am. But here is how I went about implementing it anyway.
My first thought, and the way that I actually ended up implementing, was to simply add the relationship data to the Project.xml file. My second thought was that relationships really are complex enough to stand on their own, putting each one in it’s own file, much as I do forms, reports or tables. The last idea I had was a separate file that contained all of the relationships.
The last option I dismissed immediately. I had no need of a special file just for relationships. It didn’t seem to serve any purpose. So the real question is: Are relationships objects unto themselves, or just additional data about the tables? Well, the indices and keys for tables are considered meta data that is stored within the XSD for the table. And that decided me. This was just meta data about the tables. I couldn’t add the information to the table schema files as the XSD format doesn’t supply any means of doing so*. So I decided to store the relationship data in the Project.xml file.
Doing this was a piece of cake once I had decided to. After a little bit of playing around to find the best output I could come up with, I ended up with the following code:
Private Sub ExportRelationships(ByVal Relations, ByVal OutputFilePath) Dim XmlDocument, RelationshipsElement Set XmlDocument = CreateObject("Msxml2.DomDocument.6.0") XmlDocument.Load OutputFilePath Set RelationshipsElement = XmlDocument.selectSingleNode("database/relationships") If RelationshipsElement Is Nothing Then Set RelationshipsElement = XmlDocument.createElement("relationships") XmlDocument.documentElement.appendChild RelationshipsElement End If Dim Relation For Each Relation In Relations Dim RelationElement: Set RelationElement = XmlDocument.createElement("relation") RelationshipsElement.appendChild RelationElement RelationElement.setAttribute "name", Relation.Name RelationElement.setAttribute "table", Relation.Table RelationElement.setAttribute "foreign-table", Relation.ForeignTable RelationElement.setAttribute "attributes", Relation.Attributes If Relation.Fields.Count > 0 Then Dim FieldsElement: Set FieldsElement = XmlDocument.createElement("fields") RelationElement.appendChild FieldsElement Dim Field, FieldElement For Each Field In Relation.Fields Set FieldElement = XmlDocument.createElement("field") FieldsElement.appendChild FieldElement FieldElement.setAttribute "name", Field.Name FieldElement.setAttribute "foreign-name", Field.ForeignName Next End If Next WriteXmlDocument XmlDocument, OutputFilePath Set XmlDocument = Nothing End Sub
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <database name="Example.mdb" type="2"> <properties> <property name="AllowByPassKey" value="-1" /> <property name="AppVersion" value="1.0" /> </properties> <linked-tables> <link name="Logs" source="Logs" database=".\Archives.mdb" /> <link name="Cache" source="Master" database="C:\Temp\Cache.mdb" /> <link name="Archive" source="Master" database=".\Archives.mdb" /> <link name="Master" source="Master" database=".\Data.mdb" /> </linked-tables> <relationships> <relation name="MasterCache" table="Master" foreign-table="Cache" attributes="1024"> <fields> <field name="id" foreign-name="id" /> </fields> </relation> <relation name="MasterDetail" table="Master" foreign-table="Detail" attributes="42"> <fields> <field name="id" foreign-name="master_id" /> </fields> </relation> </relationships> </database>
*At least as far as I know. If I'm wrong here, feel free to ping me. I'd be more than happy to modify the code. :-)