We have a dataset with a personal GDB (mdb) with several feature classes and an mxd tied to those features to create layers based on definition queries…

Currently to QAQC our data we use MsAccess queries to cross check data both within a feature class table and across feature classes that have common ID’s…

This is building space data, so things we check are a field that marks the space vacant [sp_vacant ] and another field within the same feature class that marks the space classification (hallway, stair, office, etc [sp_type])… Since we do not want to mark public areas as vacant the query checks any vacant values against the space type to get a list of possible errors…

Another type of MSAccess query we run would be to check personnel recorded for that building [sp_person] in one feature class against a matching field [sp_type] from the other feature class where both features contain matching room ID’s [sp_ID]…

To do the previous we build queries in Access, export those tables in Excel and use them as a checklist to edit possible mistakes. I then have to go back in Access once edits are made to re run the queries to check for completeness.

Rather than all that I would like to create, run and view the same type of queries in ArcMap rather than using MsAccess.

Or perhaps getting an editable layer with attributes that I can change directly and those changes will propagate to the original feature class data.

So a table or preferably layer that responds to a sql like query.

closed as too broad by PolyGeo, Fezter, MappaGnosis, Jakub Sisak GeoGraphics, Jason Scheirer Feb 26 '14 at 23:33

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Your Question looks like it contains a number of questions, and so, as it stands is a poor fit for the Q&A format of GIS SE. To focus it down to a Question that is more Answer-able, I recommend that you use the edit button beneath it to revise it to talk mainly about the part you must want answered that seems to be your 4th point (although that relies on 3. being answered first). Try to describe how you are doing that part now (step-wise) so that we can recommend where your workflow could be modified. – PolyGeo Feb 25 '14 at 22:27
  • Actually I think it is point 2. that you most want answered so that is what I think the focus of your Question should become. Be sure to include a single question mark in your revised Question to pinpoint what you are asking. – PolyGeo Feb 25 '14 at 22:45
  • 1
    What alternatives to MS Access queries have you considered or evaluated? Sounds more like a BIM problem than a GIS problem, also. – blah238 Feb 25 '14 at 22:54
  • If you create an MS Access database and save it as MSAccess 2007 or before, that database can be imported to ArcGIS as a personal geodatabase. So when you open your mxd it is tied to you MS Access db as a geo db. On top of that, the attribute tables you can open in arcGIS you can select all records, copy and paste direct to excel. No reason to export. So your access database is intrinsically tied to your mxd. You update that and your mxd will update. – Oliver Burdekin Feb 26 '14 at 1:48
  • msAccess 2007 might be wrong, but an .mdb (access format) shows up in arcGIS as a personal geo db. :) – Oliver Burdekin Feb 26 '14 at 2:00

I recommend you research Microsoft Sql Server Express because it supports Spatial data as a native datatype. ArcGIS supports read/write to Sql Server Express. You have the full power of T-SQL at your disposal, allowing you to mix and match Attribute queries with Spatial queries, join tables, create views, index data, backup data. The scripting in Sql Server is much better than MS Access.

  • That sounds really great and i will definitely look into the possibilities. For this project we are required to submit in mxd/mdb data set for delivery. Would that be possible using a SQL server? – Moon47 Feb 26 '14 at 23:19
  • Yes, you can export MS SQL Server Feature Classes and Tables to a personal geodatabase. The mxd would reference the pgeo. – klewis Feb 27 '14 at 13:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.