Use a SELECT @@IDENTITY as a sub-query in an UPDATE ?

[mark4asp]

It didn't work.

UPDATE tblBlogs SET link_ID = blog_ID
WHERE blog_ID = @@IDENTITY

I get this error when trying to save an Access query:

syntax error (missing operator) in
query expression blog_ID = @@IDENTITY

Note: blog_ID is an autonumber key for tblBlogs and link_ID is a Long
Integer field in the same table. The Access 2002 database had SQL
Server Compatible Syntax (ANSI 92) checked in Options.

The INSERT statement does worked OK. So there was an @@IDENTITY
created in that session.

========== ========== ==========
ASP Code:
========== ========== ==========

Set conn = getAdoConn("Blog")
sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
"VALUES('" & sTitle & "','" & sBody & "'," &_
bDisplay & "," & iAuthor & ")"
doAdoSqlCmd conn, sSQL
sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
"WHERE blog_ID = @@IDENTITY"
doAdoSqlCmd conn, sSQL
KillConn conn

Function getAdoConn(db)
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 15
conn.CommandTimeout = 30
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
Server.MapPath("..\database\" & db & ".mdb;") &_
"User ID=Admin;Password=;"
Set getAdoConn = conn
End Function

Sub doAdoSqlCmd(conn, sqlcmd)
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = 1
cmd.CommandText = sqlcmd
cmd.Execute
Set cmd = Nothing
End Sub

Sub KillConn(cn)
cn.Close
Set cn = Nothing
End Sub

========== ========== ==========
End Code.
========== ========== ==========


On Fri, 12 Sep 2003 06:58:54 -0400, "Michel Walsh"
<vanderghast@msn.com> wrote:

>Hi,
>
>
> You don't need to "select" it. If you use ADO ( not DAO, not the query
>designer), the following should work:
>
> CurrentProject.Connection.Execute "UPDATE tableName SET f1 = f2 WHERE
>f3=@@IDENTITY"
>
>
> Note that @@IDENTITY is maintained by connection. You have to use the
>same connection object that you did something to "assign" the @@IDENTITY to
>something, that to get it back. As a trivial example, but worth of mention,
>@@IDENTITY of the currentProject.Connection won't be aware of the latest DAO
>operations.
>
>
>
>Hoping it may help,
>Vanderghast, Access MVP
>
>
>"mark4asp" <mark4asp#killspam#@ntlworld.com> wrote in message
>news:2lv0mvs9tq3oj9jc4u7kmb01f751h3utej@4ax.com.. .

>> Is it possible to use a SELECT @@IDENTITY statement as a sub-query in
>> an UPDATE ?
>>
>> For instance I wanted to run the following query but Access would not
>> allow me to enter it:
>>
>> I've tried each of the following:
>>
>> UPDATE tblBlogs SET link_ID = blog_ID
>> WHERE blog_ID=(SELECT @@IDENTITY AS iBlogID);
>>
>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY);
>>
>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY AS iBlogID);
>>
>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>> WHERE tblBlogs.blog_ID = SOME (SELECT @@IDENTITY AS iBlogID);
>>
>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>> WHERE tblBlogs.blog_ID = ANY (SELECT @@IDENTITY);
>>
>> Why is it not possible to do this?
>>
>> The following does work:
>>
>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>> WHERE tblBlogs.blog_ID = (SELECT MAX(blog_ID) FROM tblBlogs);
>>

>

[Bob Barrows]

I would think you would have better success doing this* :

sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
"VALUES('" & sTitle & "','" & sBody & "'," &_
bDisplay & "," & iAuthor & ")"
doAdoSqlCmd conn, sSQL
sSQL = "Select @@IDENTITY"
set rs = conn.execute sSQL,,1
newID = rs(0)
rs.close
set rs=nothing
response.write newID 'for debugging
sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
"WHERE blog_ID = " & newID
doAdoSqlCmd conn, sSQL
KillConn conn

Sub doAdoSqlCmd(conn, sqlcmd)
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = 1
cmd.CommandText = sqlcmd
cmd.Execute ,,128 'use 128 to specify that no records are returned
Set cmd = Nothing
End Sub



*I'm going to continue illustrating using dynamic sql as you have done -
however, I recommend that you switch to using saved parameter queries

HTH,
Bob Barrows

mark4asp wrote:

> It didn't work.
>
> UPDATE tblBlogs SET link_ID = blog_ID
> WHERE blog_ID = @@IDENTITY
>
> I get this error when trying to save an Access query:
>
> syntax error (missing operator) in
> query expression blog_ID = @@IDENTITY
>
> Note: blog_ID is an autonumber key for tblBlogs and link_ID is a Long
> Integer field in the same table. The Access 2002 database had SQL
> Server Compatible Syntax (ANSI 92) checked in Options.
>
> The INSERT statement does worked OK. So there was an @@IDENTITY
> created in that session.
>
> ========== ========== ==========
> ASP Code:
> ========== ========== ==========
>
> Set conn = getAdoConn("Blog")
> sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
> "VALUES('" & sTitle & "','" & sBody & "'," &_
> bDisplay & "," & iAuthor & ")"
> doAdoSqlCmd conn, sSQL
> sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
> "WHERE blog_ID = @@IDENTITY"
> doAdoSqlCmd conn, sSQL
> KillConn conn
>
> Function getAdoConn(db)
> Dim conn
> Set conn = Server.CreateObject("ADODB.Connection")
> conn.ConnectionTimeout = 15
> conn.CommandTimeout = 30
> conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
> Server.MapPath("..\database\" & db & ".mdb;") &_
> "User ID=Admin;Password=;"
> Set getAdoConn = conn
> End Function
>
> Sub doAdoSqlCmd(conn, sqlcmd)
> Dim cmd
> Set cmd = Server.CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = conn
> cmd.CommandType = 1
> cmd.CommandText = sqlcmd
> cmd.Execute
> Set cmd = Nothing
> End Sub
>
> Sub KillConn(cn)
> cn.Close
> Set cn = Nothing
> End Sub
>
> ========== ========== ==========
> End Code.
> ========== ========== ==========
>
>
> On Fri, 12 Sep 2003 06:58:54 -0400, "Michel Walsh"
> <vanderghast@msn.com> wrote:
>

>> Hi,
>>
>>
>> You don't need to "select" it. If you use ADO ( not DAO, not the
>> query designer), the following should work:
>>
>> CurrentProject.Connection.Execute "UPDATE tableName SET f1 = f2
>> WHERE f3=@@IDENTITY"
>>
>>
>> Note that @@IDENTITY is maintained by connection. You have to use
>> the same connection object that you did something to "assign" the
>> @@IDENTITY to something, that to get it back. As a trivial example,
>> but worth of mention, @@IDENTITY of the currentProject.Connection
>> won't be aware of the latest DAO operations.
>>
>>
>>
>> Hoping it may help,
>> Vanderghast, Access MVP
>>
>>
>> "mark4asp" <mark4asp#killspam#@ntlworld.com> wrote in message
>> news:2lv0mvs9tq3oj9jc4u7kmb01f751h3utej@4ax.com...

>>> Is it possible to use a SELECT @@IDENTITY statement as a sub-query
>>> in
>>> an UPDATE ?
>>>
>>> For instance I wanted to run the following query but Access would
>>> not allow me to enter it:
>>>
>>> I've tried each of the following:
>>>
>>> UPDATE tblBlogs SET link_ID = blog_ID
>>> WHERE blog_ID=(SELECT @@IDENTITY AS iBlogID);
>>>
>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY);
>>>
>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY AS iBlogID);
>>>
>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>> WHERE tblBlogs.blog_ID = SOME (SELECT @@IDENTITY AS iBlogID);
>>>
>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>> WHERE tblBlogs.blog_ID = ANY (SELECT @@IDENTITY);
>>>
>>> Why is it not possible to do this?
>>>
>>> The following does work:
>>>
>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>> WHERE tblBlogs.blog_ID = (SELECT MAX(blog_ID) FROM tblBlogs);

[Michel Walsh]

Hi,

Should be a matter of where you used it. If you used it in the query
designer, it won't work, if you use it with DAO, it won't work. You have to
use ADO and the same connection you used to append the data. The following
work, in Access, in the immediate (debug) window (Access 2000, or more
recent, Access the application, maintains a permanent ADO connection to the
database, Jet or MS SQL Server, called CurrentProject.Connection) :


