Sorry I'm new in this,

Add a record using T-SQL and Connection.Execute
How can i insert the identity in a VB-variable

Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
objConn = CurrentProject.Connection
objConn.Open
objConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES ('012345678')
SELECT @@IDENTITY as 'NewID'"

Msgbox NewID triggers an error !

Filip

In article <ZQzlb.98407$sC7.4689000@phobos.telenet-ops.be>,
benoit.filips@pandora.be says...
> objConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES ('012345678')
> SELECT @@IDENTITY as 'NewID'"

Did you forget to put the semicolon in, or did you mistype it when you
posted?

-- Rick

There was no semicolon in the code!I followed an example in a Book!
Where should i put the semicolon?

Filip

"Guinness Mann" <GMann@dublin.com> wrote in message
news:MPG.1a00945d5712d804989715@news.newsguy.com.. .
> In article <ZQzlb.98407$sC7.4689000@phobos.telenet-ops.be>,
> benoit.filips@pandora.be says...
> > objConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES
('012345678')
> > SELECT @@IDENTITY as 'NewID'"
>
> Did you forget to put the semicolon in, or did you mistype it when you
> posted?
>
> -- Rick
>

In article <E%Jlb.99955$R_6.4649061@phobos.telenet-ops.be>,
benoit.filips@pandora.be says...
> There was no semicolon in the code!I followed an example in a Book!
> Where should i put the semicolon?
> > > objConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES
> ('012345678')
> > > SELECT @@IDENTITY as 'NewID'"

Between statements, I believe. Where the "Go" would go. Otherwise you
must send two commands.

-- Rick

I try this but it keeps returning zero !

Dim newid As Long
CurrentProject.Connection.Execute "INSERT INTO [ORDER] (ORD_P_ID,
ORD_CREATION_DATE) VALUES ('4004', CONVERT(DATETIME,'" & Year(Date) & "-" &
Month(Date) & "-" & Day(Date) & " 00:00:00', 102)); select @@identity as
'newID'"
Me.Requery
MsgBox newid >> returns 0


Msgbox DMax("ORD_ID","[ORDER]") >> returns the new id

My question stays: is it possible to return the newid into a VB-variable
directly?

Filip


"Guinness Mann" <GMann@dublin.com> wrote in message
news:MPG.1a02f6c4f0a25599989718@news.newsguy.com.. .
> In article <E%Jlb.99955$R_6.4649061@phobos.telenet-ops.be>,
> benoit.filips@pandora.be says...
> > There was no semicolon in the code!I followed an example in a Book!
> > Where should i put the semicolon?
> > > > objConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES
> > ('012345678')
> > > > SELECT @@IDENTITY as 'NewID'"
>
> Between statements, I believe. Where the "Go" would go. Otherwise you
> must send two commands.
>
> -- Rick
>

In article <podmb.103924$yO.4810825@phobos.telenet-ops.be>,
benoit.filips@pandora.be says...
> I try this but it keeps returning zero !
>
> Dim newid As Long
> CurrentProject.Connection.Execute "INSERT INTO [ORDER] (ORD_P_ID,
> ORD_CREATION_DATE) VALUES ('4004', CONVERT(DATETIME,'" & Year(Date) & "-" &
> Month(Date) & "-" & Day(Date) & " 00:00:00', 102)); select @@identity as
> 'newID'"
> Me.Requery
> MsgBox newid >> returns 0
I think the zero means that it didn't affect any rows.

> My question stays: is it possible to return the newid into a VB-variable
> directly?

No. You must create a recordset, retrieve the results into the
recordset and then query the recordset.

-- Rick