1

Topic: External key

Hi
Where do I find the external key within the R6 table structure please? I need to update this for a record I had to manually add because it failed to import with the rest of a batch. I have its unique code (its a record from Living Record) but can't find a way to add it or extract these via the wizard or in the back-end.

Thanks
Purgle

Purgle Linham
Wiltshire & Swindon Biological Records Centre

2

Re: External key

The table is called Taxon_Occurrence_Ext_Ref

Mike Weideli

3

Re: External key

Thanks Mike. I don't have that table in my Access version which Rob set up and which I use to query/edit the database. I assumed these were completely linked so I don't quite understand why that is. I now have SQL Server Management Studio and can see it the table listed in there but have to idea how to use this to perform the actions I use Access for!

Thanks

Purgle Linham
Wiltshire & Swindon Biological Records Centre

4

Re: External key

You should  be able to attach the table. The process may vary depending on the Access version but basically

1. Open Access and select External Data from main menu
2. Select ODBC database
3. You will now need to select a source. Probably in machine data sources. Hopefully named something obvious. 
3. Select Link table (not import)
4. Find the table and highlight it then click OK to link it,


Let me know if this doesn't seem correct on your machine and if so what version of Access you have  and I will advise further.

Mike Weideli

5

Re: External key

The External Key is also actually in the report wizard under Taxon - Observations

Phil

6

Re: External key

Thanks Mike, with the help of our IT officer to work out how to make the connection with the database I've now added that table, and now know how to make sure my Access database is up to date if any table changes occur.

BUT, just as I found in the report wizard (thanks Phil I had found it in there but then couldn't get to it in edit mode :) ) I cannot add the missing code to the record as the field seems to be uneditable. Is there a way around this or should I just admit defeat and re-import it?

Thanks
Purgle

Purgle Linham
Wiltshire & Swindon Biological Records Centre

7

Re: External key

Permissions on this table is restricted which is probably why you are having problems. It is set up only to be used when importing. I don't think this makes sense. I attempted to do a batch update to avoid the need to use MS Access, but this doesn't work because of the  permisssions. I have noted this to change at the next release (6.26) , but if you  can get the following sql run this should fix it. 

GRANT UPDATE ON Taxon_Occurrence_Ext_Ref to R2K_FullEdit
GRANT DELETE ON Taxon_Occurrence_Ext_Ref to R2K_FullEdit
GRANT INSERT ON Taxon_Occurrence_Ext_Ref to R2K_FullEdit

Mike Weideli

8

Re: External key

Thanks Mike, I'll try that.

Purgle Linham
Wiltshire & Swindon Biological Records Centre

9

Re: External key

Hello
A related question - where do I find the MapMate Key in the R6 table structure please? I want to search this to compare records in a historical batch which was imported without this key. I looked in the Report wizard for it and in the back-end in what seemed the obvious tables but not found it.

Thanks

Purgle Linham
Wiltshire & Swindon Biological Records Centre

10

Re: External key

If the MapMate records were imported through the report wizard as MapMap data the R6 key will be an adaptation of the globally unique key exported from the 'MapMate'. This consists of a string of 7 to 9 digits and lower-case letters. The last three characters identify the copy of MapMate from which the observations came (referred to by MapMate as the Centre Unique Key (cuk)). This will be translated to an NBN style, 16-character key in which the cuk becomes the Site ID, and will used as the key for the Taxon Occurrence

I think it gets a key startin MM. If you can find some imported MapMate records and click on the data and click on the 'i' icon  you should see that the record identifier is an adapation of the Map Mate key. Once you know the format you should be able to get to the data through  the backend or with an XML report.

Mike Weideli

11

Re: External key

Thanks Mike
I knew that the MM records were identified in the metadata but hadn't thought about the idea that the ID was hiding within it. I was just trying to find a specific MM key field.

In case its useful for anyone else, the format in R6 seems to be e.g:

A MapMate ID (or GUK) '6vv1i2wn' in my original data becomes a R6 taxon occurrence key of 'MMEEE2WNEEE6VV1I'.

So the matching bit of the key is the '6vv1i' and the '2wn' CUK is used to create a Custodian value of 'MMEEE2WN'

Purgle

Purgle Linham
Wiltshire & Swindon Biological Records Centre