Autonumbers vs Identity Key

[dguilbault]

I've got my database (2002) upsized and working well in SQL Server for
everything except one hairpulling thing. In Access, when i have a table
column defined as an AutoNumber, if i add data to a form, the autonumber
field is automatically populated. Now i find that once in SQL Server, it
doesn't populate until the record is saved! How am i supposed to refer to
the autonumber if i don't know what it is yet??????? ARRRGGGGHHHH

 

[Tony Toews [MVP]]

I've got my database (2002) upsized and working well in SQL Server for
everything except one hairpulling thing. In Access, when i have a table
column defined as an AutoNumber, if i add data to a form, the autonumber
field is automatically populated. Now i find that once in SQL Server, it
doesn't populate until the record is saved! How am i supposed to refer to
the autonumber if i don't know what it is yet???????

So how are you referring to the Autonumber ID field? Presumably in
code? Then, if the record hasn't been saved, then save the record.

If me.dirty = false then _
docmd.runcommand accmdsaverecord

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[dguilbault]

Oh if it were only that easy. No...this didn't work.

 

[Tony Toews [MVP]]

Oh if it were only that easy. No...this didn't work.

Please post your code.

Why didn't this work? Did you get an error message?

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[dguilbault]

Ok...here is what i ended up doing.

I have to do a Me.Recalc...this shows the identity key.
Then I had to add a boolean (bF9) as a public variable.
I then had to make bF9 true before doing the recalc, then set it to False
when done.

I then added code to the events that fired on the recalc, exiting the sub if
bF9 was true.

I also had to add code that adds the new record for any foreign table
identity keys.

WHAT A COMPLETE PAIN!!!!! WHO'S BRIGHT IDEA WAS IT TO MAKE SQL SERVER ONLY
ADD THE IDENTITY KEY AFTER THE RECORD SAVE??????? SHEESH

 

[Tom Wickerath MDB]

it's not a SQL Server problem

it is an MDB linked table problem if anything

move to SQL Server Access Data Projects and stfu, quit your whining bitch

sorry that it's SOOOOOO COMPLEX

I mean jesus christ

 

['69 Camaro]

Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Tom would never post such a message.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[dguilbault]

Thanks for the info...i run across these idiots all the time. The holier
than thou crowd, who have no idea what it is to be THE only developer on
LEGACY systems.

 

[dguilbault]

Oh...and by the way, genius ... an ADP acts EXACTLY THE SAME WAY.

 

[Pat Hartman \(MVP\)]

You only have to do what Tony suggested and you only have to do it if you
need to refer to the current record's Autonumber in code. You have gone way
over the edge. I use bound forms to linked SQL Server tables all the time
and never have to write any such code.

The reason that SQL Server doesn't assign an autonumber immediately is
because it doesn't know you need one until you (or Access) save the record.
All ODBC data sources act the same way. This is not an SQL Server issue.
It occurs with DB2, Oracle, MySQL, etc. Jet is entwined with Access which
is the only reason you see autonumbers early when your tables are Jet.

 

[dguilbault]

OK FOLKS...DROP IT. I HAVE A SOLUTION WHAT WORKS FOR ME.
I CAN'T HELP IT IF YOU WON'T BELIEVE THAT YOUR MVP GAVE ME AN ANSWER THAT
DOESN'T WORK.

 

[Tom Wimpernark]

look buddy-- the answer that the MVP gave _DID_ work

share your answer, kid