===========
CurrentProject.Connection.Execute "CREATE TABLE demo( f1 COUNTER, f2
varchar(50));"
CurrentProject.Connection.Execute "INSERT INTO demo (f2) VALUES ( 'aaa') ; "
CurrentProject.Connection.Execute "INSERT INTO demo (f2) VALUES (
@@Identity ) ; "

? CurrentProject.COnnection.Execute("SELECT * FROM demo").GetString()
1 aaa
2 1

===========



Hoping it may help,
Vanderghast, Access MVP



"mark4asp" <mark4asp#killspam#@ntlworld.com> wrote in message
news:tkl5mvchd076fh8ud4kn3v9e3s1amgfnqf@4ax.com...

> It didn't work.
>
> UPDATE tblBlogs SET link_ID = blog_ID
> WHERE blog_ID = @@IDENTITY
>
> I get this error when trying to save an Access query:
>
> syntax error (missing operator) in
> query expression blog_ID = @@IDENTITY
>
> Note: blog_ID is an autonumber key for tblBlogs and link_ID is a Long
> Integer field in the same table. The Access 2002 database had SQL
> Server Compatible Syntax (ANSI 92) checked in Options.
>
> The INSERT statement does worked OK. So there was an @@IDENTITY
> created in that session.
>
> ========== ========== ==========
> ASP Code:
> ========== ========== ==========
>
> Set conn = getAdoConn("Blog")
> sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
> "VALUES('" & sTitle & "','" & sBody & "'," &_
> bDisplay & "," & iAuthor & ")"
> doAdoSqlCmd conn, sSQL
> sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
> "WHERE blog_ID = @@IDENTITY"
> doAdoSqlCmd conn, sSQL
> KillConn conn
>
> Function getAdoConn(db)
> Dim conn
> Set conn = Server.CreateObject("ADODB.Connection")
> conn.ConnectionTimeout = 15
> conn.CommandTimeout = 30
> conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
> Server.MapPath("..\database\" & db & ".mdb;") &_
> "User ID=Admin;Password=;"
> Set getAdoConn = conn
> End Function
>
> Sub doAdoSqlCmd(conn, sqlcmd)
> Dim cmd
> Set cmd = Server.CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = conn
> cmd.CommandType = 1
> cmd.CommandText = sqlcmd
> cmd.Execute
> Set cmd = Nothing
> End Sub
>
> Sub KillConn(cn)
> cn.Close
> Set cn = Nothing
> End Sub
>
> ========== ========== ==========
> End Code.
> ========== ========== ==========
>
>
> On Fri, 12 Sep 2003 06:58:54 -0400, "Michel Walsh"
> <vanderghast@msn.com> wrote:
>

> >Hi,
> >
> >
> > You don't need to "select" it. If you use ADO ( not DAO, not the

query

> >designer), the following should work:
> >
> > CurrentProject.Connection.Execute "UPDATE tableName SET f1 = f2 WHERE
> >f3=@@IDENTITY"
> >
> >
> > Note that @@IDENTITY is maintained by connection. You have to use the
> >same connection object that you did something to "assign" the @@IDENTITY

to

> >something, that to get it back. As a trivial example, but worth of

mention,

> >@@IDENTITY of the currentProject.Connection won't be aware of the latest

DAO

> >operations.
> >
> >
> >
> >Hoping it may help,
> >Vanderghast, Access MVP
> >
> >
> >"mark4asp" <mark4asp#killspam#@ntlworld.com> wrote in message
> >news:2lv0mvs9tq3oj9jc4u7kmb01f751h3utej@4ax.com.. .

> >> Is it possible to use a SELECT @@IDENTITY statement as a sub-query in
> >> an UPDATE ?
> >>
> >> For instance I wanted to run the following query but Access would not
> >> allow me to enter it:
> >>
> >> I've tried each of the following:
> >>
> >> UPDATE tblBlogs SET link_ID = blog_ID
> >> WHERE blog_ID=(SELECT @@IDENTITY AS iBlogID);
> >>
> >> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY);
> >>
> >> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY AS iBlogID);
> >>
> >> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >> WHERE tblBlogs.blog_ID = SOME (SELECT @@IDENTITY AS iBlogID);
> >>
> >> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >> WHERE tblBlogs.blog_ID = ANY (SELECT @@IDENTITY);
> >>
> >> Why is it not possible to do this?
> >>
> >> The following does work:
> >>
> >> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >> WHERE tblBlogs.blog_ID = (SELECT MAX(blog_ID) FROM tblBlogs);
> >>

> >

>

[Michel Walsh]

Hi,



To continue on Bob idea, using a stored procedure is a way to be sure that
the same connection is used. Note that in general, that kind of stuff has to
be embedded into a transaction (so if one update fail, the whole stuff
fails), so, without T-SQL, all in VBA (so it works even for Jet) :

myAdoConnection.Open ' if you wish to open "as late as possible"
On Error Resume Next
With myAdoConnection
.BeginTrans
.Execute " .... " ' the first insert here
.Execute " .... VALUES( @@Identity, .... ) "
If 0 = Err.Number then
.CommitTrans
Else
. RollbackTrans
End If
End With
On Error ... ' whatever appropriate
myAdoConnection.Close 'if you wish to "close as soon as
possible"



Hoping it may help,
Vanderghast, Access MVP


"mark4asp" <mark4asp#killspam#@ntlworld.com> wrote in message
news:tkl5mvchd076fh8ud4kn3v9e3s1amgfnqf@4ax.com...

> It didn't work.
>
> UPDATE tblBlogs SET link_ID = blog_ID
> WHERE blog_ID = @@IDENTITY
>
> I get this error when trying to save an Access query:
>
> syntax error (missing operator) in
> query expression blog_ID = @@IDENTITY
>
> Note: blog_ID is an autonumber key for tblBlogs and link_ID is a Long
> Integer field in the same table. The Access 2002 database had SQL
> Server Compatible Syntax (ANSI 92) checked in Options.
>
> The INSERT statement does worked OK. So there was an @@IDENTITY
> created in that session.
>
> ========== ========== ==========
> ASP Code:
> ========== ========== ==========
>
> Set conn = getAdoConn("Blog")
> sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
> "VALUES('" & sTitle & "','" & sBody & "'," &_
> bDisplay & "," & iAuthor & ")"
> doAdoSqlCmd conn, sSQL
> sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
> "WHERE blog_ID = @@IDENTITY"
> doAdoSqlCmd conn, sSQL
> KillConn conn
>
> Function getAdoConn(db)
> Dim conn
> Set conn = Server.CreateObject("ADODB.Connection")
> conn.ConnectionTimeout = 15
> conn.CommandTimeout = 30
> conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
> Server.MapPath("..\database\" & db & ".mdb;") &_
> "User ID=Admin;Password=;"
> Set getAdoConn = conn
> End Function
>
> Sub doAdoSqlCmd(conn, sqlcmd)
> Dim cmd
> Set cmd = Server.CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = conn
> cmd.CommandType = 1
> cmd.CommandText = sqlcmd
> cmd.Execute
> Set cmd = Nothing
> End Sub
>
> Sub KillConn(cn)
> cn.Close
> Set cn = Nothing
> End Sub
>
> ========== ========== ==========
> End Code.
> ========== ========== ==========
>
>
> On Fri, 12 Sep 2003 06:58:54 -0400, "Michel Walsh"
> <vanderghast@msn.com> wrote:
>

> >Hi,
> >
> >
> > You don't need to "select" it. If you use ADO ( not DAO, not the

query

> >designer), the following should work:
> >
> > CurrentProject.Connection.Execute "UPDATE tableName SET f1 = f2 WHERE
> >f3=@@IDENTITY"
> >
> >
> > Note that @@IDENTITY is maintained by connection. You have to use the
> >same connection object that you did something to "assign" the @@IDENTITY

to

> >something, that to get it back. As a trivial example, but worth of

mention,

> >@@IDENTITY of the currentProject.Connection won't be aware of the latest

DAO

> >operations.
> >
> >
> >
> >Hoping it may help,
> >Vanderghast, Access MVP
> >
> >
> >"mark4asp" <mark4asp#killspam#@ntlworld.com> wrote in message
> >news:2lv0mvs9tq3oj9jc4u7kmb01f751h3utej@4ax.com.. .

