[Fwd: GUID and Access2000]

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter Plumber - 20 Feb 2006 17:20 GMT

-------- Original-Nachricht --------
Betreff: GUID an Access2000
Datum: Mon, 20 Feb 2006 10:27:24 +0100
Von: Peter Plumber <Klempner@gmxdot.net>
Newsgruppen: microsoft.public.vb.database.ado

I am wondering if it were possiple to use an INSERT INTO statement
for adding a new Record with a GUID-field

when the id-field is defined as long it works perfectly:
sSQL = "INSERT INTO tabelle2 (name, id) VALUES ('Toni', 12)"

while this causes a syntax error when using a "replication-id"
sSQL = "INSERT INTO tabelle1 (name, guid) VALUES " _
& "('Toni', '{9EB6D22A-B689-41C7-B422-CDF413A79ECF}')"

what am i doing wrong?

thx

Peter

Reply to this Message

Dave Bleby bleby ntlworld com. > - 21 Feb 2006 08:32 GMT

What you are doing wrong is trying to pass a string to a field defined as a
long without an explicit cast. However you will not be able to cast the
replication-id as any sort of numeric as it contains non-numeric characters
i.e. letters, hyphens, etc.
To get this data into the database you would need to recreate the field as a
varchar or text data type.

Signature

All the best,

Dave.

> -------- Original-Nachricht --------
> Betreff: GUID an Access2000
[quoted text clipped - 17 lines]
>
> Peter

Reply to this Message

Peter Plumber - 21 Feb 2006 09:37 GMT

david schrieb:

> What you are doing wrong is trying to pass a string to a field defined as a
> long without an explicit cast. However you will not be able to cast the
> replication-id as any sort of numeric as it contains non-numeric characters
> i.e. letters, hyphens, etc.
> To get this data into the database you would need to recreate the field as a
> varchar or text data type.

The guid-Field is defined as replication-id and in the
WHERE condition it is possible to use it like:

UPDATE tabelle1 SET name='Kurt'
WHERE guid='{9EB6D22A-B689-41C7-B491-CDF413A79ECF}'

Even the following lines do work well:

sSQL = "SELECT guid, name FROM tabelle1" _
& " WHERE GUID='{9EB6D22A-B689-41C7-B491-CDF413A79ECF}'"
rs.Open sSQL, cnn, adOpenDynamic, adLockOptimistic, adCmdText
rs.Fields("guid").Value = "{4EF815C6-A5B1-4BA1-9AFD-35BA97598D32}"
rs.Update

I wondered why the same action should not be possible using
only an UPDATE SQL-statment.

thx

Peter

Reply to this Message

Dave Bleby bleby ntlworld com. > - 23 Feb 2006 21:28 GMT

Hi Peter,

Sorry to take so long in replying.
I have just tried the "INSERT...INTO" statement that you gave in your
original post and it worked perfectly. Could you just re-state the problem
you are encountering?

Signature

All the best,

Dave.

> david schrieb:
> > What you are doing wrong is trying to pass a string to a field defined as a
[quoted text clipped - 24 lines]
>
> Peter

Reply to this Message

Peter Plumber - 24 Feb 2006 15:21 GMT

david schrieb:

> Hi Peter,
>
> Sorry to take so long in replying.
> I have just tried the "INSERT...INTO" statement that you gave in your
> original post and it worked perfectly. Could you just re-state the problem
> you are encountering?

this was bad luck. My problem had nothing to do with syntax
this query failed because of the name "guid"
(is it a property in Access.VBA - Access.Reference.GUID).

INSERT INTO tabelle1 (name, guid) VALUES
('Toni', '{9EB6D22A-B689-41C7-B422-CDF413A79ECF}')"

Since these works perfectly:

INSERT INTO tabelle1 (name, guidval) VALUES
('Toni', '{9EB6D22A-B689-41C7-B422-CDF413A79ECF}')"

>Even [guid] instead of guid works
INSERT INTO tabelle1 (name, [guid]) VALUES
('Toni', '{9EB6D22A-B689-41C7-B422-CDF413A79ECF}')"

>and no problem in SELECT queries (!)
SELECT guid, name FROM tabelle1
WHERE guid='{9EB6D22A-B689-41C7-B491-CDF413A79ECF}'

Thanks a lot

Peter

Reply to this Message

VB Forum / Database Access / February 2006

[Fwd: GUID and Access2000]