Unanswered: Linking to (oracle) SDO_GEOMETRY data type
Could someone give me some assistance/guideance with a table linking issue i have encountered.
I have an Access frontend which is linked to an Oracle backend via ODBC.
After making changes to the frontend i re-linked the application back to the live server using the Linked Table Manager.
While testing the application i noticed that one of the drop down combo boxes on the access form would not allow me to change to a different item in the list.
After a thorough investigation i noticed that the table linked to the combo box had lost its primary keys during the table linking process.
One of the guys here in the office then suggested that this was a common bug and that all i would need to do is run the Link Table Manager again to resolve the problem. Unfortunately in my case this didnt work.
I therefore attempted to remove the linked table and re-import it hoping that all of the relationships/keys etc would have been rebuilt.
While attempting to import the linked table via ODBC ("File/Get External Data" menu) i received the following message "Invalid field definition 'GEOM' in definition of index or relationsip".
I then proceeded to check the table structure using Oracle Enterprise manager to discover that one of the fields is defined as follows:
Obviously the application does not recognise the SDO_GEOMETRY datatype and therefore it will not allow me to import the table again. So this (my only option so far) is not really an option.
I reverted back to a previous copy of the mdb file & linked it back to the test server and applied the development changes again, then relinked the table back to the live server to finish testing. Both times the linking went as expected and no keys where dropped, but this appeared to be a little hit and miss. ie worked on one example and then failed on the next scenario.
The problem i have is that the primary key being dropped occurs sporadically and once the Access mdb file fails the association it will not re-enable the links even if i continually try to link to the test or the live servers. This so far has meant that i have had re-apply all the development changes not knowing if the linking facility will work correctly or not.
I have checked both live and test servers and they are configured identically, same structures tables etc. The only difference is the data content.
What makes this even more puzzling to me is that there are a few other tables in the application that contain fields with the same datatype, and they do not appear to dropping keys or associations.
I am therefore puzzled as to whether this is actually an Oracle datatype issue or a bug with the Linked Table Manager. So far i am edging towards a bug in the table manager partly due to the fact that the other tables are not loosing their primary keys.
If someone could give me some advice or guidance on what to do the next time this occurs it would be greatly appreciated.