uniqueidentifier in Access 2002

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Patrice - 19 Jan 2007 16:04 GMT

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

Reply to this Message

Sylvain Lafontaine - 20 Jan 2007 07:46 GMT

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.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

>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
[quoted text clipped - 20 lines]
>
> TIA

Reply to this Message

Patrice - 22 Jan 2007 09:16 GMT

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

> You don't tell why you are using an uniqueidentifier (GUID) column, so
> it's hard to give you any recommendation.
[quoted text clipped - 37 lines]
>>
>> TIA

Reply to this Message

Patrice - 22 Jan 2007 12:48 GMT

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 !!!

> 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
[quoted text clipped - 56 lines]
>>>
>>> TIA

Reply to this Message

Patrice - 22 Jan 2007 13:34 GMT

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.

Signature

Patrice

> After further investigation I just found that there is a ROWGUIDCOL
> attribute that looks like necessary to identify the uniqueidentifier
[quoted text clipped - 63 lines]
>>>>
>>>> TIA

Reply to this Message

Patrice - 22 Jan 2007 15:04 GMT

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...

> 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
[quoted text clipped - 69 lines]
>>>>>
>>>>> TIA

Reply to this Message