sql

[rochak]

Hi,

I am doing an insert onto a table in access. I am inserting four fields while the fifth field which is an ID is auto generated. The sql statement is

sql = "INSERT INTO LogTable(Username,[Date],[Time],EditFields) VALUES('" & struser & "','" & Date & "','" & Time & "','New Mailbox " & newmailname & " created');"

Is there a way for me to store the value of the autogenerated id where this insert is being performed, since I need this id value later on.

Thanks

[Mark Gambo]

You will have to re-query in order to get the Autonumber because the autonumber is not created until the data has been inserted (or appended).

You should consider creating a function that will query the table to get the last ID number used and then increment it. THen you will know what the ID number is before you insert it. One additional item to note after you change the column from an auto number into a number field type make sure you change the index so that duplicat numbers are not allowed, so that a duplicate ID number is not entered.

[RobDog888]

From the help file...

To find the last value that was used for an auto-increment column, you can use the following statement: SELECT @@IDENTITY. You cannot specify a table name. The value returned is from the last table, containing an auto-increment column, that was updated.

[Mark Gambo]

I am doing an insert onto a table in access . . .

RobDog888,

Will @@IDENTITY work with an Access DB I thought it only worked with MS SQL, MySQL et. al.

[rochak]

Can I use Dmax(field,table) since the last auto generated id would be the maximum?

[Mark Gambo]

Can I use Dmax(field,table) since the last auto generated id would be the maximum?

Yes you can.

[RobDog888]

RobDog888,

Will @@IDENTITY work with an Access DB I thought it only worked with MS SQL, MySQL et. al.

Yes its available in Access but which version are you running? The quote is directly from the help file.

[Mark Gambo]

Yes its available in Access but which version are you running? The quote is directly from the help file.

Weel they same you learn something new everyday. I wasn't aware of that, but I guess it is more of an ADO function rather that a DB specific function.

[RobDog888]

I think its more of an DB function rather then a ADO function since it is more of a T-SQL92 standard SQL function if I'm not mistaken. So it depends more on the db supporting the SQL standards.