Insert tagged value from MS Access

[bholtzman]

I've built a tool in MS Access that connects to the EA database and provides tracking and a custom metadata "overlay". The tool can export a combined set of EA and non-EA metadata in various custom formats. One of the data elements I am extracting is from the two tagged value tables in EA, t_attributetag and t_objectproperties.

I have limited the Access app to querying the native EA tables only. I have never done any inserts from the Access app, but I have selectively run a few table updates manually.

The question has now come up as to whether new tagged value entries can be made from the Access side. Is that a bad idea or is it doable with careful diligence? Is there a way to populate the ea_guid field in the tagged value tables?

I am using EA 13.5 and Access 2016.

Thanks.

Bill

[qwerty]

Generally it's a bad idea to bypass the API when creating elements. It is possible but not recommended at all. Tagged belong to those very special elements you should not create outside the API. The reason is that TVs are usually used with MDGs which tend to put things in t_xref. And that's just black magic. Not that it's impossible. However, it's very likely that the results are similar to what the sorcerer's apprentice achieved: a mess.

If you want to create just plain tagged values with no relation to an MDG you might do so. Know that they will not be visible in the UI unless you reload relevant parts manually. Again, I would't do that too without strong force.

q.

[bholtzman]

Thanks qwerty. Can you tell me anything about what the ea_guid does?

Bill

[qwerty]

It's a GUID, simple as that. Just invent one. There are libs which provide creation of GUIDs. Just use one of them

q.

[bholtzman]

So it's completely arbitrary and my choice of one has no impact on EA (unless I happen to miraculously match another one?

Bill

[qwerty]

Uhm. Yeeeees, well. Basically that's true for GUIDs. But now as you ask, there are a couple of exceptions. One is, that package and its element guid are identical. And then (now, where was that stored in my brain?) there are some guids which partially match other guids (or at least something was coded in the guid). I just check with t_attributetags and they don't seem to be the ones. The simplest would be to just give it a shot on a test repository. If the TVs appear and the consistency check does not moan it should be fine (not giving any guarantees here; see above).

q.

[bholtzman]

Got it. I'll try it out and report back!

Bill

[bholtzman]

Hi qwerty,
I did have success in creating completely random guids and inserting them into the ea_guid field in t_attributetags along with the user-specified fields - ElementID (ID field from t_attribute), Property and Value. The data thus entered using MS Access then appeared perfectly in the EA model. I was also able to manipulate the same data from either EA or Access, updating and deleting. The only thing is I now get two errors when I close the Properties dialog box in EA. See attached. I get the Microsoft Cursor Engine error first, and then the Recordset error. But the mechanics of EA and the Access app still work properly. (I don't seem to be able to upload images of the errors to this site.)

I ran some code to see if an EA-generated ea_guid from t_attributetags appeared anywhere else in the database, at least in any table visible to me in MS Access. There was none. So I feel like your assumption that this ea_guid value is basically random and serves no core purpose is valid.

What are your thoughts? Thanks.

**Update** The errors were due to incorrect selection of ODBC driver options so disregard them! The method seems to actually work!

Bill

PS We use EA 13.5.1351 Corporate and are accessing a MySQL database on Amazon Web Services.

[qwerty]

I think that if you tested it and found no issue then it should be fine to go (unless one of the Sparxian shouts STOP). The above mentioned cross references were used only in rare places and the area you were walking does not seem to be the dangerous one.

q.

[bholtzman]

That's awesome. In the course of trying to evaluate this, as I mentioned I built some code to look for a matching guid in any table. In so doing, I am realizing the value of the guid. If you have metadata that can apply to individual rows in a variety of tables in your database, the guid gives you a single "primary key" to use to look up those rows. I understand that better now that I have code that can search all of your tables for a specific value in a specific field. If anyone is interested, below is my VB code.

Bill

Private Function findGUID(guid As String)
'this loops through all EA tables in the database to find a specific guid, displaying a message box to identify the table where it was found,
'along with the 1st field in that table and the value of the field for that guid

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
Dim fld As DAO.Field, sql As String, rst As Recordset, i As Single, FirstField As String

For Each tdf In db.TableDefs
i = 0
    If (tdf.name Like "t_*") Then
            For Each fld In tdf.Fields
            If i = 0 Then
            FirstField = fld.name
            i = 1
            End If
            If fld.name = "ea_guid" Then
                sql = "select " & FirstField & " as FF from " & tdf.name & " where " & fld.name & " = " & Chr(34) & guid & Chr(34) & ";"
                Set rst = CurrentDb.OpenRecordset(sql)
                If rst.RecordCount > 0 Then
                MsgBox (tdf.name & "." & FirstField & ": " & rst!FF)
                End If
            End If
            Next
    End If
Set fld = Nothing
Next

MsgBox ("Done")

End Function