Steven Taylor <ne******@super jacent.net> wrote in
news:42******** *************** @news.optusnet. com.au:
Hope someone can help. This is half an Access question. The
half I'm using is Access Xp as a backend data file. I'm using
ODBC to connect to the data file. All commands are via SQL type
statements (SELECT, UPDATE & INSERT INTO). (I'm using/trialling
Realbasic as a frontend).
Can Realbasic use DAO? If it can, then you can use your data via
Jet, and then use a DAO append-only recordset to insert the record,
read the new Autonumber value, then use that to retrieve the
finished record.
After inserting a record (row) into a table using "INSERT INTO
...." what is the easiest way to obtain the value of a counter
field for the record just inserted?
I have considered "SELECT Max(CounterFiel d) FROM ....". By using
ODBC is this my only option in retrieving the counter field value?
If you can't do it with DAO, and there are no ODBC methods that work
the same way (i.e., returning a recordset and inserting the new
record in the recordset, rather than through a SQL INSERT), then you
are forced to do something like having a field in the table
dedicated to holding data to uniquely identify the record. You'd
insert your record with this unique value, then immediately retrieve
the new record based on that one field, grab the Autonumber value
and then set the field you'd used to indentify the inserted record
to Null.
I've done it in the past with batch inserts using a temp table. I
reserved the value -1000000 as the value for a particular non-unique
field, and made that the default value. When I inserted a batch of
records, I could then retrieve them by that value in that field.
When I'd processed them, I'd clear the field to Null.
It worked pretty well because it wasn't a multi-user application.
You could do the same thing in a multi-user application by using a
user-stamp in the record in conjunction with the dedicated field
that indicates it's a new record.
All of those are kludges for use when you don't have the capability
to do a cursor-based record add (i.e., in a recordset).
--
David W. Fenton
http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc