To GUID or not to GUID... 
Author Message
 To GUID or not to GUID...

 ... that is the question.

I've been heading in the direction of converting all my Keys from Autonumber
to Autonumber/Replication (GUID - Globally Unique ID).

I want to do that to enable merging remote databases into a master.

I've been working slowly in that direction waiting for the first Gotcha and
I found it yesterday.

I usually have forms with a Combo Box for easy lookup and goto a specific
record.

I create a recordset clone, do a findfirst on a criteria (eg. ID = IdValue
from the combo box.)

With a GUID ID I get the error, Recordset.findfirst does not support GUIDS.
I have to create a recordset as a table type, set the index, and use Seek.

This really messes up some nice things I do.

First - Is there anyway around this.  I want to base my search on the
selected
          Recordset of the form.  This could involve any number of criteria
including
          ad-hoc criteria like clicking on a field and clicking the Filter
by this field value
          Iconl

Second - Any other Gotchas I need to look out for?

Thanks - Tom



Thu, 19 Jun 2003 21:51:14 GMT  
 To GUID or not to GUID...

GUIDs and Forms don't necessarily mix... you'll want to read:

http://www.trigeminal.com/usenet/usenet011.asp

Steve



Quote:
>With a GUID ID I get the error, Recordset.findfirst does not support GUIDS.
>I have to create a recordset as a table type, set the index, and use Seek.

>This really messes up some nice things I do.

>First - Is there anyway around this.  I want to base my search on the
>selected
>          Recordset of the form.  This could involve any number of criteria
>including
>          ad-hoc criteria like clicking on a field and clicking the Filter
>by this field value
>          Iconl

>Second - Any other Gotchas I need to look out for?



Thu, 19 Jun 2003 22:27:46 GMT  
 To GUID or not to GUID...

Not very encouraging.  Now if I only knew what he meant when he mentioned "better ways to choose PKs " than GUIDs.

                    FINAL RECOMMENDATION: (from Link Below)
    While it is possible to surmount both the issues in #2 and #3, #2 is very problematic and is very difficult to consider maintainable in light of #3.  It is generally not worth the risks of bugs and issues or in forcing upgrade problems. There are better ways to choose PKs that do not involve such risks.... so it is almost always advisable to go that way if you want to write clean and easily maintainable database and replicated applications.

Quote:

> GUIDs and Forms don't necessarily mix... you'll want to read:

> http://www.trigeminal.com/usenet/usenet011.asp

> Steve



> >With a GUID ID I get the error, Recordset.findfirst does not support GUIDS.
> >I have to create a recordset as a table type, set the index, and use Seek.

> >This really messes up some nice things I do.

> >First - Is there anyway around this.  I want to base my search on the
> >selected
> >          Recordset of the form.  This could involve any number of criteria
> >including
> >          ad-hoc criteria like clicking on a field and clicking the Filter
> >by this field value
> >          Iconl

> >Second - Any other Gotchas I need to look out for?



Fri, 20 Jun 2003 05:48:16 GMT  
 To GUID or not to GUID...



Quote:
>Not very encouraging.  Now if I only knew what he meant when he mentioned "better ways to choose PKs " than GUIDs.

You can create composite PK's per replica that will not conflict with
other replica members.

IOW, use a specific code for each replica, maintained in a local (non
replicated) table, use that and another field (next available id) to
form your composite PK.

Steve



Sat, 21 Jun 2003 02:12:57 GMT  
 To GUID or not to GUID...
I have generated my own ID's in the past but want to stick with Access's
automated numbering if possible.

