Author Topic: MS Access Replication ID  (Read 1720 times)

«Midnight»

  • EA Guru
  • *****
  • Posts: 5651
  • Karma: +0/-0
  • That nice Mister Grey
    • View Profile
MS Access Replication ID
« on: January 29, 2006, 10:05:08 am »
I am trying to work with data models for MS Access 2000 (or later).

I am working with EA 6.1, build 785.

These versions of Access allow the use of a GUID as the primary key. You can set the data type to AutoNumber, and the field size to Replication ID (instead of Long Integer).

Same holds true for foreign keys; you set the data type to Number, and once again set the field size to Replication ID.

I cannot find any method to data model this in EA. If I import a data schema through ODBC EA does import the keys and relationships, but converts these fields to Text(255), in both the above cases.

Any ideas?

Sparx folks: have you any updates, profiles, or whatever for Jet 4.0 that could handle this?

David
No, you can't have it!

sargasso

  • EA Practitioner
  • ***
  • Posts: 1406
  • Karma: +1/-2
  • 10 COMFROM 30; 20 HALT; 30 ONSUB(50,90,10)
    • View Profile
Re: MS Access Replication ID
« Reply #1 on: January 29, 2006, 04:59:23 pm »
Oh I reckon this is a killer!!!

The problem is - Access reveals the GUID as a string.

I have tried for donkeys to get at the actual GUID internal representation (i.e. the 128bit value) to do exactly this type of thing elsewhere than EA - without luck!

Quote from Access help
Quote
The Microsoft Jet database engine stores GUIDs as arrays of type Byte. However, Microsoft Access can't return Byte data from a control on a form or report.
 Nor does it return it through ODBC!!

No suggestions - sory.

Bruce
"It is not so expressed, but what of that?
'Twere good you do so much for charity."

Oh I forgot, we aren't doing him are we.

«Midnight»

  • EA Guru
  • *****
  • Posts: 5651
  • Karma: +0/-0
  • That nice Mister Grey
    • View Profile
Re: MS Access Replication ID
« Reply #2 on: January 30, 2006, 04:33:51 am »
Still, it would be nice if we could at least forward engineer. I cannot find anything in EA at present that allows this. No data type, nor any kind of setting for field size or whatever.

As to ODBC, I wonder if we could do some kind of detective work here. It's been some time since I wrote a lot of ADOX or ODBC metadata stuff, but...

If we can detect the AutoNumber property of a primary key, and if the key reports a text data type, then it would have to be a GUID. Given a list of such fields, we can now match with foreign keys that reference these primay key fields, which would also have to be of type GUID. Would this be enough?

Of course, this approach presupposes that we can detect the AutoNumber property.

Anyone know off hand?
No, you can't have it!