The problem isn't so much that the SQL doesn't work, it's more of a problem of how the query requests are made. I have experienced this same problem. When you make two explicit .Execute calls to a database (even thru the same open connection) you might not get what you expect from @@IDENTITY.
Don't forget that you can run multiple SQL statements back to back with the ; between them. So what you can do is put that identity select immediately after the insert statement. Seeing as the insert doesn't select anything anyway, this gets you two things: a litter more speed by not having to use two explicit .Execute calls, and ensures that you don't have to resort to using workarounds like SELECT MAX. Here's what you do:
sSql = "INSERT .... ; SELECT @@IDENTITY;"
Set rs = Conn.Execute(sSql)
Ticket_ID = rs(0)
Peter
------------------------------------------------------
Work smarter, not harder.
|