> >> Is it possible to use a SELECT @@IDENTITY statement as a sub-query in
> >> an UPDATE ?
> >>
> >> For instance I wanted to run the following query but Access would not
> >> allow me to enter it:
> >>
> >> I've tried each of the following:
> >>
> >> UPDATE tblBlogs SET link_ID = blog_ID
> >> WHERE blog_ID=(SELECT @@IDENTITY AS iBlogID);
> >>
> >> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY);
> >>
> >> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY AS iBlogID);
> >>
> >> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >> WHERE tblBlogs.blog_ID = SOME (SELECT @@IDENTITY AS iBlogID);
> >>
> >> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >> WHERE tblBlogs.blog_ID = ANY (SELECT @@IDENTITY);
> >>
> >> Why is it not possible to do this?
> >>
> >> The following does work:
> >>
> >> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >> WHERE tblBlogs.blog_ID = (SELECT MAX(blog_ID) FROM tblBlogs);
> >>

> >

>

[mark4asp]

Thanks. I wanted to do this in 2 SQL calls rather than 3 but I can see
it is forlorn.

This time I decided to keep the same command object as well as
connection.

I got the same error I had last time. The record was INSERTed but the
link_ID field is not updated. I shall have to do it with 3 SQL calls
rather than 2. That is annoying because previously 2 calls were
sufficient. i.e. when using:

UPDATE tblBlogs SET link_ID = blog_ID
WHERE blog_ID = (SELECT MAX(blog_ID) FROM tblBlogs)

rather than:

UPDATE tblBlogs SET link_ID = blog_ID
WHERE blog_ID = @@IDENTITY

========== ========== ==========
Error Msg:
========== ========== ==========

Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'blog_ID =
@@IDENTITY'.
/if_blog_Local/admin/editBlog.asp, line 43

========== ========== ==========
ASP Code:
========== ========== ==========

Set conn = getAdoConn("Blog")
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = 1
sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
"VALUES('" & sTitle & "','" & sBody & "'," &_
bDisplay & "," & iAuthor & ")"
cmd.CommandText = sSQL
cmd.Execute
sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
"WHERE blog_ID = @@IDENTITY"
cmd.CommandText = sSQL
cmd.Execute
Set cmd = Nothing
KillConn conn

========== ========== ==========

The getAdoConn and KillConn functions are the same as previously.


On Sat, 13 Sep 2003 06:58:21 -0400, "Bob Barrows"
<reb_01501@yahoo.com> wrote:

>I would think you would have better success doing this* :
>
>sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
>"VALUES('" & sTitle & "','" & sBody & "'," &_
>bDisplay & "," & iAuthor & ")"
>doAdoSqlCmd conn, sSQL
>sSQL = "Select @@IDENTITY"
>set rs = conn.execute sSQL,,1
>newID = rs(0)
>rs.close
>set rs=nothing
>response.write newID 'for debugging
>sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
> "WHERE blog_ID = " & newID
>doAdoSqlCmd conn, sSQL
>KillConn conn
>
>Sub doAdoSqlCmd(conn, sqlcmd)
> Dim cmd
> Set cmd = Server.CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = conn
> cmd.CommandType = 1
> cmd.CommandText = sqlcmd
> cmd.Execute ,,128 'use 128 to specify that no records are returned
> Set cmd = Nothing
>End Sub
>
>
>
>*I'm going to continue illustrating using dynamic sql as you have done -
>however, I recommend that you switch to using saved parameter queries
>
>HTH,
>Bob Barrows
>
>mark4asp wrote:

>> It didn't work.
>>
>> UPDATE tblBlogs SET link_ID = blog_ID
>> WHERE blog_ID = @@IDENTITY
>>
>> I get this error when trying to save an Access query:
>>
>> syntax error (missing operator) in
>> query expression blog_ID = @@IDENTITY
>>
>> Note: blog_ID is an autonumber key for tblBlogs and link_ID is a Long
>> Integer field in the same table. The Access 2002 database had SQL
>> Server Compatible Syntax (ANSI 92) checked in Options.
>>
>> The INSERT statement does worked OK. So there was an @@IDENTITY
>> created in that session.
>>
>> ========== ========== ==========
>> ASP Code:
>> ========== ========== ==========
>>
>> Set conn = getAdoConn("Blog")
>> sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
>> "VALUES('" & sTitle & "','" & sBody & "'," &_
>> bDisplay & "," & iAuthor & ")"
>> doAdoSqlCmd conn, sSQL
>> sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
>> "WHERE blog_ID = @@IDENTITY"
>> doAdoSqlCmd conn, sSQL
>> KillConn conn
>>
>> Function getAdoConn(db)
>> Dim conn
>> Set conn = Server.CreateObject("ADODB.Connection")
>> conn.ConnectionTimeout = 15
>> conn.CommandTimeout = 30
>> conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
>> Server.MapPath("..\database\" & db & ".mdb;") &_
>> "User ID=Admin;Password=;"
>> Set getAdoConn = conn
>> End Function
>>
>> Sub doAdoSqlCmd(conn, sqlcmd)
>> Dim cmd
>> Set cmd = Server.CreateObject("ADODB.Command")
>> Set cmd.ActiveConnection = conn
>> cmd.CommandType = 1
>> cmd.CommandText = sqlcmd
>> cmd.Execute
>> Set cmd = Nothing
>> End Sub
>>
>> Sub KillConn(cn)
>> cn.Close
>> Set cn = Nothing
>> End Sub
>>
>> ========== ========== ==========
>> End Code.
>> ========== ========== ==========
>>
>>
>> On Fri, 12 Sep 2003 06:58:54 -0400, "Michel Walsh"
>> <vanderghast@msn.com> wrote:
>>

>>> Hi,
>>>
>>>
>>> You don't need to "select" it. If you use ADO ( not DAO, not the
>>> query designer), the following should work:
>>>
>>> CurrentProject.Connection.Execute "UPDATE tableName SET f1 = f2
>>> WHERE f3=@@IDENTITY"
>>>
>>>
>>> Note that @@IDENTITY is maintained by connection. You have to use
>>> the same connection object that you did something to "assign" the
>>> @@IDENTITY to something, that to get it back. As a trivial example,
>>> but worth of mention, @@IDENTITY of the currentProject.Connection
>>> won't be aware of the latest DAO operations.
>>>
>>>
>>>
>>> Hoping it may help,
>>> Vanderghast, Access MVP
>>>
>>>
>>> "mark4asp" <mark4asp#killspam#@ntlworld.com> wrote in message
>>> news:2lv0mvs9tq3oj9jc4u7kmb01f751h3utej@4ax.com...
>>>> Is it possible to use a SELECT @@IDENTITY statement as a sub-query
>>>> in
>>>> an UPDATE ?
>>>>
>>>> For instance I wanted to run the following query but Access would
>>>> not allow me to enter it:
>>>>
>>>> I've tried each of the following:
>>>>
>>>> UPDATE tblBlogs SET link_ID = blog_ID
>>>> WHERE blog_ID=(SELECT @@IDENTITY AS iBlogID);
>>>>
>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY);
>>>>
>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY AS iBlogID);
>>>>
>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>> WHERE tblBlogs.blog_ID = SOME (SELECT @@IDENTITY AS iBlogID);
>>>>
>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>> WHERE tblBlogs.blog_ID = ANY (SELECT @@IDENTITY);
>>>>
>>>> Why is it not possible to do this?
>>>>
>>>> The following does work:
>>>>
>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>> WHERE tblBlogs.blog_ID = (SELECT MAX(blog_ID) FROM tblBlogs);

>
>

[mark4asp]

Thanks, your version didn't work (opening the recordset that way).

I finally managed to get this to work using your doAdoSqlCmd 128
argument. I shall now attempt to try it with a transaction - just
because it can, in theory, be done.


Set conn = getAdoConn("Blog")

sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
"VALUES('" & sTitle & "','" & sBody & "'," &_
bDisplay & "," & iAuthor & ")"
doAdoSqlCmd conn, sSQL

Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = 1
cmd.CommandText = "Select @@IDENTITY"
Set rsBlogs = Server.CreateObject("ADODB.Recordset")
rsBlogs.Open cmd, ,0, 1
iBlogID = rsBlogs(0)
rsBlogs.close
Set rsBlogs = Nothing
Set cmd= Nothing

sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
"WHERE blog_ID = " & iBlogID
doAdoSqlCmd conn, sSQL

conn.close
Set conn= Nothing






On Sat, 13 Sep 2003 06:58:21 -0400, "Bob Barrows"
<reb_01501@yahoo.com> wrote:

