uniqueidentifier in Access 2002

[Patrice]

I recently got an Access 2002 ADP that uses SQL Server 2000. I noticed that
all forms are unbound. After some research it looks like there is (was ?) a
known problem about uniqueidentifier columns.

For example a bound Access form will create default values even for nullable
uniqueidentifier columns. Similarly it looks like that the uniqueidentifier
retrieved from a combo is a string while the one retrieved from the
underlying field is under its native format (ie a 16 byte array).

Though I've seen several time the formar issue I didn't saw any really
convincing solution. Is there a way to solve this in Access 2002 ?

For now :
- I would have still to use unbound forms
- a suggested soltuino was to clean up those values (by checking if they are
part of the combox values and deleting if not).
- I could think about something like using varchar(36) instead of unique
identifier (sacrifying the size to get back the usual correct behavior).
- other solution ?

Any advice would be helpfull.

TIA

 

[Sylvain Lafontaine]

You don't tell why you are using an uniqueidentifier (GUID) column, so it's
hard to give you any recommendation.

Combobox values are always strings, so that's no news here for the GUID.
(Even with other types like Int, this often causes some problems because
null values are converted to an empty string "".)

Here some info about GUID but I don't know if they apply to ADP:

http://www.trigeminal.com/usenet/usenet011.asp?1033
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=9#9
http://www.trigeminal.com/code/guids.bas

Finally, using unbound forms is probably the fastest way to access
SQL-Server from Access over the Internet (WAN); so if you're not on a LAN,
then you should probably leave these things in place.

 

[Patrice]

They are used as pk and fk all over the place likely to handle data
consolidations that never took place. When used as a FK, Access creates
automatically a value for those fields when the form is bound.

For now the application uses unbound forms but basically the first screen
just allows to select THE record you wan't to work on, the second shows the
record and then you have to close and go back to the filtering screen to
work on another record).

For now I'm willing to keep this unchanged but I would like to suggest
something more practical for new screens while waiting perhaps for a
redesign (the db has some other problems). My personal preference is to use
bound forms unless I have a valid reason not to do so (IMO the only reason
to doing this in this application is to workaround the GUID problem).

 

[Patrice]

After further investigation I just found that there is a ROWGUIDCOL
attribute that looks like necessary to identify the uniqueidentifier column
that is used as the row id column. The Access UI perhaps choke because all
columns are just uniqueidentifiers. I'm about to give this a try. Wish me
luck !!!

 

[Patrice]

Doesn't look like it make any difference. It's likely I'll have to accept
being forced in this path while waiting for a DB overhaul that has also
other problems anyway...

Too bad.

 

[Patrice]

And finally same behavior in Access 2003. If i create a new table in an ADP
project and add multiple uniqueidentifier columns they are all initialized
with a default value by the UI even though they have no default values
defined...