2

My overall goal:

My coworkers have an excel table which they update/use frequently. The table is not in a format that arcmap can decipher. Therefore, I've created a locked sheet (in the format I need) within the excel table, which points to the data my coworkers use. Periodically throughout the year I need to create a map with features that reflect the changes made in said excel table. My thinking is to store my own table in a geodatabase along with my feature class. I want to be able to easily update my table via a linked excel sheet within said gdb. So ultimately for every new map, I would like to just do a quick query in Access to update my table instead of manually doing it every time.

What I've done so far:

I know arcmap does not recognize the linked table alone, so I must add the linked data to a table, hence this next step: I created a personal geodatabase (.mdb) in arcmap and created a new table within the gdb which includes all the fields I need. When I open my gdb in Access 2013, I add my linked excel sheet. (I want to keep the linked table so it automatically updates when the excel table is updated).

After linking my excel sheet, I want to the append the values of the linked excel sheet to the table I created in my gdb.

I've tried using the Append Query, but nothing happens. The linked excel sheet values do not append to the table I created in my gdb. I also don't see an error messages.

Is there another way to do this? Am I doing something wrong?

Improve this question
3
  • 1
    (1/2)Hey - just spit balling here - but my initial reaction would be to use a Query Layer and have it reference the excel table. However, you will not be able to edit the excel table in ArcMap. My next reaction would be to create a data transfer process wherein the data is copied to the GDB and you do your revisions as needed then export - this will cause problems in workflow though. My next thought is an SDE with GlobalID's.
    – MDHald
    Jan 13, 2016 at 18:11
  • 1
    (2/2) it would look something like this. Flat File>MS SQL>Query Layer>SDE
    – MDHald
    Jan 13, 2016 at 18:11
  • I will give that a try! Although somehow I got everything to append to my table. I wonder if my queries actually worked and they were just slow, because my values appended multiple times. I'm still going to go through everything and make sure I get a fool proof method. Thank you for your help!
    – Katie B.
    Jan 13, 2016 at 18:26

1 Answer 1

Reset to default
2

My append query worked! I had to close and reopen my table in Access to see the changes. (Obviously I'm still learning MS Access) So this one's for the beginners!

Here's the query steps:

  1. Create Tab > Query Design
  2. Show Table Window > Select Linked Table
  3. (As long as the fields in the linked table and the gdb are identical) Double-Click the Asterisk* to add all the fields to the "Select Table." If the fields aren't identical, then you will have to manually add all the fields to the "Select Table."
  4. Create Tab > Query Design > Append
  5. Append Window > Append To Table Name: > Select gdb table > OK > Run
    (You might need to manually enter the fields if they're not identical)
  6. Values should now be appended to gdb table
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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