Good morning FME'rs
Does anybody know about a clever way to create relationships between tables while writing to an access database?
Page 1 / 1
You can use the SQLExecutor and some SQL to alter the table definition with relationships:
Thanks, Dave: I was looking into that but ran into an error. I was trying to execute the SQL after translation...
See syntax below:
Alter table [MH_Inspections] add constraint FK_MH_Inspecitons_Media Foreign key ([InspectionID]) references [MH_Inspections_Media] ([InspectionID]); Error:
Creating reader for format: Microsoft Access (JDBC)
Trying to find a JAVA plugin for reader named `MSACCESS_JDBC'
Using Java Reader Interface Version 2.0 (Oct 31, 2000) with module MSACCESS_JDBC to read data
MS Access (JDBC) Reader: Preloading JDBC Driver Class 'net.ucanaccess.jdbc.UcanaccessDriver'
MS Access (JDBC) Reader: Creating connection using connection string 'jdbc:ucanaccess://F:\\\\TestDatasets\\Test3.mdb' and properties '{jackcessOpener=COM.safe.fme.jdbc.msaccess.MsAccessCryptCodecOpener, immediatelyReleaseResources=true}'
MS Access (JDBC) Reader: Connected to 'UCanAccess driver for Microsoft Access databases using HSQLDB' version 'V2010 VERSION_14]' using driver 'Ucanaccess' version '4.0.2' built against JDBC version '4.0'
MS Access (JDBC) Reader: Connection class 'net.ucanaccess.jdbc.UcanaccessConnection' is being loaded from '/C:/Program Files (x86)/FME/plugins/ucanaccess-4.0.2.jar'
MS Access (JDBC) Reader: Executing SQL: Alter table MH_Inspections]
add constraint FK_MH_Inspecitons_Media Foreign key ((InspectionID]) references MH_Inspections_Media] ((InspectionID]);
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.2 a UNIQUE constraint does not exist on referenced columns: MH_INSPECTIONS_MEDIA in statement Alter table MH_INSPECTIONS
add constraint FK_MH_Inspecitons_Media Foreign key ( INSPECTIONID ) references MH_INSPECTIONS_MEDIA ( INSPECTIONID )]
Thanks, Dave: I was looking into that but ran into an error. I was trying to execute the SQL after translation...
See syntax below:
Alter table [MH_Inspections] add constraint FK_MH_Inspecitons_Media Foreign key ([InspectionID]) references [MH_Inspections_Media] ([InspectionID]); Error:
Creating reader for format: Microsoft Access (JDBC)
Trying to find a JAVA plugin for reader named `MSACCESS_JDBC'
Using Java Reader Interface Version 2.0 (Oct 31, 2000) with module MSACCESS_JDBC to read data
MS Access (JDBC) Reader: Preloading JDBC Driver Class 'net.ucanaccess.jdbc.UcanaccessDriver'
MS Access (JDBC) Reader: Creating connection using connection string 'jdbc:ucanaccess://F:\\\\TestDatasets\\Test3.mdb' and properties '{jackcessOpener=COM.safe.fme.jdbc.msaccess.MsAccessCryptCodecOpener, immediatelyReleaseResources=true}'
MS Access (JDBC) Reader: Connected to 'UCanAccess driver for Microsoft Access databases using HSQLDB' version 'V2010 VERSION_14]' using driver 'Ucanaccess' version '4.0.2' built against JDBC version '4.0'
MS Access (JDBC) Reader: Connection class 'net.ucanaccess.jdbc.UcanaccessConnection' is being loaded from '/C:/Program Files (x86)/FME/plugins/ucanaccess-4.0.2.jar'
MS Access (JDBC) Reader: Executing SQL: Alter table MH_Inspections]
add constraint FK_MH_Inspecitons_Media Foreign key ((InspectionID]) references MH_Inspections_Media] ((InspectionID]);
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.2 a UNIQUE constraint does not exist on referenced columns: MH_INSPECTIONS_MEDIA in statement Alter table MH_INSPECTIONS
add constraint FK_MH_Inspecitons_Media Foreign key ( INSPECTIONID ) references MH_INSPECTIONS_MEDIA ( INSPECTIONID )]
Thanks for the tip-- I had the relationship done backwards in the syntax. I am testing replacing the access writer with a feature writer and passing it to the SQL Executer transformer.. it seemed to work on the first test-- just a couple of tables. I just don't get it why it would not work with the "SQL to Run after Write" under advanced parameters.