GUID default value

[Vladimir]

Greetings

We are developing a complex system, which will run on Oracle, MS SQL
Server and MS Access databases. All the databases are to be replicated.
We are using a single common database scheme. For each table we have a
GUID column, which is filled once a new record is inserted. We have
default values for the GUID column in Oracle DB and MS SQL.

Could you please tell me which is the correct column data type for GUID
in MS Access and how can we set a default value for the column?

Here is a sample for MS SQL Server

create table PRODUCT_SRC (
ID int identity,
CR_OPE_ID int null default
dbo.GET_CURRENT_USER_ID(),
OPE_ID int null default
dbo.GET_CURRENT_USER_ID(),
CH_DATE datetime not null default
GETDATE(),
CR_DATE datetime not null default
GETDATE(),
STATUS char(1) not null default 'A',
GUID uniqueidentifier not null default newid(),
NAME varchar(100) not null,
constraint PK_PRODUCT_SRC primary key (ID)
)
go

and Oracle

create table PRODUCT_SRC (
ID INTEGER not null,
CR_OPE_ID INTEGER,
OPE_ID INTEGER,
CH_DATE DATE default SYSDATE
not null,
CR_DATE DATE default SYSDATE
not null ,
STATUS CHAR(1) default 'A' not
null,
GUID RAW(32) default
sys_guid(),
NAME VARCHAR2(100) not null,
constraint PK_PRODUCT_SRC primary key (ID)
);


Thanks, Vladimir.

 

[Guest]

You don't say specifically, but I am assuming that you are importing a GUID
into Access, rather than having Access create the GUID. If the GUID is
stored as type Byte, then it must be converted to a string, and then use
GUIDFromString function to convert to an Access GUID. If you are starting
from Access, there is a StringFromGUID function that will convert it the
other way.

 

[peregenem]

We are developing a complex system, which will run on Oracle, MS SQL
Server and MS Access databases.

Do you mean Access or do you mean Jet...?

All the databases are to be replicated.
We are using a single common database scheme. For each table we have a
GUID column, which is filled once a new record is inserted. We have
default values for the GUID column in Oracle DB and MS SQL.

Could you please tell me which is the correct column data type for GUID
in MS Access and how can we set a default value for the column?

create table PRODUCT_SRC (
GUID uniqueidentifier not null default newid(),

Jet has a GUID data type. Jet (or is it just Access?) has the ability
to auto-generate GUID values as default. However, I don't think you can
create such a column using SQL DDL alone e.g.

CREATE TABLE PRODUCT_SRC (
....
my_GUID GUID DEFAULT GenGUID NOT NULL

fails because the function which generates GUIDs, GenGUID, seems to be
private to Jet/Access. While this

my_GUID GUID IDENTITY

succeeds, values fail to be auto-generated.

I think you have to use the Access UI or DAO (or possibly the OLE DB
Provider for Jet via ADO) to create the table set the DefaultValue
property to GenGUID. If you did mean Access, then obviously this is no
problem. If, as I suspect, you meant Jet then I'm not entirely sure
this is possible.

 

[Vladimir]

Greetings

Thanks for you answers.

I'm rather new to MS Access (the area of my expertise is Oracle and MS
SQL Server), so please correct me if I'm wrong.

We are not going to use Access to create forms or whatever....
The application will use Jet engine to work with .mdb files.
The client workstations will not have Access installed, just MDAC.

Personally, I'm using MS Access 2002 to examine/alter the database
structure.
At our company we do all the database modelling in Sybase PowerDesigner
10. This tool can generate a DDL sql script as well as create a
database through ODBC.

So far I have been able to create "Replication ID" (GUID) fields, but I
couldn't make defaults for them. I guess, we will have to generate
GUIDs for .mdb databases in the application, though I'd rather use
defaults.

 

[Douglas J. Steele]

If you're thinking of using GUIDs in Jet databases, you might want to read
what MichKa has to say at http://www.trigeminal.com/usenet/usenet011.asp

 

[Vladimir]

Thanks Douglas

The article is good. I'd also add that using GUIDs as primary/foreign
keys is also a productivity impact for GUID fields are not indexed
well. But:
1. We are not using GUIDs as keys
2. We are not using MS Access replication engine. We will use our own
replication scheme.
3. We consider replacing MS Access with MSDE 2000 or MS SQL Server 2005
Express. I'm inclined to think that this would be the best solution.

Sincerely, Vladimir,
MCSD.NET