Unique fields in Access 2007

MySQL has an awesome uniqueness setup with these statements:

... on duplicate key update id=last_insert_id()...

... field2 char(100) not null unique...

insert into tbl (col1,col2,col3) values (
  (select id from tbl2 where f2='x'),
  (select id from tbl3 where f3='y'),
  (select id from tbl4 where f4='z'));

Using AutoIt, the Access statements would be

... field2 char(100) constraint field2u unique...

$query = "select id from tbl2 where f2='x';"
$rs.Open($query, $conn)
$values = $values & "," & $rs.Fields("id").Value
$rs.Close()
; repeat for other two select statements
; insert into...

For small apps, it’s not bad.

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
http://msdn.microsoft.com/en-us/library/office/bb177889(v=office.12).aspx

Advertisements