>I would think you would have better success doing this* :
>
>sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
>"VALUES('" & sTitle & "','" & sBody & "'," &_
>bDisplay & "," & iAuthor & ")"
>doAdoSqlCmd conn, sSQL
>sSQL = "Select @@IDENTITY"
>set rs = conn.execute sSQL,,1
>newID = rs(0)
>rs.close
>set rs=nothing
>response.write newID 'for debugging
>sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
> "WHERE blog_ID = " & newID
>doAdoSqlCmd conn, sSQL
>KillConn conn
>
>Sub doAdoSqlCmd(conn, sqlcmd)
> Dim cmd
> Set cmd = Server.CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = conn
> cmd.CommandType = 1
> cmd.CommandText = sqlcmd
> cmd.Execute ,,128 'use 128 to specify that no records are returned
> Set cmd = Nothing
>End Sub
>
>
>
>*I'm going to continue illustrating using dynamic sql as you have done -
>however, I recommend that you switch to using saved parameter queries
>
>HTH,
>Bob Barrows
>
>mark4asp wrote:

>> It didn't work.
>>
>> UPDATE tblBlogs SET link_ID = blog_ID
>> WHERE blog_ID = @@IDENTITY
>>
>> I get this error when trying to save an Access query:
>>
>> syntax error (missing operator) in
>> query expression blog_ID = @@IDENTITY
>>
>> Note: blog_ID is an autonumber key for tblBlogs and link_ID is a Long
>> Integer field in the same table. The Access 2002 database had SQL
>> Server Compatible Syntax (ANSI 92) checked in Options.
>>
>> The INSERT statement does worked OK. So there was an @@IDENTITY
>> created in that session.
>>
>> ========== ========== ==========
>> ASP Code:
>> ========== ========== ==========
>>
>> Set conn = getAdoConn("Blog")
>> sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
>> "VALUES('" & sTitle & "','" & sBody & "'," &_
>> bDisplay & "," & iAuthor & ")"
>> doAdoSqlCmd conn, sSQL
>> sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
>> "WHERE blog_ID = @@IDENTITY"
>> doAdoSqlCmd conn, sSQL
>> KillConn conn
>>
>> Function getAdoConn(db)
>> Dim conn
>> Set conn = Server.CreateObject("ADODB.Connection")
>> conn.ConnectionTimeout = 15
>> conn.CommandTimeout = 30
>> conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
>> Server.MapPath("..\database\" & db & ".mdb;") &_
>> "User ID=Admin;Password=;"
>> Set getAdoConn = conn
>> End Function
>>
>> Sub doAdoSqlCmd(conn, sqlcmd)
>> Dim cmd
>> Set cmd = Server.CreateObject("ADODB.Command")
>> Set cmd.ActiveConnection = conn
>> cmd.CommandType = 1
>> cmd.CommandText = sqlcmd
>> cmd.Execute
>> Set cmd = Nothing
>> End Sub
>>
>> Sub KillConn(cn)
>> cn.Close
>> Set cn = Nothing
>> End Sub
>>
>> ========== ========== ==========
>> End Code.
>> ========== ========== ==========
>>
>>
>> On Fri, 12 Sep 2003 06:58:54 -0400, "Michel Walsh"
>> <vanderghast@msn.com> wrote:
>>

>>> Hi,
>>>
>>>
>>> You don't need to "select" it. If you use ADO ( not DAO, not the
>>> query designer), the following should work:
>>>
>>> CurrentProject.Connection.Execute "UPDATE tableName SET f1 = f2
>>> WHERE f3=@@IDENTITY"
>>>
>>>
>>> Note that @@IDENTITY is maintained by connection. You have to use
>>> the same connection object that you did something to "assign" the
>>> @@IDENTITY to something, that to get it back. As a trivial example,
>>> but worth of mention, @@IDENTITY of the currentProject.Connection
>>> won't be aware of the latest DAO operations.
>>>
>>>
>>>
>>> Hoping it may help,
>>> Vanderghast, Access MVP
>>>
>>>
>>> "mark4asp" <mark4asp#killspam#@ntlworld.com> wrote in message
>>> news:2lv0mvs9tq3oj9jc4u7kmb01f751h3utej@4ax.com...
>>>> Is it possible to use a SELECT @@IDENTITY statement as a sub-query
>>>> in
>>>> an UPDATE ?
>>>>
>>>> For instance I wanted to run the following query but Access would
>>>> not allow me to enter it:
>>>>
>>>> I've tried each of the following:
>>>>
>>>> UPDATE tblBlogs SET link_ID = blog_ID
>>>> WHERE blog_ID=(SELECT @@IDENTITY AS iBlogID);
>>>>
>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY);
>>>>
>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY AS iBlogID);
>>>>
>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>> WHERE tblBlogs.blog_ID = SOME (SELECT @@IDENTITY AS iBlogID);
>>>>
>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>> WHERE tblBlogs.blog_ID = ANY (SELECT @@IDENTITY);
>>>>
>>>> Why is it not possible to do this?
>>>>
>>>> The following does work:
>>>>
>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>> WHERE tblBlogs.blog_ID = (SELECT MAX(blog_ID) FROM tblBlogs);

>
>

[Bob Barrows]

This does not seem to work with an UPDATE statement. You are right that
@@IDENTITY can be used in the VALUES clause of an INSERT query, but when I
try it in an UPDATE statement, I get the same syntax error as the OP.

Bob

Michel Walsh wrote:

> Hi,
>
> Should be a matter of where you used it. If you used it in the query
> designer, it won't work, if you use it with DAO, it won't work. You
> have to use ADO and the same connection you used to append the data.
> The following work, in Access, in the immediate (debug) window
> (Access 2000, or more recent, Access the application, maintains a
> permanent ADO connection to the database, Jet or MS SQL Server,
> called CurrentProject.Connection) :
>
>
> ===========
> CurrentProject.Connection.Execute "CREATE TABLE demo( f1 COUNTER, f2
> varchar(50));"
> CurrentProject.Connection.Execute "INSERT INTO demo (f2) VALUES (
> 'aaa') ; " CurrentProject.Connection.Execute "INSERT INTO demo (f2)
> VALUES ( @@Identity ) ; "
>
> ? CurrentProject.COnnection.Execute("SELECT * FROM demo").GetString()
> 1 aaa
> 2 1
>
> ===========
>
>
>
> Hoping it may help,
> Vanderghast, Access MVP
>
>
>
> "mark4asp" <mark4asp#killspam#@ntlworld.com> wrote in message
> news:tkl5mvchd076fh8ud4kn3v9e3s1amgfnqf@4ax.com...

>> It didn't work.
>>
>> UPDATE tblBlogs SET link_ID = blog_ID
>> WHERE blog_ID = @@IDENTITY
>>
>> I get this error when trying to save an Access query:
>>
>> syntax error (missing operator) in
>> query expression blog_ID = @@IDENTITY
>>
>> Note: blog_ID is an autonumber key for tblBlogs and link_ID is a Long
>> Integer field in the same table. The Access 2002 database had SQL
>> Server Compatible Syntax (ANSI 92) checked in Options.
>>
>> The INSERT statement does worked OK. So there was an @@IDENTITY
>> created in that session.
>>
>> ========== ========== ==========
>> ASP Code:
>> ========== ========== ==========
>>
>> Set conn = getAdoConn("Blog")
>> sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
>> "VALUES('" & sTitle & "','" & sBody & "'," &_
>> bDisplay & "," & iAuthor & ")"
>> doAdoSqlCmd conn, sSQL
>> sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
>> "WHERE blog_ID = @@IDENTITY"
>> doAdoSqlCmd conn, sSQL
>> KillConn conn
>>
>> Function getAdoConn(db)
>> Dim conn
>> Set conn = Server.CreateObject("ADODB.Connection")
>> conn.ConnectionTimeout = 15
>> conn.CommandTimeout = 30
>> conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
>> Server.MapPath("..\database\" & db & ".mdb;") &_
>> "User ID=Admin;Password=;"
>> Set getAdoConn = conn
>> End Function
>>
>> Sub doAdoSqlCmd(conn, sqlcmd)
>> Dim cmd
>> Set cmd = Server.CreateObject("ADODB.Command")
>> Set cmd.ActiveConnection = conn
>> cmd.CommandType = 1
>> cmd.CommandText = sqlcmd
>> cmd.Execute
>> Set cmd = Nothing
>> End Sub
>>
>> Sub KillConn(cn)
>> cn.Close
>> Set cn = Nothing
>> End Sub
>>
>> ========== ========== ==========
>> End Code.
>> ========== ========== ==========
>>
>>
>> On Fri, 12 Sep 2003 06:58:54 -0400, "Michel Walsh"
>> <vanderghast@msn.com> wrote:
>>

>>> Hi,
>>>
>>>
>>> You don't need to "select" it. If you use ADO ( not DAO, not
>>> the query designer), the following should work:
>>>
>>> CurrentProject.Connection.Execute "UPDATE tableName SET f1 = f2
>>> WHERE f3=@@IDENTITY"
>>>
>>>
>>> Note that @@IDENTITY is maintained by connection. You have to
>>> use the same connection object that you did something to "assign"
>>> the @@IDENTITY to something, that to get it back. As a trivial
>>> example, but worth of mention, @@IDENTITY of the
>>> currentProject.Connection won't be aware of the latest DAO
>>> operations.
>>>
>>>
>>>
>>> Hoping it may help,
>>> Vanderghast, Access MVP
>>>
>>>
>>> "mark4asp" <mark4asp#killspam#@ntlworld.com> wrote in message
>>> news:2lv0mvs9tq3oj9jc4u7kmb01f751h3utej@4ax.com...
>>>> Is it possible to use a SELECT @@IDENTITY statement as a sub-query
>>>> in an UPDATE ?
>>>>
>>>> For instance I wanted to run the following query but Access would
>>>> not allow me to enter it:
>>>>
>>>> I've tried each of the following:
>>>>
>>>> UPDATE tblBlogs SET link_ID = blog_ID
>>>> WHERE blog_ID=(SELECT @@IDENTITY AS iBlogID);
>>>>
>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY);
>>>>
>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY AS iBlogID);
>>>>
>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>> WHERE tblBlogs.blog_ID = SOME (SELECT @@IDENTITY AS iBlogID);
>>>>
>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>> WHERE tblBlogs.blog_ID = ANY (SELECT @@IDENTITY);
>>>>
>>>> Why is it not possible to do this?
>>>>
>>>> The following does work:
>>>>
>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>> WHERE tblBlogs.blog_ID = (SELECT MAX(blog_ID) FROM tblBlogs);

