Front-end to SDE Geodatabases.

Question

Dear All,

Is there any way to use forms (MS Access) as UI to Read/Write the data within Enterprise SDE Geodatabase feature classes ?
If Not, is there any other interface(Customizable or not) which could make the data edition procedures more interactive at an Enterprise level?

Thank you in advance !

Ref.  MS SQL Server 2008 R2.
                    ArcGIS (10.1)
                    http://forums.esri.com/Thread.asp?c=2&f=1719&t=126502 (2004-2008) Very old...

RosieYacoub · Answer

I am experimenting with editing JUST THE ATTRIBUTES  of a feature class in SDE. I created a view in SQL Management Studio that had only the attribute fields (no shape, no objectid).  Then I linked to that in MS Access using ODBC, and edited a few existing records through a form.  The form is limited to edits (no additions or deletions available), and I have hidden the table from view.  Hoping this might provide a better method for data entry than editing rows in ArcMap.

ThomasColson · Answer

You could, but you'll spend more time messing with SQL than with GIS, and be recognized by your superior as a "DB Admin". Access CANNOT edit data in a table with the SQL Geometry field, and you'd need some pretty complex triggers on indexed views in order to make them editable. At this point, why are you using GIS?

We've accomplished some of this using VS Lightswitch and insert/update triggers, but took years to get to this point. There is no simple way to edit SDE tables outside of ArcGIS without extensive customization and a rock-solid back up plan. And...once you do so....don't bother calling ESRI tech support about ANY problem with your SDE. They catch you doing that, and you're on their "Do not return call list". "Are you sure this isn't related to the time you tried editing that table with Access 7 years ago?" they'll say....

WilliamCraft · Answer

While technically possible to perform inserts, updates, and deletes against an enterprise geodatabase through Microsoft Access, I would not recommend it in most cases.  To set this up, you need to go to the External Data tab in MS Access, clicked the ODBC Database button in the ribbon, and choose the option for "Link to the data source by creating a linked table" option.  This requires that you set up a DSN via the Microsoft ODBC Administrator in Windows (typically found by typing odbcad32.exe from the Start menu).  I just gave this a try with a SQL Server 2008 R2 and an Oracle 11g geodatabase using Access 2010, specifically by updating a few attribute values.

There are multiple problems with doing this.  The first problem occurs when and if your feature class is versioned within the geodatabase.  Working with versioned data involves knowledge of how the delta tables behave in accordance with the business (base) table.  Performing inserts directly against the business table but not in the A table may have negative consequences for your geodatabase.  I would always recommend using an ArcGIS client to perform edits because you can define the transactional version within your connection properties.  Hacking the business table or the delta tables is risky.  The second problem is somewhat related to the first problem.  Best practice for editing versioned data from a non-ArcGIS client is to edit the multiversion view for the feature class rather than the base table.  Multiversion views are database views according to your RDBMS and those are not open for write access via an ODBC connection using the Linked Table method I described above.  If you try to make edits from MS Access to a multiversion view, you will receive a error similar to State xyz is closed or Error during execution of trigger.  A potential third problem with this method might be that a limitation exists whereby you cannot make geometry edits, only attribute edits.  Even if you could alter the values of the SHAPE field, I would NEVER recommend doing this outside an ArcGIS client especially if you're using SDELOB.  If you're using ST_Geometry or SDO_GEOMETRY, you might be able to edit the shape using SQL but now you are talking about something way more sophisticated than a basic Form UI within MS Access.

In summary, the answer to your question is "yes, sort of, but not recommended in most cases".