If you have a database table with an auto-generated numerical index field, you may need to retrieve the value of this field after an INSERT statement.<\/p>\n
In Microsoft Access such a field uses an “AutoNumber” data type. In MySQL it’s known as an “auto_increment” field.<\/p>\n
We often see these types of fields where a relationship exists between two tables, for example between a “customers” table and “customer_orders” table. The “customers” table may have an AutoNumber field which the system automatically increments when a record is inserted, and in the “customer_orders” table you’ll see a “CustomerID” field which uses this same value to map one or more orders to one customer.<\/p>\n
It’s a convenient way to have the database itself automatically create a unique identifier.<\/p>\n
If you ever need to use Macro Scheduler to transfer data into such a database you can use the built-in database functions to handle the INSERTS. But when it comes to inserting the data into the second table you’re going to need a way to get the ID that was automatically generated for the first.<\/p>\n
Some database technologies give you a function to do just that. MySQL has a function called LAST_INSERT_ID() which you can call with a SELECT statement:<\/p>\n
DBExec>dbID,INSERT into customers (name,address1,address2) VALUES (\"Mr Customer\",\"..\",\"..\"),res \r\nDBQuery>dbID,SELECT LAST_INSERT_ID(),last_id,nr,nf,0<\/pre>\nThis will return the ID in LAST_ID_1_1 and you can then use it in your next INSERT for the detail records.<\/p>\n
See: MySQL: last_insert_id<\/a><\/p>\n
Microsoft Access does the same with a function called @@identity:<\/p>\n
DBExec>dbID,INSERT into customers (name,address1,address2) VALUES (\"Mr Customer\",\"..\",\"..\"),res \r\nDBQuery>dbID,SELECT @@identity,last_id,nr,nf,0<\/pre>\n