[Bob Barrows]

You do realize that Max(blog_ID) may not give you the correct result in a
multi-user environment, right?

Hmm, I went to try to recreate your problem and I just noticed what you were
doing: why do you want two columns in the same table with the same data (the
blog_ID)? Very strange.

Also, why bother with a Command object? Why not just use the connection's
Execute method?

Anyways, I just tried myself: I cannot do this in less than 3 steps, either.
It looks like the only places @@IDENTITY is supported is in a standalone
select statement (it wouldn't even work with a subquery) or in an insert
statement's values clause.

Here's the code that worked for me:
dim cn,lRecs, sSQL, newID,rs
set cn=server.CreateObject("adodb.connection")

cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & _
server.MapPath("db7.mdb")
sSQL = "INSERT INTO tblBlogs(body) " &_
"VALUES('test')"
cn.Execute sSQL,lRecs,129
if lRecs > 0 then
Response.Write lRecs & " record was inserted<BR>"
set rs= cn.Execute("Select @@IDENTITY",,1)
newID = rs(0).value
rs.close
set rs=nothing
'Response.Write sSQL & "<BR>"
sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " & _
"WHERE blog_ID = " & newID
lRecs = 0
cn.Execute sSQL,lRecs,129
Response.Write lRecs & " record was updated"
end if
cn.Close
set cn=nothing

Sorry, it seems that @@IDENTITY is not as well implemented in Jet as it is
in SQL Server. I guess this is just another price to pay for using a Jet
database in this environment ...

As an alternative, you can use a recordet for the update (ugh!)

Bob Barrows


mark4asp wrote:

> Thanks. I wanted to do this in 2 SQL calls rather than 3 but I can see
> it is forlorn.
>
> This time I decided to keep the same command object as well as
> connection.
>
> I got the same error I had last time. The record was INSERTed but the
> link_ID field is not updated. I shall have to do it with 3 SQL calls
> rather than 2. That is annoying because previously 2 calls were
> sufficient. i.e. when using:
>
> UPDATE tblBlogs SET link_ID = blog_ID
> WHERE blog_ID = (SELECT MAX(blog_ID) FROM tblBlogs)
>
> rather than:
>
> UPDATE tblBlogs SET link_ID = blog_ID
> WHERE blog_ID = @@IDENTITY
>
> ========== ========== ==========
> Error Msg:
> ========== ========== ==========
>
> Microsoft JET Database Engine (0x80040E14)
> Syntax error (missing operator) in query expression 'blog_ID =
> @@IDENTITY'.
> /if_blog_Local/admin/editBlog.asp, line 43
>
> ========== ========== ==========
> ASP Code:
> ========== ========== ==========
>
> Set conn = getAdoConn("Blog")
> Set cmd = Server.CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = conn
> cmd.CommandType = 1
> sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
> "VALUES('" & sTitle & "','" & sBody & "'," &_
> bDisplay & "," & iAuthor & ")"
> cmd.CommandText = sSQL
> cmd.Execute
> sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
> "WHERE blog_ID = @@IDENTITY"
> cmd.CommandText = sSQL
> cmd.Execute
> Set cmd = Nothing
> KillConn conn
>
> ========== ========== ==========
>
> The getAdoConn and KillConn functions are the same as previously.
>

[mark4asp]

On Sat, 13 Sep 2003 15:32:08 -0400, "Bob Barrows"
<reb_01501@yahoo.com> wrote:

>You do realize that Max(blog_ID) may not give you the correct result in a
>multi-user environment, right?

Yes. That's why I must use @@IDENTITY

>Hmm, I went to try to recreate your problem and I just noticed what you were
>doing: why do you want two columns in the same table with the same data (the
>blog_ID)? Very strange.

The tblBlogs is a threaded discourse list. A parent record has Blog_ID
= Link_ID within the same record. A child element has Link_ID =
Blog_ID value of another record - which will be the parent of that
particular thread.

This is useful as it means each thread can be easily selected and the
entire table can be sorted on Blog_ID within Link_ID to give the
threads in chronological order (assuming Blog_ID to be autonumber)

>Also, why bother with a Command object? Why not just use the connection's
>Execute method?

I couldn't get the syntax right. I kept getting an error so I turned
to using something that had worked for me before.

I hate the way Microsoft have done ADO - more than one way to do the
same thing - with the result that I can't figure out what anything
does and I need to collect routines. to do these tasks. The example
code you provided didn't work (but thanks anyway).

>Anyways, I just tried myself: I cannot do this in less than 3 steps, either.
>It looks like the only places @@IDENTITY is supported is in a standalone
>select statement (it wouldn't even work with a subquery) or in an insert
>statement's values clause.

Infuriating isn't it?

I suppose I should be using MSDE as I can't afford SQL Server but it's
a question of what hosts provide for - which is why there are so many
Access and mySQL sites - not the best databases but they're more
widely available than potentially useful inexpensive databases like
firebird and postgres. I need something with good SQL as well as a
front end that my users can use too, when they may need to browse the
database off-site, perhaps during down time. They're currently using
Access - so that's what I must write in.

>Here's the code that worked for me:
>dim cn,lRecs, sSQL, newID,rs
>set cn=server.CreateObject("adodb.connection")
>
>cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & _
>server.MapPath("db7.mdb")
>sSQL = "INSERT INTO tblBlogs(body) " &_
>"VALUES('test')"
>cn.Execute sSQL,lRecs,129
>if lRecs > 0 then
> Response.Write lRecs & " record was inserted<BR>"
> set rs= cn.Execute("Select @@IDENTITY",,1)
> newID = rs(0).value
> rs.close
> set rs=nothing
> 'Response.Write sSQL & "<BR>"
> sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " & _
> "WHERE blog_ID = " & newID
> lRecs = 0
> cn.Execute sSQL,lRecs,129
> Response.Write lRecs & " record was updated"
>end if
>cn.Close
>set cn=nothing
>
>Sorry, it seems that @@IDENTITY is not as well implemented in Jet as it is
>in SQL Server. I guess this is just another price to pay for using a Jet
>database in this environment ...
>
>As an alternative, you can use a recordet for the update (ugh!)
>
>Bob Barrows
>
>
>mark4asp wrote:

>> Thanks. I wanted to do this in 2 SQL calls rather than 3 but I can see
>> it is forlorn.
>>
>> This time I decided to keep the same command object as well as
>> connection.
>>
>> I got the same error I had last time. The record was INSERTed but the
>> link_ID field is not updated. I shall have to do it with 3 SQL calls
>> rather than 2. That is annoying because previously 2 calls were
>> sufficient. i.e. when using:
>>
>> UPDATE tblBlogs SET link_ID = blog_ID
>> WHERE blog_ID = (SELECT MAX(blog_ID) FROM tblBlogs)
>>
>> rather than:
>>
>> UPDATE tblBlogs SET link_ID = blog_ID
>> WHERE blog_ID = @@IDENTITY
>>
>> ========== ========== ==========
>> Error Msg:
>> ========== ========== ==========
>>
>> Microsoft JET Database Engine (0x80040E14)
>> Syntax error (missing operator) in query expression 'blog_ID =
>> @@IDENTITY'.
>> /if_blog_Local/admin/editBlog.asp, line 43
>>
>> ========== ========== ==========
>> ASP Code:
>> ========== ========== ==========
>>
>> Set conn = getAdoConn("Blog")
>> Set cmd = Server.CreateObject("ADODB.Command")
>> Set cmd.ActiveConnection = conn
>> cmd.CommandType = 1
>> sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
>> "VALUES('" & sTitle & "','" & sBody & "'," &_
>> bDisplay & "," & iAuthor & ")"
>> cmd.CommandText = sSQL
>> cmd.Execute
>> sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
>> "WHERE blog_ID = @@IDENTITY"
>> cmd.CommandText = sSQL
>> cmd.Execute
>> Set cmd = Nothing
>> KillConn conn
>>
>> ========== ========== ==========
>>
>> The getAdoConn and KillConn functions are the same as previously.
>>

>

[Bob Barrows]

mark4asp wrote:

> On Sat, 13 Sep 2003 15:32:08 -0400, "Bob Barrows"
> <reb_01501@yahoo.com> wrote:
>
> I hate the way Microsoft have done ADO - more than one way to do the
> same thing - with the result that I can't figure out what anything
> does and I need to collect routines. to do these tasks. The example
> code you provided didn't work (but thanks anyway).

It worked fine for me. what went wrong when you tried it?

>

>> Anyways, I just tried myself: I cannot do this in less than 3 steps,
>> either. It looks like the only places @@IDENTITY is supported is in
>> a standalone select statement (it wouldn't even work with a
>> subquery) or in an insert statement's values clause.

>
> Infuriating isn't it?

Nah, not for me - I don't use Access so it's not really a problem for me ;-)

Bob

[Michel Walsh]

Hi,


Use [ ] around it seems to solve the problem (still using the previous
table demo):


=============
CurrentProject.Connection.Execute "INSERT INTO demo(f2) VALUES( 'b' ); "

CurrentProject.Connection.Execute "UPDATE demo SET f2='c' WHERE
f1=[@@IDENTITY] "

? CurrentProject.Connection.Execute("SELECT * FROM demo").GetString

1 aaa
2 1
3 c
=============


Hoping it may help,
Vanderghast, Access MVP


"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:%231xWKvieDHA.1824@TK2MSFTNGP10.phx.gbl...

> This does not seem to work with an UPDATE statement. You are right that
> @@IDENTITY can be used in the VALUES clause of an INSERT query, but when I
> try it in an UPDATE statement, I get the same syntax error as the OP.
>
> Bob
>
> Michel Walsh wrote:

> > Hi,
> >
> > Should be a matter of where you used it. If you used it in the query
> > designer, it won't work, if you use it with DAO, it won't work. You
> > have to use ADO and the same connection you used to append the data.
> > The following work, in Access, in the immediate (debug) window
> > (Access 2000, or more recent, Access the application, maintains a
> > permanent ADO connection to the database, Jet or MS SQL Server,
> > called CurrentProject.Connection) :
> >
> >
> > ===========
> > CurrentProject.Connection.Execute "CREATE TABLE demo( f1 COUNTER, f2
> > varchar(50));"
> > CurrentProject.Connection.Execute "INSERT INTO demo (f2) VALUES (
> > 'aaa') ; " CurrentProject.Connection.Execute "INSERT INTO demo (f2)
> > VALUES ( @@Identity ) ; "
> >
> > ? CurrentProject.COnnection.Execute("SELECT * FROM demo").GetString()
> > 1 aaa
> > 2 1
> >
> > ===========
> >
> >
> >
> > Hoping it may help,
> > Vanderghast, Access MVP
> >
> >
> >
> > "mark4asp" <mark4asp#killspam#@ntlworld.com> wrote in message
> > news:tkl5mvchd076fh8ud4kn3v9e3s1amgfnqf@4ax.com...

