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.
In Microsoft Access such a field uses an “AutoNumber” data type. In MySQL it’s known as an “auto_increment” field.
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.
It’s a convenient way to have the database itself automatically create a unique identifier.
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.
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:
DBExec>dbID,INSERT into customers (name,address1,address2) VALUES ("Mr Customer","..",".."),res
DBQuery>dbID,SELECT LAST_INSERT_ID(),last_id,nr,nf,0
This will return the ID in LAST_ID_1_1 and you can then use it in your next INSERT for the detail records.
Microsoft Access does the same with a function called @@identity:
DBExec>dbID,INSERT into customers (name,address1,address2) VALUES ("Mr Customer","..",".."),res
DBQuery>dbID,SELECT @@identity,last_id,nr,nf,0
See: Retrieving Identity or Autonumber Values
Your particular flavour of database might have something similar. If not you could take a risk and do this:
DBQuery>mdbH,SELECT TOP 1 ID FROM customers ORDER BY ID DESC,last_id,nr,nf,0
This will return the largest ID – the latest one. But if you’re working on a database that has many users updating it simultaneously there’s a danger you could retrieve the ID generated by someone else’s INSERT. If it’s just you, then it’s probably OK, but you’ve been warned.
For more information on Macro Scheduler’s database functions see:
Using Macro Scheduler’s Database Functions
