topic Best way to publish data from a Micrososft Access Memo field to ArcGIS in Data Management Questions

Best way to publish data from a Micrososft Access Memo field to ArcGIS

AndrewRudin — Tue, 04 May 2010 18:46:19 GMT

I have a user that is editing an Access database, and that table has a memo field, which apparently can hold up to 64,000 characters. What is best way to convert that access table to exist as a feature class and maintain all the data in the memo field. I am an ArcGIS person and I'm not totally familiar with all the special field types in oracle like nvarchar2, or varchar2, etc. I know they exist, but how do I create a feature class with fields of these types? or does ESRI even support that?

Something else to note, this access database regularly gets new records, so I'd also need a way to load in new records to SDE from Access, so its not just a one time process.

Thank you for any help

-Andrew

Re: Best way to publish data from a Micrososft Access Memo field to ArcGIS

AndrewRudin — Tue, 04 May 2010 18:47:33 GMT

sorry, forgot to mention, our SDE uses Oracle10g.

Re: Best way to publish data from a Micrososft Access Memo field to ArcGIS

VinceAngelo — Tue, 04 May 2010 20:53:32 GMT

You don't say which version of ArcSDE you're using, but SE_CLOB_TYPE and SE_NCLOB_TYPE
columns would be the moral equivalents of a MEMO field (STRING and NSTRING are limited
to 4000 bytes, so they couldn't hold a full 64k memo). CLOBs would be half the storage of
NCLOBs if you have limited non-ASCII data in them , or they could be 2-3 times larger if
you were storing all non-ASCII . Then again, if you know the memos only contain 400
character strings, you can use the VARCHAR types.

Given the Access source, ArcObjects is going to be your easiest tool for extracting the data
and transferring to the business table, but you still have some work to engineer a solution
to maintain the table after edit. The ways to accomplish this are legion, but the best method
is dependent on the frequency of update, number of rows (total and changed), and whether
you want this to be an automated process.

- V