If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Where is the record I just inserted?
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 |
#2
|
|||
|
|||
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 "Siegfried Heintze" wrote in message ... 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 |
#3
|
|||
|
|||
"Siegfried Heintze" wrote in
: 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 |
#4
|
|||
|
|||
... 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 |
#5
|
|||
|
|||
"Siegfried Heintze" wrote in
: 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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
"Siegfried Heintze" wrote in
: 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 |
#8
|
|||
|
|||
Tim,
I'm confused! This is for T-SQL (aka MS SQL Server). Does this also work for MSAccess? Siegfried "Tim Ferguson" wrote in message ... "Siegfried Heintze" wrote in : 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 |
#9
|
|||
|
|||
"Siegfried Heintze" wrote in
: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
New record created in table when trying to edit displayed record | cleongatl | Using Forms | 0 | August 30th, 2005 10:01 PM |
How to retrieve id of last inserted record in Access without using @@identity? | J.S. | Running & Setting Up Queries | 13 | August 24th, 2005 04:21 PM |
dealing with a subform record when it's "dirty" | Paul James | Using Forms | 8 | October 17th, 2004 08:45 AM |
Copy an existing record into appropriate tables after modifying. | bdehning | General Discussion | 8 | July 7th, 2004 08:44 AM |
Form Doesn't Go To New Record | Steve | New Users | 15 | May 16th, 2004 04:33 PM |