> >> It didn't work.
> >>
> >> UPDATE tblBlogs SET link_ID = blog_ID
> >> WHERE blog_ID = @@IDENTITY
> >>
> >> I get this error when trying to save an Access query:
> >>
> >> syntax error (missing operator) in
> >> query expression blog_ID = @@IDENTITY
> >>
> >> Note: blog_ID is an autonumber key for tblBlogs and link_ID is a Long
> >> Integer field in the same table. The Access 2002 database had SQL
> >> Server Compatible Syntax (ANSI 92) checked in Options.
> >>
> >> The INSERT statement does worked OK. So there was an @@IDENTITY
> >> created in that session.
> >>
> >> ========== ========== ==========
> >> ASP Code:
> >> ========== ========== ==========
> >>
> >> Set conn = getAdoConn("Blog")
> >> sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
> >> "VALUES('" & sTitle & "','" & sBody & "'," &_
> >> bDisplay & "," & iAuthor & ")"
> >> doAdoSqlCmd conn, sSQL
> >> sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
> >> "WHERE blog_ID = @@IDENTITY"
> >> doAdoSqlCmd conn, sSQL
> >> KillConn conn
> >>
> >> Function getAdoConn(db)
> >> Dim conn
> >> Set conn = Server.CreateObject("ADODB.Connection")
> >> conn.ConnectionTimeout = 15
> >> conn.CommandTimeout = 30
> >> conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
> >> Server.MapPath("..\database\" & db & ".mdb;") &_
> >> "User ID=Admin;Password=;"
> >> Set getAdoConn = conn
> >> End Function
> >>
> >> Sub doAdoSqlCmd(conn, sqlcmd)
> >> Dim cmd
> >> Set cmd = Server.CreateObject("ADODB.Command")
> >> Set cmd.ActiveConnection = conn
> >> cmd.CommandType = 1
> >> cmd.CommandText = sqlcmd
> >> cmd.Execute
> >> Set cmd = Nothing
> >> End Sub
> >>
> >> Sub KillConn(cn)
> >> cn.Close
> >> Set cn = Nothing
> >> End Sub
> >>
> >> ========== ========== ==========
> >> End Code.
> >> ========== ========== ==========
> >>
> >>
> >> On Fri, 12 Sep 2003 06:58:54 -0400, "Michel Walsh"
> >> <vanderghast@msn.com> wrote:
> >>
> >>> Hi,
> >>>
> >>>
> >>> You don't need to "select" it. If you use ADO ( not DAO, not
> >>> the query designer), the following should work:
> >>>
> >>> CurrentProject.Connection.Execute "UPDATE tableName SET f1 = f2
> >>> WHERE f3=@@IDENTITY"
> >>>
> >>>
> >>> Note that @@IDENTITY is maintained by connection. You have to
> >>> use the same connection object that you did something to "assign"
> >>> the @@IDENTITY to something, that to get it back. As a trivial
> >>> example, but worth of mention, @@IDENTITY of the
> >>> currentProject.Connection won't be aware of the latest DAO
> >>> operations.
> >>>
> >>>
> >>>
> >>> Hoping it may help,
> >>> Vanderghast, Access MVP
> >>>
> >>>
> >>> "mark4asp" <mark4asp#killspam#@ntlworld.com> wrote in message
> >>> news:2lv0mvs9tq3oj9jc4u7kmb01f751h3utej@4ax.com...
> >>>> Is it possible to use a SELECT @@IDENTITY statement as a sub-query
> >>>> in an UPDATE ?
> >>>>
> >>>> For instance I wanted to run the following query but Access would
> >>>> not allow me to enter it:
> >>>>
> >>>> I've tried each of the following:
> >>>>
> >>>> UPDATE tblBlogs SET link_ID = blog_ID
> >>>> WHERE blog_ID=(SELECT @@IDENTITY AS iBlogID);
> >>>>
> >>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >>>> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY);
> >>>>
> >>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >>>> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY AS iBlogID);
> >>>>
> >>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >>>> WHERE tblBlogs.blog_ID = SOME (SELECT @@IDENTITY AS iBlogID);
> >>>>
> >>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >>>> WHERE tblBlogs.blog_ID = ANY (SELECT @@IDENTITY);
> >>>>
> >>>> Why is it not possible to do this?
> >>>>
> >>>> The following does work:
> >>>>
> >>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >>>> WHERE tblBlogs.blog_ID = (SELECT MAX(blog_ID) FROM tblBlogs);

