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(CounterField) 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(CounterField) 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(CounterField) 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(CounterField) 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(CounterField) 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******@sbcglobal.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(CounterField) 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.com
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******@sbcglobal.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(CounterField) 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.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1

[Steven Taylor]

>>

I have considered "SELECT Max(CounterField) 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******@superjacent.net> wrote in
news:42***********************@news.optusnet.com.a u:

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(CounterField) 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("SELECT @@IDENTITY").Collect(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.

[Steven Taylor]

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

Thanks for that, just tried "SELECT @IDENTITY" and it worked. I now
have to get my head around why it worked, if one is not specifying a
table name. I'm assuming that after every "INSERT INTO...." the very
next line is "SELECT @@IDENTITY". Are there any multi-user issues
here? Access help really doesn't help and the only reference to
@@IDENTITY is at 'SQL Data Types' help page and that is very sparse.

Steve.

[Steven Taylor]

Thanks David,

I've inserted some comments inline

David W. Fenton wrote:

Steven Taylor <ne******@superjacent.net> wrote in
news:42***********************@news.optusnet.com.a u:

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.

I am comfortable with DAO from within Access. Outside of Access,
Realbasic for instance, I'm not too sure. I am now showing some of my
ignorance here, but what the heck, here goes. Realbasic can access
ActiveX objects, does that mean then that ultimately DAO is available to
me. I know Realbasic hooks into (or can use) ADO but I haven't
trialled it or used it. Even in Access I don't use ADO because I'm so
familiar with DAO.
snip
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 like this idea, it makes sense.
Steve.

[David W. Fenton]

Steven Taylor <ne******@superjacent.net> wrote in
news:42***********************@news.optusnet.com.a u:

David W. Fenton wrote:

Steven Taylor <ne******@superjacent.net> wrote in
news:42***********************@news.optusnet.com.a u:

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.

I am comfortable with DAO from within Access. Outside of Access,
Realbasic for instance, I'm not too sure. I am now showing some
of my ignorance here, but what the heck, here goes. Realbasic
can access ActiveX objects, does that mean then that ultimately
DAO is available to me. I know Realbasic hooks into (or can use)
ADO but I haven't trialled it or used it. Even in Access I don't
use ADO because I'm so familiar with DAO.

I don't know what RealBasic can and can't do. The methods for using
DAO ought to be pretty similar to setting up your RealBasic app for
using ADO, but that's just speculation on my part.

"Can access ActiveX objects" may mean "can use COM," and if so, then
DAO ought to work, too.

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 like this idea, it makes sense.

It's a kludge and I would do it the other way if you can.

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

[David W. Fenton]

"ly******@yahoo.ca" <ly******@yahoo.ca> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

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
Well, that's very cool. Jet 4 really was a major upgrade in many
ways, and it seems to me that a lot of those advances got lost in
the ADO folderol and in all the problems with the very unstable
initial releases of Jet 4 (6 service packs before it was stable).
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("SELECT @@IDENTITY").Collect(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.

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

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 now support clients on A97, A2K and A2K3 (I had a one-time client
with A2K2), and don't really program much in A2K because it feels
like working with one hand tied behind my back. Perhaps if I worked
in A2K all the time, I'd have discovered more of the new features of
Jet 4, but the price in programming productivity and instability
seems to me to not at all be worth the tradeoff.

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

[David W. Fenton]

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

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

Thanks for that, just tried "SELECT @IDENTITY" and it worked. I
now have to get my head around why it worked, if one is not
specifying a table name. I'm assuming that after every "INSERT
INTO...." the very next line is "SELECT @@IDENTITY". Are there
any multi-user issues here? Access help really doesn't help and
the only reference to @@IDENTITY is at 'SQL Data Types' help page
and that is very sparse.

It works for the same reason .RecordsAffected works in the same
conditions -- it's a property of the database object (in DAO)
/connection (in ADO) you've just used. So, no, there shouldn't be
any multi=user issues, unless you have multiple forms adding records
in your front end using a single DAO database variable or the ADO
default connection. Since DAO database objects and ADO connections
cannot be shared between users, there are no multi-user issues.

Before Jet 4, the identity value was not available, though that was
commonly available when using ODBC with server database back ends.
It's a very useful thing to have now in Jet 4.

Too bad so few of my clients use Jet 4 back ends.

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

[rkc]

Steven Taylor wrote:

I have considered "SELECT Max(CounterField) 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".

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.

[Steven Taylor]

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.

[Trevor Best]

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.

I agree.

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

Project->Order->OrderItem->ExpeditingComponentSplit->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 items in stock have an audit trail to issue and installation,
Material Control would be an absolute nightmare.

[1] MMT = Material Movement Ticket
[2] MRR = Material Receiving Report
--
[OO=00=OO]

[rude person]

On Tue, 19 Jul 2005 08:59:48 +0100, Trevor Best <no****@besty.org.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

[rkc]

rude person wrote:

On Tue, 19 Jul 2005 08:59:48 +0100, Trevor Best <no****@besty.org.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.

[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->ExpeditingComponentSplit->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******@superjacent.net> wrote in
news:42***********************@news.optusnet.com.a u:

[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*******************@twister.nyroc.rr.com:

rude person wrote:

On Tue, 19 Jul 2005 08:59:48 +0100, Trevor Best
<no****@besty.org.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*******************@twister.nyroc.rr.com:

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********@bway.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
distinguishing 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]

[Trevor Best]

rude person wrote:

Theory and practice sometimes diverge.

In theory, theory and practice are the same, in practice however, they
are not :-)

(sorry, couldn't resist, saw that on someone's sig, can't remember if it
was here or not)

--
[OO=00=OO]

[David W. Fenton]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:Xp*******************@twister.nyroc.rr.com:

David W. Fenton wrote:

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?

Then your schema is going to force users to enter fake data.

To me, that's vastly worse than having incomplete data.

And it means that you *can't* enforce uniqueness except in code for
certain kinds of data where there are going to be unknowns in the
real-world data entry process.

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

[David W. Fenton]

not@all (rude person) wrote in news:42ddf866.83209729@localhost:

On Tue, 19 Jul 2005 14:09:44 -0500, "David W. Fenton"
<dX********@bway.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
distinguishing 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.

I agree 100%.

Entry of fake data is a far worse problem than correct but
incomplete records.

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

[David W. Fenton]

Steven Taylor <ne******@superjacent.net> wrote in
news:42***********************@news.optusnet.com.a u:

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.

You still aren't agreeing with rkc, who says some other field (or
combination of fields) *must* be defined as unique.

You are simply saying this *may* be the case, not that it is a
requirement, as rkc is saying.

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