Accessing Access mdb via ODBC - how to return autonumber

[Steven Taylor]

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).

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?

Thanks in advance.

Steven Taylor
Melbourne, Australia.

[rkc]

Steven Taylor wrote:

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).

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?

Your best option is to retrieve the record using whatever real fields
make the record unique. That should never be a counter.

[rkc]

Steven Taylor wrote:

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).

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?

Your best option is to retrieve the record using whatever real fields
make the record unique. That should never be a counter.

[Saintor]

And why not? I use the dmax() function inserted in nz()

=nz(dmax(...))+ 1

I have considered "SELECT Max(CounterFiel d) FROM ....". By using ODBC
is this my only option in retrieving the counter field value?

Thanks in advance.

Steven Taylor
Melbourne, Australia.

[Saintor]

And why not? I use the dmax() function inserted in nz()

=nz(dmax(...))+ 1

I have considered "SELECT Max(CounterFiel d) FROM ....". By using ODBC
is this my only option in retrieving the counter field value?

Thanks in advance.

Steven Taylor
Melbourne, Australia.

[Loring D via AccessMonster.com]

You can use the Select Mas--------------- query. Put text field on the form
or sub form showing the Max Count. This "lets call it the total Textbox", wil
always show a total count. After updating from insert statement. Refresh the
textbox. This will show the new count. Hope this useful.

Doug
lo******@sbcglo bal.net

Steven Taylor wrote:

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).

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?

Thanks in advance.

Steven Taylor
Melbourne, Australia.

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200507/1

[Loring D via AccessMonster.com]

You can use the Select Mas--------------- query. Put text field on the form
or sub form showing the Max Count. This "lets call it the total Textbox", wil
always show a total count. After updating from insert statement. Refresh the
textbox. This will show the new count. Hope this useful.

Doug
lo******@sbcglo bal.net

Steven Taylor wrote:

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).

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?

Thanks in advance.

Steven Taylor
Melbourne, Australia.

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200507/1

[Steven Taylor]

>>

I have considered "SELECT Max(CounterFiel d) FROM ....". By using
ODBC is this my only option in retrieving the counter field value?

Your best option is to retrieve the record using whatever real fields
make the record unique. That should never be a counter.

Unfortunately this is not possible at the moment as I'm dealing with an
existing system.

Could you elaborate on your comment regarding "Unique field, should
never be a counter".

Steve.

[David W. Fenton]

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

[lylefair]

Assuming you are using the latest DAO-JET have you tried something
like:

Sub temp()
Dim rst As DAO.Recordset
With DBEngine(0)(0)
.Execute "INSERT INTO TABLE1 ([VALUE], IDS) VALUES ('c', 5)"
Set rst = .OpenRecordset( "SELECT @@IDENTITY")
End With
Debug.Print rst.Collect(0)
' shows 7 the last auto number of the last table updated
Set rst = Nothing
End Sub

DAO/JET has lotsa new stuff; its champions don't seem to mention this
new stuff. Is there something wrong with it? OHHHHHHHH ... maybe it
can't be used in ODBC? I never use ODBC so I don't know. (Maybe
!!!!!!NEW!!!!!! is bad; "We don't want people like YOU around here,
Mister; better ride on outta here").

Well if ODBC won't do the job I suppose you could use ADO on top of
OLEDB. It's soooooooooooooo complicated.

With CurrentProject. Connection
.Execute "INSERT INTO TABLE1 ([VALUE], IDS) VALUES ('c', 5)"
Debug.Print .Execute("SELEC T @@IDENTITY").Co llect(0)
' shows 8 the last auto number of the last table updated
End With

I suppose you will have to create your own connection if you are
working in ODBC. This is at least 30 seconds work.

Well, now I see why those MS insiders recommend against ADO. You can
SEE how much more miserable the ADO is. No setting object pointers.
Cleans up its own garbage ... eek it's awful. And I bet it's only
99.973 % as fast. WAIT ... against ODBC ... who knows ... maybe 99.974?

No REALLY ... it's JUST "SELECT @@IDENTITY" ... no table name required.

Yes I am old. No I am not confusing JET with MS-SQL. Yes, someone woke
me up. No, I can't go back to sleep.