I finally gave up on using the GUID for combo box lookup
(jRecordSet.FindFirst(GUID = Value).  I tried adding a Random Autonumber and
combining that with the AddDate I already had (addDate = Now()).  and
putting them in the 2nd and 3rd hidden column in the combo box rowsource and
do the lookups.  It worked fine on a simple test.  I can beef up the code to
check the GUID after the lookup and jRecordset.FindNext if not the same.

Are there any other gotcha's in Using GUIDs instead of AutoNumbers?

Tom


Quote:



> >Not very encouraging.  Now if I only knew what he meant when he mentioned

"better ways to choose PKs " than GUIDs.
Quote:

> You can create composite PK's per replica that will not conflict with
> other replica members.

> IOW, use a specific code for each replica, maintained in a local (non
> replicated) table, use that and another field (next available id) to
> form your composite PK.

> Steve



Sat, 21 Jun 2003 22:36:48 GMT  
 To GUID or not to GUID...
 other gotchas? DLookup problems, Form/Subform problems, combo/listbox
issues, and more? What will it take to convince you?  KB article entitled

"Access: GUID primary keys will kill you in your sleep"

or something like that?

BTW -- a PK involving a DATE field, which is a double and thus subject to
floating point issues, is also a bad choice. I would suggest serious design
thought and perhaps a good look at the NATURAL keys?

--
MichKa

a new book on internationalization in VB at
http://www.i18nWithVB.com/


Quote:
> I have generated my own ID's in the past but want to stick with Access's
> automated numbering if possible.

> I finally gave up on using the GUID for combo box lookup
> (jRecordSet.FindFirst(GUID = Value).  I tried adding a Random Autonumber
and
> combining that with the AddDate I already had (addDate = Now()).  and
> putting them in the 2nd and 3rd hidden column in the combo box rowsource
and
> do the lookups.  It worked fine on a simple test.  I can beef up the code
to
> check the GUID after the lookup and jRecordset.FindNext if not the same.

> Are there any other gotcha's in Using GUIDs instead of AutoNumbers?

> Tom





> > >Not very encouraging.  Now if I only knew what he meant when he
mentioned
> "better ways to choose PKs " than GUIDs.

> > You can create composite PK's per replica that will not conflict with
> > other replica members.

> > IOW, use a specific code for each replica, maintained in a local (non
> > replicated) table, use that and another field (next available id) to
> > form your composite PK.

> > Steve



Sat, 21 Jun 2003 22:55:31 GMT  
 To GUID or not to GUID...

Quote:
> other gotchas? DLookup problems, Form/Subform problems, combo/listbox
> issues, and more? What will it take to convince you?  KB article entitled

> "Access: GUID primary keys will kill you in your sleep"

> or something like that?

That is too funny. How about a grade B horror film about the misuse of GUID;
"Return of the GUIDs"

Steve



Sun, 22 Jun 2003 01:58:06 GMT  
 To GUID or not to GUID...

Quote:
> Are there any other gotcha's in Using GUIDs instead of AutoNumbers?

In addition to the items that MichKa mentions, creating parameter based
queries on GUIDs will be problematic.

I would urge you to reconsider using a date field as part of a PK. Remember
that a date field is stored as a double internally, date to the right of the
decimal point, time to the right of the decimal point. I could envision
situations where the result is not what one would expect...

Steve



Sun, 22 Jun 2003 02:02:03 GMT  
 To GUID or not to GUID...
On Tue, 2 Jan 2001 12:58:06 -0500, "Steve Thompson"

Quote:

>> "Access: GUID primary keys will kill you in your sleep"

>> or something like that?

>That is too funny. How about a grade B horror film about the misuse of GUID;
>"Return of the GUIDs"

<snork> Well, The Blob was pretty gooey... <g>

           John W. Vinson[MVP]    
http://go.compuserve.com/msdevapps?loc=us&access=public



Sun, 22 Jun 2003 09:31:20 GMT  
 To GUID or not to GUID...

ohhhhhhhhhhh, that's a good new year start.

Vanderghast, Access MVP


Quote:
> On Tue, 2 Jan 2001 12:58:06 -0500, "Steve Thompson"

> >> "Access: GUID primary keys will kill you in your sleep"

> >> or something like that?

> >That is too funny. How about a grade B horror film about the misuse of
GUID;
> >"Return of the GUIDs"

> <snork> Well, The Blob was pretty gooey... <g>

>            John W. Vinson[MVP]
> http://go.compuserve.com/msdevapps?loc=us&access=public



Sun, 22 Jun 2003 19:41:20 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. sorry but again: GUID and AUTONUMBER (not GUID only, not AUTONUMBER only, but both)

2. How to convert a GUID string to a GUID data type

3. Using a GUID as a Primary Key or indexing a GUID

4. AddNew with a GUID - can't get back the GUID

5. GUID - Reading the GUID of a file

6. GUID not allowed in Find - aarrgghh!!!!

7. Help: How to create an autonumber field as GUID by code

8. Problem with GUID Generated by function GetGUID in knowledge base article

9. Access GUID???

10. Can't .FINDFIRST a GUID???

11. GUID for class modules and routines

12. Using GUID as primary key in table cant create join nor use findfirst method

 

 
Powered by phpBB® Forum Software