Very Computer

1. ODBC, Serial Fields (aka "Counter Fields"), and Insert

We are in the process of trying to use ODBC in our application, and I seem
to have hit a wall.

We are storing data in a master/detail relationship, joined by the primary
key of the master table.  The "natural" primary key to use is a serial
field (known as a "counter field" in Microsoft Access), that is, a field
who's value is automatically generated whenever an insert is done.  Following
this line, the obvious way to insert a master record and three linked detail
records is this:

        insert into master (a, b, c) values ("a", "b", "c");
        long id = the value of the serial field for the row just inserted;
        insert into detail (master_id, val) values ($id, "value1");
        insert into detail (master_id, val) values ($id, "value2");
        insert into detail (master_id, val) values ($id, "value3");

The problem is the (pseudo-)statement "the value of the serial field for
the row just inserted".  Our backend database is Microsoft Access, and it
does have what it calls "counter fields", but I can't find anyway to get
the value of the counter field last inserted.  Microsoft Tech Support's
first suggestion was:

        select max(id) from master;     // master.id is a counter field

which doesn't work for obvious reasons in a multiuser environment.  Their
only other suggestion was to have some other table where I have a
"next id value" field that I increment...  This would work, but it is
unnecessarily slow, especially should we go to a client/server database
someday (retrieving the next id value, followed by an update, followed by
the insert is at least three server roundtrips instead of one).

I can think of a few other workarounds, like coming up with some sort of
random value for the master.id column, trying the insert, and re-trying
if it fails because of a duplicate primary key value.  Assuming I could
get a decent enough random number generator, this might be OK, but it
_is_ pretty kludgey.

I have trouble believing that Microsoft could get something this basic
so wrong in ODBC.  It's mind boggling that they'd forget to have some
way of getting the value of the primary key of the last row inserted
in this situation.  I hope that they didn't, and that the only problem
is that both I and the Microsoft Tech Support guy didn't RTFM closely enough.

So...  Am I wrong?  Is there really no way to have an automatically assigned
primary key value that's usable for a master/detail relationship through
ODBC?  Say it ain't so!  :-)

--
Bill Foote                   | Oh, and I suppose *you'd* prefer a little

Medicus Systems              | little file cabinet and then wink at you and
Alameda, CA USA              | point to the right drawer    -- Crow T. Robot

2. US-DC: Oracle Production DBA

3. How to make a "decimal"-field to an "integer"-field

4. ADO: Convert Access 97 -> 2000

5. aReport."Field".SetFilter("this")?

6. Reporting tool for Oracle 8i

7. max of ("...","...","..")

8. Using DataCombo

9. need to automatically update "lastModified" field

10. Problem with "counter" primary key

11. Help: On "flushing" field in APT

12. HELP: Checkbox field "Focus"

13. HELP - "MEMO field missing/invalid"