A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Keeping autonumbering after going to SQL backend



 
 
Thread Tools Display Modes
  #1  
Old July 8th, 2009, 02:19 PM posted to microsoft.public.access
jmillerWV
external usenet poster
 
Posts: 50
Default Keeping autonumbering after going to SQL backend

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.

  #2  
Old July 8th, 2009, 02:40 PM posted to microsoft.public.access
Klatuu
external usenet poster
 
Posts: 7,074
Default Keeping autonumbering after going to SQL backend

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
--
Dave Hargis, Microsoft Access MVP


"jmillerWV" wrote:

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.

  #3  
Old July 8th, 2009, 04:02 PM posted to microsoft.public.access
jmillerWV
external usenet poster
 
Posts: 50
Default Keeping autonumbering after going to SQL backend

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" wrote:

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
--
Dave Hargis, Microsoft Access MVP


"jmillerWV" wrote:

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.

  #4  
Old July 8th, 2009, 04:14 PM posted to microsoft.public.access
Klatuu
external usenet poster
 
Posts: 7,074
Default Keeping autonumbering after going to SQL backend

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.
--
Dave Hargis, Microsoft Access MVP


"jmillerWV" wrote:

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" wrote:

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
--
Dave Hargis, Microsoft Access MVP


"jmillerWV" wrote:

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.

  #5  
Old July 8th, 2009, 04:25 PM posted to microsoft.public.access
Armen Stein
external usenet poster
 
Posts: 507
Default Keeping autonumbering after going to SQL backend

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

  #6  
Old July 8th, 2009, 07:30 PM posted to microsoft.public.access
jmillerWV
external usenet poster
 
Posts: 50
Default Keeping autonumbering after going to SQL backend

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

"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"jmillerWV" wrote:

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" wrote:

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
--
Dave Hargis, Microsoft Access MVP


"jmillerWV" wrote:

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.

  #7  
Old July 8th, 2009, 07:34 PM posted to microsoft.public.access
jmillerWV
external usenet poster
 
Posts: 50
Default Keeping autonumbering after going to SQL backend

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.

"Armen Stein" wrote:

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.