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:

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

A counter, usually refered to as an autonumber when using Access/Jet,
is used to uniquely identify a record. It should not be used to make
the data in that record unique. That should come from a combination
of values in fields (not generated by the db engine) that is only ever
allowed to occur once in a table. That is the heart and soul of a
relational database.

I'll have to disagree with you on this one. You even mention "A
counter.....is used to uniqely identify a record". Without the use of
a single field (primary key) to identify a record, creating a 'many to
many' type relationship would be a nightmare. The joining table
(linking table) rather than having a minimun two foreign key fields
would require as many combined fields from each of the primary tables.

I'm not suggesting though, that every table must have a counter as a
primary key, just that it makes it so much simpler to work with if one
is present, if it's going to be related to.

I suppose it comes down to personal preference.

Steve.

A counter is a pointer. It points to a single record. This makes
them useful as a 'key'. The use of a counter does not make the
information represented by a record unique. That, as you obviously
realise, sometimes takes a combination of other fields in the record.

My suggestion to your op was that if you just inserted a record
in a table then you know the values you just used to do it. You
can use the fields that make the record unique to retrieve the
counter assigned to that record by the db engine. The fact that
designers of many db engines provide short cuts for that purpose
is a bonus, I guess.

[rkc]

Trevor Best wrote:
<snip>

I agree.

In one of my databases, a hierarchy goes like this (usually 1:n)

Project->Order->OrderItem->ExpeditingComp onentSplit->DelSplit->MMTItem[1]->MRRItem[2]
Can you imagine the PK on MRRItem if I didn't use a counter?
ProjectNo + OrderNo + OrderItemNo + CompSplit + DelSplit + MMTNo + MRRNo

The point is that a unique combination of information that makes up a
MRRItem does exists in each MRRItem. The counter does not make each
MRRItem unique.

[Steven Taylor]

rkc wrote:
snip

My suggestion to your op was that if you just inserted a record
in a table then you know the values you just used to do it. You
can use the fields that make the record unique to retrieve the
counter assigned to that record by the db engine. The fact that
designers of many db engines provide short cuts for that purpose
is a bonus, I guess.

Yes, I agree.

Steve.

[David W. Fenton]

Steven Taylor <ne******@super jacent.net> wrote in
news:42******** *************** @news.optusnet. com.au:

[rkc:]

A counter, usually refered to as an autonumber when using
Access/Jet, is used to uniquely identify a record. It should not
be used to make the data in that record unique. That should come
from a combination of values in fields (not generated by the db
engine) that is only ever allowed to occur once in a table. That
is the heart and soul of a relational database.

I'll have to disagree with you on this one. . ..

I think you're not fully grasping rkc's point (you really ought to
work harder to maintain attributions, too).

Say you've got a table of companies. If you use an AutoNumber as a
PK, but don't put a unique index on company name, then you can end
up with identical companies.

That's all rkc is saying, that if you use an Autonumber as a
surrogate key, you *still* have to enforce uniqueness in the other
fields, insofar as it is physically possible. This usually means
that you have a unique index on the candidate natural primary key
that the surrogate key replaces for ease of use.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

rkc <rk*@rochester. yabba.dabba.do. rr.bomb> wrote in
news:4L******** ***********@twi ster.nyroc.rr.c om:

rude person wrote:

On Tue, 19 Jul 2005 08:59:48 +0100, Trevor Best
<no****@besty.o rg.uk> wrote:

Steven Taylor wrote:

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

I agree.

etc

I think the point being made isn't "don't use a counter", it is
that two records shouldn't be exactly the same apart from the
counter

Yes. Exactly that.

Well, except for one point:

You don't mean that the records shouldn't be identical, but that two
records should not refer to a single real-world entity.

With incomplete data, you may end up with two records that look
identical because they are not complete, but actually really do
refer to two different entities. The problem with those is
distinguishing them while you are waiting to get real data.

But you really do sometimes have identical records as a temporary
condition caused by real-world incompleteness.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[rkc]