>
>
>

[Bob Barrows]

Ahh! I did not think of that! What made you try it?
Thanks!

Bob

Michel Walsh wrote:

> Hi,
>
>
> Use [ ] around it seems to solve the problem (still using the
> previous table demo):
>
>
> =============
> CurrentProject.Connection.Execute "INSERT INTO demo(f2) VALUES( 'b'
> ); "
>
> CurrentProject.Connection.Execute "UPDATE demo SET f2='c' WHERE
> f1=[@@IDENTITY] "
>
> ? CurrentProject.Connection.Execute("SELECT * FROM demo").GetString
>
> 1 aaa
> 2 1
> 3 c
> =============
>
>
> Hoping it may help,
> Vanderghast, Access MVP
>
>
> "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> news:%231xWKvieDHA.1824@TK2MSFTNGP10.phx.gbl...

>> This does not seem to work with an UPDATE statement. You are right
>> that @@IDENTITY can be used in the VALUES clause of an INSERT query,
>> but when I try it in an UPDATE statement, I get the same syntax
>> error as the OP.
>>
>> Bob
>>
>> Michel Walsh wrote:

>>> Hi,
>>>
>>> Should be a matter of where you used it. If you used it in the query
>>> designer, it won't work, if you use it with DAO, it won't work. You
>>> have to use ADO and the same connection you used to append the data.
>>> The following work, in Access, in the immediate (debug) window
>>> (Access 2000, or more recent, Access the application, maintains a
>>> permanent ADO connection to the database, Jet or MS SQL Server,
>>> called CurrentProject.Connection) :
>>>
>>>
>>> ===========
>>> CurrentProject.Connection.Execute "CREATE TABLE demo( f1 COUNTER, f2
>>> varchar(50));"
>>> CurrentProject.Connection.Execute "INSERT INTO demo (f2) VALUES (
>>> 'aaa') ; " CurrentProject.Connection.Execute "INSERT INTO demo (f2)
>>> VALUES ( @@Identity ) ; "
>>>
>>> ? CurrentProject.COnnection.Execute("SELECT * FROM
>>> demo").GetString() 1 aaa
>>> 2 1
>>>
>>> ===========
>>>
>>>
>>>
>>> Hoping it may help,
>>> Vanderghast, Access MVP
>>>
>>>
>>>
>>> "mark4asp" <mark4asp#killspam#@ntlworld.com> wrote in message
>>> news:tkl5mvchd076fh8ud4kn3v9e3s1amgfnqf@4ax.com...
>>>> It didn't work.
>>>>
>>>> UPDATE tblBlogs SET link_ID = blog_ID
>>>> WHERE blog_ID = @@IDENTITY
>>>>
>>>> I get this error when trying to save an Access query:
>>>>
>>>> syntax error (missing operator) in
>>>> query expression blog_ID = @@IDENTITY
>>>>
>>>> Note: blog_ID is an autonumber key for tblBlogs and link_ID is a
>>>> Long Integer field in the same table. The Access 2002 database had
>>>> SQL Server Compatible Syntax (ANSI 92) checked in Options.
>>>>
>>>> The INSERT statement does worked OK. So there was an @@IDENTITY
>>>> created in that session.
>>>>
>>>> ========== ========== ==========
>>>> ASP Code:
>>>> ========== ========== ==========
>>>>
>>>> Set conn = getAdoConn("Blog")
>>>> sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
>>>> "VALUES('" & sTitle & "','" & sBody & "'," &_
>>>> bDisplay & "," & iAuthor & ")"
>>>> doAdoSqlCmd conn, sSQL
>>>> sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
>>>> "WHERE blog_ID = @@IDENTITY"
>>>> doAdoSqlCmd conn, sSQL
>>>> KillConn conn
>>>>
>>>> Function getAdoConn(db)
>>>> Dim conn
>>>> Set conn = Server.CreateObject("ADODB.Connection")
>>>> conn.ConnectionTimeout = 15
>>>> conn.CommandTimeout = 30
>>>> conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
>>>> Server.MapPath("..\database\" & db & ".mdb;") &_
>>>> "User ID=Admin;Password=;"
>>>> Set getAdoConn = conn
>>>> End Function
>>>>
>>>> Sub doAdoSqlCmd(conn, sqlcmd)
>>>> Dim cmd
>>>> Set cmd = Server.CreateObject("ADODB.Command")
>>>> Set cmd.ActiveConnection = conn
>>>> cmd.CommandType = 1
>>>> cmd.CommandText = sqlcmd
>>>> cmd.Execute
>>>> Set cmd = Nothing
>>>> End Sub
>>>>
>>>> Sub KillConn(cn)
>>>> cn.Close
>>>> Set cn = Nothing
>>>> End Sub
>>>>
>>>> ========== ========== ==========
>>>> End Code.
>>>> ========== ========== ==========
>>>>
>>>>
>>>> On Fri, 12 Sep 2003 06:58:54 -0400, "Michel Walsh"
>>>> <vanderghast@msn.com> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>>
>>>>> You don't need to "select" it. If you use ADO ( not DAO, not
>>>>> the query designer), the following should work:
>>>>>
>>>>> CurrentProject.Connection.Execute "UPDATE tableName SET f1 = f2
>>>>> WHERE f3=@@IDENTITY"
>>>>>
>>>>>
>>>>> Note that @@IDENTITY is maintained by connection. You have to
>>>>> use the same connection object that you did something to "assign"
>>>>> the @@IDENTITY to something, that to get it back. As a trivial
>>>>> example, but worth of mention, @@IDENTITY of the
>>>>> currentProject.Connection won't be aware of the latest DAO
>>>>> operations.
>>>>>
>>>>>
>>>>>
>>>>> Hoping it may help,
>>>>> Vanderghast, Access MVP
>>>>>
>>>>>
>>>>> "mark4asp" <mark4asp#killspam#@ntlworld.com> wrote in message
>>>>> news:2lv0mvs9tq3oj9jc4u7kmb01f751h3utej@4ax.com...
>>>>>> Is it possible to use a SELECT @@IDENTITY statement as a
>>>>>> sub-query in an UPDATE ?
>>>>>>
>>>>>> For instance I wanted to run the following query but Access would
>>>>>> not allow me to enter it:
>>>>>>
>>>>>> I've tried each of the following:
>>>>>>
>>>>>> UPDATE tblBlogs SET link_ID = blog_ID
>>>>>> WHERE blog_ID=(SELECT @@IDENTITY AS iBlogID);
>>>>>>
>>>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>>>> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY);
>>>>>>
>>>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>>>> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY AS iBlogID);
>>>>>>
>>>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>>>> WHERE tblBlogs.blog_ID = SOME (SELECT @@IDENTITY AS
>>>>>> iBlogID);
>>>>>>
>>>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>>>> WHERE tblBlogs.blog_ID = ANY (SELECT @@IDENTITY);
>>>>>>
>>>>>> Why is it not possible to do this?
>>>>>>
>>>>>> The following does work:
>>>>>>
>>>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
>>>>>> WHERE tblBlogs.blog_ID = (SELECT MAX(blog_ID) FROM
>>>>>> tblBlogs);

[Michel Walsh]

Hi,


Since the variable was somehow recognized under some circumstances, I
suspect ADO, or Jet, was "trying to help too much" in the actual
circumstances... One way to stop it to do that was then to tell it "accept
it as is it, without trying to interpret it"... and the experimentation just
proved the intuition was not that bad... (if may be irrelevant, as any
intuition... )


Vanderghast, Access MVP


"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:%23q7%23zk4eDHA.1056@TK2MSFTNGP10.phx.gbl...

> Ahh! I did not think of that! What made you try it?
> Thanks!
>
> Bob
>
> Michel Walsh wrote:

