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.

[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******@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.

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******@super jacent.net> wrote in
news:42******** *************** @news.optusnet. com.au:

David W. Fenton wrote:

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.

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.goo glegroups.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("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.

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******@super jacent.net> wrote in
news:42******** **************@ news.optusnet.c om.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 .RecordsAffecte d 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(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".

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

[rkc]

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.