Keeping autonumbering after going to SQL backend

[jmillerWV]

I am in the middle of moving an Access2k3 Database to SQL. I have run into a
problem I need help with. My main table "OP" in Access uses they
autonumbering field as the primary key. this PK filed also acts as our order
number through the system. On main form "Add Record" starts a new record
enters dates and times by default the PK field then saves. I need some
suggestions on how to best duplicate the process of generating a sequential
number like autonumbering for the form and OP table. Hope this isn't
confussing.

 

[Klatuu]

SQL Server has the capability to use your Access Autonumber field, but you
have to set it up.
First, you want to identify the field data type as int
Then under Table Designer look for Indentity Specification
Set (Is Identity) to Yes
Set Identity Increment to 1
Set Identlty Seed to 1

It will work exactly like an Autonumber field

 

[jmillerWV]

This I understand. Yes I have set it up. The problem I need help with is that
SQL does not assign the "Autonumber" until after the INSERT is completed. I
need the ID number generated at the time the order is being entered so that
it will appear on the order sheet that is printed.

 

[Klatuu]

There are a couple of options.
One way to do that is set up your own sequential numbering scheme. The
simple answer is using the DMax function to find the highest current highest
number and add 1 to it.
The other is to force the record to update as soon as a new record is
created; however, that may not be feasible if you have any required fields.

What your dilema shows is why you never use autonumbers for anything other
than surrogate keys. Autonumber fields are really not meant for human
consumption.

There are more issue about this practice than I have time to list. My
suggestion would be to change which field you are using for your order number
and set up a scheme to increment that number and assign it in the Current
event of the form.

 

[Armen Stein]

In addition to Dave's suggestion for using an Identity field in SQL
Server, I'll add that the behavior is slightly different. In Access,
the new AutoNumber key value is generated the moment you start typing
in the form. In SQL Server, the Identity isn't generated until later,
when the record is actually saved.

If you have code that depends on the Access behavior, it will need to
be changed. If you haven't already, check out my PowerPoint
presentation on techniques for using Access as a client-server
front-end to SQL Server databases. It's called "Best of Both Worlds"
at www.JStreetTech.com/Downloads. It includes some thoughts on when
to use SQL Server, performance and security considerations,
concurrency approaches, and techniques to help everything run
smoothly. It also shows some code to retrieve the just-generated
Identity value.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[jmillerWV]

Thanks for your input. I will begin to look at doing it a different way.
Again thanks

 

[jmillerWV]

Thanks for the reply. I can see from the feed back that I will need to find a
different way than the way I was doing it. That's what makes this fun,
sometimes you have to find a different way of doing the same thing and in the
process you learn something new. Again thanks for your help.