> > Hi,
> >
> >
> > Use [ ] around it seems to solve the problem (still using the
> > previous table demo):
> >
> >
> > =============
> > CurrentProject.Connection.Execute "INSERT INTO demo(f2) VALUES( 'b'
> > ); "
> >
> > CurrentProject.Connection.Execute "UPDATE demo SET f2='c' WHERE
> > f1=[@@IDENTITY] "
> >
> > ? CurrentProject.Connection.Execute("SELECT * FROM demo").GetString
> >
> > 1 aaa
> > 2 1
> > 3 c
> > =============
> >
> >
> > Hoping it may help,
> > Vanderghast, Access MVP
> >
> >
> > "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> > news:%231xWKvieDHA.1824@TK2MSFTNGP10.phx.gbl...

> >> This does not seem to work with an UPDATE statement. You are right
> >> that @@IDENTITY can be used in the VALUES clause of an INSERT query,
> >> but when I try it in an UPDATE statement, I get the same syntax
> >> error as the OP.
> >>
> >> Bob
> >>
> >> Michel Walsh wrote:
> >>> Hi,
> >>>
> >>> Should be a matter of where you used it. If you used it in the query
> >>> designer, it won't work, if you use it with DAO, it won't work. You
> >>> have to use ADO and the same connection you used to append the data.
> >>> The following work, in Access, in the immediate (debug) window
> >>> (Access 2000, or more recent, Access the application, maintains a
> >>> permanent ADO connection to the database, Jet or MS SQL Server,
> >>> called CurrentProject.Connection) :
> >>>
> >>>
> >>> ===========
> >>> CurrentProject.Connection.Execute "CREATE TABLE demo( f1 COUNTER, f2
> >>> varchar(50));"
> >>> CurrentProject.Connection.Execute "INSERT INTO demo (f2) VALUES (
> >>> 'aaa') ; " CurrentProject.Connection.Execute "INSERT INTO demo (f2)
> >>> VALUES ( @@Identity ) ; "
> >>>
> >>> ? CurrentProject.COnnection.Execute("SELECT * FROM
> >>> demo").GetString() 1 aaa
> >>> 2 1
> >>>
> >>> ===========
> >>>
> >>>
> >>>
> >>> Hoping it may help,
> >>> Vanderghast, Access MVP
> >>>
> >>>
> >>>
> >>> "mark4asp" <mark4asp#killspam#@ntlworld.com> wrote in message
> >>> news:tkl5mvchd076fh8ud4kn3v9e3s1amgfnqf@4ax.com...
> >>>> It didn't work.
> >>>>
> >>>> UPDATE tblBlogs SET link_ID = blog_ID
> >>>> WHERE blog_ID = @@IDENTITY
> >>>>
> >>>> I get this error when trying to save an Access query:
> >>>>
> >>>> syntax error (missing operator) in
> >>>> query expression blog_ID = @@IDENTITY
> >>>>
> >>>> Note: blog_ID is an autonumber key for tblBlogs and link_ID is a
> >>>> Long Integer field in the same table. The Access 2002 database had
> >>>> SQL Server Compatible Syntax (ANSI 92) checked in Options.
> >>>>
> >>>> The INSERT statement does worked OK. So there was an @@IDENTITY
> >>>> created in that session.
> >>>>
> >>>> ========== ========== ==========
> >>>> ASP Code:
> >>>> ========== ========== ==========
> >>>>
> >>>> Set conn = getAdoConn("Blog")
> >>>> sSQL = "INSERT INTO tblBlogs(title, body, display, author_ID) " &_
> >>>> "VALUES('" & sTitle & "','" & sBody & "'," &_
> >>>> bDisplay & "," & iAuthor & ")"
> >>>> doAdoSqlCmd conn, sSQL
> >>>> sSQL = "UPDATE tblBlogs SET link_ID = blog_ID " &_
> >>>> "WHERE blog_ID = @@IDENTITY"
> >>>> doAdoSqlCmd conn, sSQL
> >>>> KillConn conn
> >>>>
> >>>> Function getAdoConn(db)
> >>>> Dim conn
> >>>> Set conn = Server.CreateObject("ADODB.Connection")
> >>>> conn.ConnectionTimeout = 15
> >>>> conn.CommandTimeout = 30
> >>>> conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
> >>>> Server.MapPath("..\database\" & db & ".mdb;") &_
> >>>> "User ID=Admin;Password=;"
> >>>> Set getAdoConn = conn
> >>>> End Function
> >>>>
> >>>> Sub doAdoSqlCmd(conn, sqlcmd)
> >>>> Dim cmd
> >>>> Set cmd = Server.CreateObject("ADODB.Command")
> >>>> Set cmd.ActiveConnection = conn
> >>>> cmd.CommandType = 1
> >>>> cmd.CommandText = sqlcmd
> >>>> cmd.Execute
> >>>> Set cmd = Nothing
> >>>> End Sub
> >>>>
> >>>> Sub KillConn(cn)
> >>>> cn.Close
> >>>> Set cn = Nothing
> >>>> End Sub
> >>>>
> >>>> ========== ========== ==========
> >>>> End Code.
> >>>> ========== ========== ==========
> >>>>
> >>>>
> >>>> On Fri, 12 Sep 2003 06:58:54 -0400, "Michel Walsh"
> >>>> <vanderghast@msn.com> wrote:
> >>>>
> >>>>> Hi,
> >>>>>
> >>>>>
> >>>>> You don't need to "select" it. If you use ADO ( not DAO, not
> >>>>> the query designer), the following should work:
> >>>>>
> >>>>> CurrentProject.Connection.Execute "UPDATE tableName SET f1 = f2
> >>>>> WHERE f3=@@IDENTITY"
> >>>>>
> >>>>>
> >>>>> Note that @@IDENTITY is maintained by connection. You have to
> >>>>> use the same connection object that you did something to "assign"
> >>>>> the @@IDENTITY to something, that to get it back. As a trivial
> >>>>> example, but worth of mention, @@IDENTITY of the
> >>>>> currentProject.Connection won't be aware of the latest DAO
> >>>>> operations.
> >>>>>
> >>>>>
> >>>>>
> >>>>> Hoping it may help,
> >>>>> Vanderghast, Access MVP
> >>>>>
> >>>>>
> >>>>> "mark4asp" <mark4asp#killspam#@ntlworld.com> wrote in message
> >>>>> news:2lv0mvs9tq3oj9jc4u7kmb01f751h3utej@4ax.com...
> >>>>>> Is it possible to use a SELECT @@IDENTITY statement as a
> >>>>>> sub-query in an UPDATE ?
> >>>>>>
> >>>>>> For instance I wanted to run the following query but Access would
> >>>>>> not allow me to enter it:
> >>>>>>
> >>>>>> I've tried each of the following:
> >>>>>>
> >>>>>> UPDATE tblBlogs SET link_ID = blog_ID
> >>>>>> WHERE blog_ID=(SELECT @@IDENTITY AS iBlogID);
> >>>>>>
> >>>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >>>>>> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY);
> >>>>>>
> >>>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >>>>>> WHERE tblBlogs.blog_ID = (SELECT @@IDENTITY AS iBlogID);
> >>>>>>
> >>>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >>>>>> WHERE tblBlogs.blog_ID = SOME (SELECT @@IDENTITY AS
> >>>>>> iBlogID);
> >>>>>>
> >>>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >>>>>> WHERE tblBlogs.blog_ID = ANY (SELECT @@IDENTITY);
> >>>>>>
> >>>>>> Why is it not possible to do this?
> >>>>>>
> >>>>>> The following does work:
> >>>>>>
> >>>>>> UPDATE tblBlogs SET tblBlogs.link_ID = tblBlogs.blog_ID
> >>>>>> WHERE tblBlogs.blog_ID = (SELECT MAX(blog_ID) FROM
> >>>>>> tblBlogs);

>
>

[Chris Hohmann]

"Michel Walsh" <vanderghast@msn.com> wrote in message
news:uK7Z6f4eDHA.620@TK2MSFTNGP11.phx.gbl...

> Hi,
>
>
> Use [ ] around it seems to solve the problem (still using the

previous

> table demo):

In a word ... awesome!

Here's a little trivia. Did you know that Access97 had undocumented
support for subqueries?

SELECT
FOO
FROM
[
SELECT TOP 10
FOO
FROM
BAR
ORDER BY FOO DESC
]. AS LastTen
ORDER BY FOO

For the OP, here's a thought. When dealing with hierarchical structures,
I like to create a "root" node. This way I can set the default value of
ParentID to NodeID of the root. With this construct in place, you would
only need one statement to insert a new node. No multiple-statements, no
transactions, no muss, no fuss. Finally, you may want to consider a
"caterpillar walk" for tree structures. Do a google search on "Joe
Celko" and "Trees". Very enlightening stuff.

HTH
-Chris