J
Jenny
ExportXML Method - AdditionalData Parameter
Context:
I need to export a table named CASE with its two related tables CIRCUMSTANCE
and SAFETYDEVICE to an XML file.
The primary key of CASE is composed of 3 fields: Prov + Year + CaseID. These
3 fields are also parts of CIRCUMSTANCE and SAFETYDEVICE tables.
For one CASE we can have many CIRCUMSTANCEs. For one CASE we can have many
SAFETYDEVICEs.
I created the appropriate indexes and relationship to be able to use
ExportXML Method in the module of a form (VBA code - VB6.3).
'Create the XML file
Set adOtherTables = Application.CreateAdditionalData
adOtherTables.Add gstrCIRCUMSTANCE
adOtherTables.Add gstrSAFETYDEVICE
Application.ExportXML acExportTable, gstrCASE, strFileName, , , ,
acUTF8, , , adOtherTables
'
The problem:
All records of CIRCUMSTANCE table and of SAFETYDEVICE table are exported in
each CASE record without respecting the relationship.
For example, if Case1 has 1 Circumstance and 2 SafetyDevices, and Case2 has
2 Circumstances and 4 SafetyDevices, then, in XML output file, Case1 will
included 3 Circumstances and 6 SafetyDevices, and Case2 will included 3
Circumstances and 6 SafetyDevices.
If I export the CASE table by right-clicking on the Table name then go to
More Options and select the 2 related tables, the output XML file is ok,
without repetition!
But the ExportXML Method does not support composite key and the fact that a
field is related to two different other tables.
The solution I found:
1. Add a field named CircumCaseID that contains (Prov + Year + CaseID) to
CASE table and to CIRCUMSTANCE table.
2. Add a field named SafetyCaseID that contains (Prov + Year + CaseID) to
CASE table and to SAFETYDEVICE table.
3. Delete old relationship between these tables.
4. Create a one to many relation between CASE!CircumCaseID and
CIRCUMSTANCE!CircumCaseID.
5. Create a one to many relation between CASE!SafetyCaseID and
SAFETYDEVICE!SafetyCaseID.
Then, with the same code, I got the appropriate output, plus new fields!
Context:
I need to export a table named CASE with its two related tables CIRCUMSTANCE
and SAFETYDEVICE to an XML file.
The primary key of CASE is composed of 3 fields: Prov + Year + CaseID. These
3 fields are also parts of CIRCUMSTANCE and SAFETYDEVICE tables.
For one CASE we can have many CIRCUMSTANCEs. For one CASE we can have many
SAFETYDEVICEs.
I created the appropriate indexes and relationship to be able to use
ExportXML Method in the module of a form (VBA code - VB6.3).
'Create the XML file
Set adOtherTables = Application.CreateAdditionalData
adOtherTables.Add gstrCIRCUMSTANCE
adOtherTables.Add gstrSAFETYDEVICE
Application.ExportXML acExportTable, gstrCASE, strFileName, , , ,
acUTF8, , , adOtherTables
'
The problem:
All records of CIRCUMSTANCE table and of SAFETYDEVICE table are exported in
each CASE record without respecting the relationship.
For example, if Case1 has 1 Circumstance and 2 SafetyDevices, and Case2 has
2 Circumstances and 4 SafetyDevices, then, in XML output file, Case1 will
included 3 Circumstances and 6 SafetyDevices, and Case2 will included 3
Circumstances and 6 SafetyDevices.
If I export the CASE table by right-clicking on the Table name then go to
More Options and select the 2 related tables, the output XML file is ok,
without repetition!
But the ExportXML Method does not support composite key and the fact that a
field is related to two different other tables.
The solution I found:
1. Add a field named CircumCaseID that contains (Prov + Year + CaseID) to
CASE table and to CIRCUMSTANCE table.
2. Add a field named SafetyCaseID that contains (Prov + Year + CaseID) to
CASE table and to SAFETYDEVICE table.
3. Delete old relationship between these tables.
4. Create a one to many relation between CASE!CircumCaseID and
CIRCUMSTANCE!CircumCaseID.
5. Create a one to many relation between CASE!SafetyCaseID and
SAFETYDEVICE!SafetyCaseID.
Then, with the same code, I got the appropriate output, plus new fields!