"Identity cannot be determined for newly inserted rows."

[Guest]

I'm considering changing the connection string for my queries from using ODBC
to OLEDB. However, when I try this I get the following error in one part of
my code:

The error was generated in this...

rstSMF.open "SELECT * FROM tmpSMF WHERE 1=0", cnn, adOpenStatic,
adLockOptimistic
rstSMF.addNew
rstSMF.update

(get the ID using @@IDENT, i need this to poke into a different table)

rstSMF!something = "Some text" *boom*

It appears that under OLEDB, once you have issued an update on a new row,
you're basically locked out and any attempt to change values from that point
on will cause this error. Is this correct?

If so, is there some way to avoid this? I'm not sure where this access
pattern is used, and I'd hate to have to track down every .update in the
entire (very large) app.

Maury

 

[Pat Hartman \(MVP\)]

What prompted the change from ODBC to OLEDB? I think (but don't take this
as gospel) I heard that OLEDB is old technology.

 

[Guest]

What prompted the change from ODBC to OLEDB? I think (but don't take this
as gospel) I heard that OLEDB is old technology.

Well actually ODBC is even older. OLEBD, while no spring chicken, is
supposed to be faster and "more direct" in terms of object model. It seems to
be faster when I try it. A better reason is that you don't have to define a
DSN, so you don't have to do any setup on a new machine when you want to
install your Access app.

Maury

 

[Aaron Kempf]

with Access Data Projects you can use @@IDENTITY in order to determine the
new autonumber value

MDb just doesn't have this capability

 

[Guest]

with Access Data Projects you can use @@IDENTITY in order to determine the
new autonumber value

MDb just doesn't have this capability

It's not an MDB.

Maury

 

[Tony Toews [MVP]]

with Access Data Projects you can use @@IDENTITY in order to determine the
new autonumber value

MDb just doesn't have this capability

If you use ADO MDBs do have this capability.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Aaron Kempf]

I agree

I don't allow Microsoft to 'change directions'

and most of the MVPs around here are just spreading mis-information

 

[Aaron Kempf]

can you really?

wow-- I had no idea.
I'll still never use MDB for anything though; all it is is bloatware

 

[Guest]

and most of the MVPs around here are just spreading mis-information

Bzzzt! Bzzzt! Irony alert! Irony alert!

Maury