> Hello all,
> I have an Access 2k3 Database I am moving to SQL 2k. In Access I used the
> autonumbering property to generate a "Record Number" for work to be preformed
> by us. This record number was placed on the work sheet as soon as it printed.
> this number also was used in identifying the order and so on. When "Add
> Record" button on the entry form was pressed a new record was started and the
> Autonumber was placed in the id fiel n the form. Problem is I can't seem to
> get SQL to do the same. Will be using the Access fronted end connected to
> SQL. Can anyone help or point me in the right direction? Thanks in advance

"Aaron Bertrand [SQL Server MVP]" wrote:

> Does the column have the IDENTITY property?  I have not used Access as a
> front end to SQL Server, but it is important to note that in SQL Server, the
> IDENTITY value is not assigned until the INSERT is complete.  So I guess
> whether your form can access the number before it is really complete depends
> on how the form is coded (meaning this may still be an Access question - SQL
> Server has no idea that your form exists, never mind more specific details
> about how it works or what it expects).
>
>
> On 7/7/09 3:01 PM, in article
> F52A1C0A-79D0-4310-AE5B-BA2DEE4AC***@microsoft.com, "jmillerWV"
> <jmille***@discussions.microsoft.com> wrote:
>
> > Hello all,
> > I have an Access 2k3 Database I am moving to SQL 2k. In Access I used the
> > autonumbering property to generate a "Record Number" for work to be preformed
> > by us. This record number was placed on the work sheet as soon as it printed.
> > this number also was used in identifying the order and so on. When "Add
> > Record" button on the entry form was pressed a new record was started and the
> > Autonumber was placed in the id fiel n the form. Problem is I can't seem to
> > get SQL to do the same. Will be using the Access fronted end connected to
> > SQL. Can anyone help or point me in the right direction? Thanks in advance
>
>

"Erland Sommarskog" wrote:

> jmillerWV (jmille***@discussions.microsoft.com) writes:
> > I have an Access 2k3 Database I am moving to SQL 2k. In Access I used
> > the autonumbering property to generate a "Record Number" for work to be
> > preformed by us. This record number was placed on the work sheet as soon
> > as it printed. this number also was used in identifying the order and so
> > on. When "Add Record" button on the entry form was pressed a new record
> > was started and the Autonumber was placed in the id fiel n the form.
> > Problem is I can't seem to get SQL to do the same. Will be using the
> > Access fronted end connected to SQL. Can anyone help or point me in the
> > right direction? Thanks in advance
>
> Well, if you want this to work like in Access, you will need to have a
> separate table with a single integer column which you access like this:
>
>    CREATE PROCEDRURE get_next_recnumber @nextno int OUTPUT AS
>    IF NOT EXISTS (SELECT * FROM recnumbers)
>    BEGIN
>       INSERT recnumbers(num) VALUES(1)
>       SELECT @nextno = 1
>    END
>    ELSE
>       UPDATE recnumbers
>       SET    @nextno = nextno,
>              nextno = nextno + 1
>    END
>
> With this solution you should not use IDENTITY on the real table you
> insert to later, as you will use the number returned to this procedure.
>
> I suspect though, that most people who have moved from Access to
> SQL Server, have gone for a different solution. You may get some good
> answers in an Access forum.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
>