Where is the record I just inserted?

[Siegfried Heintze]

I'm using Access 2003 with the perl DBI interface.

Perform the following steps:
(1) Search for a record using "SELECT ... WHERE...".
(2) If I cannot find it, I start a transaction and then I use "INSERT ..."
followed by "SELECT MAX(id) ...". I commit the transaction.
(3) I perform the exact same select statement as in step 1 and it sometimes
fails to find the record I inserted. The insert is successful because I can
see it.

The fields I specify in the WHERE clause are a subset of the fields I use in
the VALUES clause in the INSERT statement.

The fields are specify in the WHERE clause are strings and a single
hyperlink.

Why is step 3 sometimes failing? How do I fix it so I can consistently find
the record I insert?

Thanks,
Siegfried

 

[Siegfried Heintze]

Problem solved by increasing the width of a field in MS Access.

I wish someone, either perl DBI or MS Access would give me an error when my
field is too small.

Thanks,
Siegfried

 

[Tim Ferguson]

I wish someone, either perl DBI or MS Access would give me an error
when my field is too small.

.... I don't think the field is to small; the contents are too big. The
field size will have been dictated by the systems analysis, data flow,
entity life-history, domain definition etc.

PS: why use a transaction to atomise a single INSERT?

PPS: what library are you using to carry out the insert? The
DAO.Database.Execute method has an Options parameter that allows it to
throw a trappable error if there is a failure in the SQL statement.

Best wishes



Tim F

 

[Siegfried Heintze]

... I don't think the field is to small; the contents are too big. The
field size will have been dictated by the systems analysis, data flow,
entity life-history, domain definition etc.

Whaddya do when you are scraping data off the web? I don't think there is
anything you can do except look at a few entries and make a guess.

PS: why use a transaction to atomise a single INSERT?

I'm using autoincrement integer primary keys and I don't want to be
interrupted between the time I peform an insert and the time I do the
"SELECT MAX(id) FROM JobPosting". Is not this necessary in a multi-threaded
or multi-user environment?

PPS: what library are you using to carry out the insert? The
DAO.Database.Execute method has an Options parameter that allows it to
throw a trappable error if there is a failure in the SQL statement.

Probably the ODBC library. I'm using the perl DBI with DBD::ODBC. With a
name like that, it is probably not DAO.

I wonder if there is a similar feature in ODBC?

Thanks,
Siegfried

 

[Tim Ferguson]

I'm using autoincrement integer primary keys and I don't want to be
interrupted between the time I peform an insert and the time I do the
"SELECT MAX(id) FROM JobPosting". Is not this necessary in a
multi-threaded or multi-user environment?

I may have misunderstood the nature of transactions, but I don't think
they will prevent another insert happening after yours. Transactions
allow rollbacks to take place as if they had never happened. Otherwise,
an uncommitted transaction would just stop the database for ever.

A better way to do this would be either (a) to use the IDENTITY keyword,
which returns the last autonumber allocated within the same connection;
or (b) to manage the whole allocation business on a recordset, eg

rs.AddNew
rs!RequiredField = "something"
NewIdentity = rs!ID
rs.Update

I'm using the perl DBI with DBD::ODBC. With
a name like that, it is probably not DAO.

You will need to see the documentation with the library that you are
using. In the "normal" access environment (this is, after all, an Access
newsgroup!!) trapping errors is either: easy, using DAO .Execute with the
dbFailOnError option; hard, using ADODB and almost anything; or
suppressed altogether with .Execute without the option. There should be
something in the library that lets you examine an Errors collection etc
etc.

Best wishes



Tim F

 

[Siegfried Heintze]

Could someone kindly point me to the URL on the SQL syntax for MSAccess and
specifically where the IDENTITY keyword is documented?

A better way to do this would be either (a) to use the IDENTITY keyword,
which returns the last autonumber allocated within the same connection;
or (b) to manage the whole allocation business on a recordset, eg

rs.AddNew
rs!RequiredField = "something"
NewIdentity = rs!ID
rs.Update

Thanks,
Siegfried

 

[Tim Ferguson]

Could someone kindly point me to the URL on the SQL syntax for
MSAccess and specifically where the IDENTITY keyword is documented?

Quite right: it's @@IDENTITY. Look here

http://doc.ddart.net/mssql/sql70/globals_7.htm

or search the MSDN site too.

B Wishes


Tim F

 

[Siegfried Heintze]

Tim,
I'm confused! This is for T-SQL (aka MS SQL Server). Does this also work for
MSAccess?

Siegfried

 

[Tim Ferguson]

I'm confused! This is for T-SQL (aka MS SQL Server). Does this also
work for MSAccess?

Yes, as long as you use the ADO library (not DAO, which was never updated
to Jet 4). I'm not completely sure that ADO/T-SQL handles the entire SQL
syntax, but most everyday things like @@IDENTITY, CHECK constraints,
DEFAULT values and so on are there.

That is why the answer to the question "Is DAO better for Access + Jet than
ADO" is, "Yes, except for..." Nothing in life is ever straightforward!

Best wishes


Tim F