David W. Fenton wrote:

rkc <rk*@rochester. yabba.dabba.do. rr.bomb> wrote in
news:4L******** ***********@twi ster.nyroc.rr.c om:

rude person wrote:

I think the point being made isn't "don't use a counter", it is
that two records shouldn't be exactly the same apart from the
counter

Yes. Exactly that.

Well, except for one point:

You don't mean that the records shouldn't be identical, but that two
records should not refer to a single real-world entity. With incomplete data, you may end up with two records that look
identical because they are not complete, but actually really do
refer to two different entities. The problem with those is
distinguishing them while you are waiting to get real data.

But you really do sometimes have identical records as a temporary
condition caused by real-world incompleteness.

The values in the fields that make up the selected real primary key
should never occur more than once in the same combination in the same
table. The design of the table should be such that the db engine
does not allow it. How else can uniqueness be enforced without a doubt?

[rude person]

On Tue, 19 Jul 2005 14:09:44 -0500, "David W. Fenton" <dX********@bwa y.net.invalid> wrote:

....

With incomplete data, you may end up with two records that look
identical because they are not complete, but actually really do
refer to two different entities. The problem with those is
distinguishi ng them while you are waiting to get real data.

Theory and practice sometimes diverge.
I found that if I set a field as "required", users would sometimes enter a valid
but bogus entry and make a mental note to correct it later, rather than have to re-enter
a long record when they knew all the values. It is safer to accept incomplete records
and detect them by program.

[Steven Taylor]

David W. Fenton wrote:
snip

I think you're not fully grasping rkc's point (you really ought to
work harder to maintain attributions, too).

I don't follow you re - the above. I'm not sure what you mean by "work
harder to....."

I'm not, and haven't, suggested that other fields can't be defined as
unique or combined to be unique. Merely that a counter number assists
with easier reference to the table.

Steve.

[lylefair]

> Where to you find out about these little additions to Jet 4?

They seem to be hidden. I go to Help -> Microsoft Jet SQL reference ->
Overview -> SQL Reserved Words. It seems if MS decided to reserve a
word then they are using the word. If I'm not familiar with its use, it
may be new. When I click on it, it often, but not always, tells me.
For @@Identity, the link takes one to SQL data-types. When I got there,
I saw nothing about @@Identity. But second section from the bottom
describes its use.

I really wanted to use A2K when it came out (in large part because
of the vast improvements in Jet 4 replication), but there were so
many problems with A2K as a development platform that it wasn't
worth it.

I did not agree with you. In retrospect I can't say which of us was
right. Access 2K and especially ADPs (not ADO) brought me to lot of
late night, "What am I gonna do NOW?" situations, because there were
many errors in these technologies, and also because the errors seemed
to be random, unpredictable and erratic; so a solution for Checkbox A,
is not necessarily a solution for Checkbox B, even though they seem to
be identical in use, calling, etc.

[Trevor Best]

ly******@yahoo. ca wrote:

Where to you find out about these little additions to Jet 4?

They seem to be hidden. I go to Help -> Microsoft Jet SQL reference ->
Overview -> SQL Reserved Words. It seems if MS decided to reserve a
word then they are using the word. If I'm not familiar with its use, it
may be new. When I click on it, it often, but not always, tells me.
For @@Identity, the link takes one to SQL data-types. When I got there,
I saw nothing about @@Identity. But second section from the bottom
describes its use.

I'll just add that while that is fine for Jet, if you have a SQL Server
(or MSDE) back end then you'd be better off using SCOPE_IDENTITY( ) as
the @@IDENTITY global var can be influenced by triggers that do inserts.

I'll also add (as I saw it being asked elsewhere in the thread) that in
SQL Server, @@IDENTITY is confined to the connection you're using so
multi-user issues shouldn't be an issue (i.e. you will *not* pick up
someone else's autonumber column value). I assume the same is true for
Jet, I can't imagine it being any different.

--
[OO=00=OO]