Good morning FME'rs
Does anybody know about a clever way to create relationships between tables while writing to an access database?
Solved
create relationships between tables in an access writer
+8- Contributor
Best answer by david_r
You can use the SQLExecutor and some SQL to alter the table definition with relationships:
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
- Best Answer
You can use the SQLExecutor and some SQL to alter the table definition with relationships:
+8- Contributor
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 )]
+8- Contributor
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.