Access Memo fields in ArcGIS 10.3

[deleted-user-IR249IovB3CN]

I'm trying to load an Access 2010 database into Arc Catalog through an OLE DB connection using the OLE DB Provider "Microsoft Office 15.0 Access Database Engine OLE DB Provider". Tow of the tables in the Access database contain memo fields (unlimited character length). The database loads/connects correctly but attempting to preview the two tables with memo fields in Arc Catalog I get an error. In Access converting the Access memo fields to Access text fields that have a limited length of 255  solves the problem. Those two tables preview correctly. However I lose data in those fields that were beyond the limit of 255 characters. Does anyone know how to correctly load memo field types? There is no clear segment where splitting the fields would yield meaningful fields so I'd prefer not to attempt that approach.

Thank you for any help you can provide.

[VinceAngelo]

If the OLE provider marks the source as a "String" rather than a "CLOB", it's possible that ArcGIS will place a string limit on the field length (or that the limit is imposed before ArcGIS sees the field).  OLE dates back to the days when dBase ruled the "database" world, so it's not impossible that the 256 limit is part of the protocol (and that no CLOB exists).

I fear you will likely need to pursue the split/reassemble approach, though as long as your data does not contain multi-byte UTF8 sequences, splitting on meaningful boundaries probably would not be required.

- V

[deleted-user-IR249IovB3CN]

Thank you for the insightful perspective Vince. Unfortunately I am not versed much of what you describe! Any suggestions on how I would determine if the limitation is with the OLE provider? I've come across evidence through other forums/articles that ArcGIS does classify memo as a string in the earlier OLE provider versions such as Micosoft Jet 4.0 OLE DB Provider.

[VinceAngelo]

I'm a "big iron" guy, so I haven't used Access, but a quick set of searches indicates that OLEDB doesn't support LOBs from real databases like Oracle, and maps Memo to wide string (DBTYPE_WSTR)​, so it doesn't appear that Esri has much control over how to access memo fields through that kind of connection.

- V

[deleted-user-IR249IovB3CN]

I exported the table from Access as an excel file and attempted the import to the file geodatabase. This initiated the general failure 9999 error. The table was created in the file geodatabase with the correct field names but the fields were empty. I checked the field properties and all fields that were memo type in Access were BLOB type in ArcGIS. As a test I deleted the memo fields in the excel table and attempted the import again. It was successfully imported with all fields populated. Do you know of a simple way to split the memo field and reassemble once it is in ArcGIS as a work around, or any other suggestions? Thanks for your help!

[TedKowal]

Excel has the same problem of the 255 limit within the "Cells".  The problem is not so much as reading the memo/blob field as outputting to the display containers for visual inspection.  The only work around I found was through programming...

Writing a program to Streaming the blob to and from memory or to a text file.  Very cumbersome because management thence forth will be programmatic.*

I do not believe there is a direct way to read an Access Memo field directly...

* You can use this method to split the memo field into 255 length chunks

Example Memo parsing into multiple fields (Not complete)

' ***********************************************************************
lngLengthOfInputMemoField = Len(recIn!MyMemoField)
lngStartSearchAt = 1


 Do Until lngStartSearchAt > lngLengthOfInputMemoField - 2
  lngFieldBegin = InStr(lngStartSearchAt, recIn!MyMemoField, "|") + 1
  lngStartSearchAt = lngFieldBegin
  lngFieldEnd = InStr(lngStartSearchAt, recIn!MyMemoField, "|") - 1
  lngLengthOfField = lngFieldEnd - lngFieldBegin + 1
  lngStartSearchAt = lngFieldEnd
' ***********************************************************************
' Create the exploded records
' ***********************************************************************
  recOut.AddNew
  recOut!Field1 = recIn!Field1
  recOut!Field2 = recIn!Field2
  recOut!Exploded = Mid(recIn!MyMemoField, lngFieldBegin, lngLengthOfField)
  recOut.Update
 Loop


' ***********************************************************************
' Get the next record
' ***********************************************************************
 recIn.MoveNext
Loop Until recIn.EOF


recIn.Close
recOut.Close
Set recIn = Nothing
Set recOut = Nothing
Set db = Nothing


End Function