MS Access Sequence jumped when invalid data is entered

[anandan.cr@gmail.com]

I have created a SQL Server Sequence (Backend)

CREATE SEQUENCE [dbo].[Airlines_Mast_SEQ]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 99999
CACHE
GO

CREATE TABLE [dbo].[Airlines_Mast](

[ID] [int] NOT NULL DEFAULT NEXT VALUE FOR [Airlines_Mast_SEQ] PRIMARY KEY,
[Airlines_ID] AS ('AS'+right('00000'+CONVERT([varchar],[ID]),(6))) PERSISTED NOT NULL,
[Airlines_CODE] [varchar](4) NOT NULL,
[Airlines_Name] [nvarchar](100) NOT NULL,
[Airlines_GSTIN] [varchar](15) NOT NULL,
[Airlines_Address1] [nvarchar](100) NULL,
[Airlines_Pincode] [varchar](10) NULL,
[Legal_Name] [nvarchar](100) NULL,
[Type] [varchar](15) NULL,
[Airlines_Active] [bit] NOT NULL
) ON [PRIMARY]

MS Access(Front End)

I have created a Form in MS Access using table Airlines_Mast , while entering data in Form

(1) When Data is correctly entered for a Record in one Go then SEQUENCE is maintained but, (2) When invalid Data is entered multiple time for a Record after validation check then SEQUENCE got jumped to next sequence.

Kindly suggest to maintain the sequence without Jump.

 

[arnelgp]

is it possible to add an Identity Column instead.

 

[anandan.cr@gmail.com]

is it possible to add an Identity Column instead.

We tried with Identity Column, but after adding Identity It is jumped when invalid data is entered

 

[arnelgp]

the same is true with MSaccess table (autonumber field).
create a custom function that generates sequential number.
there are many thread here. you just need to search it.

 

[Pat Hartman]

The purpose of an autonumber/identity column is to provide a unique value. In order to do this, Access and SQL Server locks the assigned number. If the save fails, a new number is generated. This is normal behavior.

If you require an ascending number without gaps, you need to generate your own sequence number. I can post code that shows how to generate the "next" sequence number if you need it. However, the sample code does not include any retry logic which you would need to add. Remember, you are working in a multi-user environment so it is always possible for you to generate a number but before your insert is committed, someone else beats you to the punch and uses that number on you. Your insert will fail. You need to trap this error and generate the next number and try the save again. This is part of the reason that autonumber/identity columns don't use this method. They simply discard the unused number and don't try to reuse it.

 

[anandan.cr@gmail.com]

The purpose of an autonumber/identity column is to provide a unique value. In order to do this, Access and SQL Server locks the assigned number. If the save fails, a new number is generated. This is normal behavior.

If you require an ascending number without gaps, you need to generate your own sequence number. I can post code that shows how to generate the "next" sequence number if you need it. However, the sample code does not include any retry logic which you would need to add. Remember, you are working in a multi-user environment so it is always possible for you to generate a number but before your insert is committed, someone else beats you to the punch and uses that number on you. Your insert will fail. You need to trap this error and generate the next number and try the save again. This is part of the reason that autonumber/identity columns don't use this method. They simply discard the unused number and don't try to reuse it.

Many -Many thanks for your Support, Requesting you to Please Share the sample code for sequence number generation.

 

[Pat Hartman]

This sample was created for a different purpose so it doesn't do everything you need. You will need to add the code to trap the duplicate PK error and loop to generate another sequence number. Unless you have a very busy application, you will rarely encounter the duplicate error but it is always possible in a multi-user environment so it is best to deal with it proactively.

I will add a third example to the database that does what you need but it won't happen for a few days. Hope this gets you on the right track.

 

[Isaac]

I have created a SQL Server Sequence (Backend)

CREATE SEQUENCE [dbo].[Airlines_Mast_SEQ]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 99999
CACHE
GO

CREATE TABLE [dbo].[Airlines_Mast](

[ID] [int] NOT NULL DEFAULT NEXT VALUE FOR [Airlines_Mast_SEQ] PRIMARY KEY,
[Airlines_ID] AS ('AS'+right('00000'+CONVERT([varchar],[ID]),(6))) PERSISTED NOT NULL,
[Airlines_CODE] [varchar](4) NOT NULL,
[Airlines_Name] [nvarchar](100) NOT NULL,
[Airlines_GSTIN] [varchar](15) NOT NULL,
[Airlines_Address1] [nvarchar](100) NULL,
[Airlines_Pincode] [varchar](10) NULL,
[Legal_Name] [nvarchar](100) NULL,
[Type] [varchar](15) NULL,
[Airlines_Active] [bit] NOT NULL
) ON [PRIMARY]

MS Access(Front End)

I have created a Form in MS Access using table Airlines_Mast , while entering data in Form

(1) When Data is correctly entered for a Record in one Go then SEQUENCE is maintained but, (2) When invalid Data is entered multiple time for a Record after validation check then SEQUENCE got jumped to next sequence.

Kindly suggest to maintain the sequence without Jump.

Anyone on this thread who has stated, in essence, that you ought not to be trying to maintain a sequence of an auto-number Key column (in any database system), I completely agree with.

Nonetheless, if you insist on thinking you need to do this, you might:

1) Skip the whole SEQUENCE on SQL Server
2) Create column [ID] identity(1,1)
3) Before doing an insert, figure out what is the max value of the ID column on sql server, and increment that by one to a variable of some kind
4) Turn IDENTITY INSERT On (on sql server table)
5) Insert the record, inserting the actual variable value from step 3 into the ID (identity) column
6) Turn IDENTITY INSERT Off (on sql server table)

Add to that an extra step in Access to be able to assign incrementing IDs to each record in case of a multi-record insert, using the seed value as what you determined in #3

But if you have multiple users that could be trying to do these inserts at the same time, this approach may be useless. It's up to you whether this might work in your particular scenario.
You could probably even go a step further ... Inserting your records to a Staging table, which would trigger a stored procedure that offficially LOCKED the final table in question, then performed the insert....But you probably should create a NON-primary key column which is user-facing and deliberately designed to sequence perfectly, rather than do all this.
Just another option to play with.

 

[The_Doc_Man]

I'm going to step into this mess another way. You shouldn't be doing this with autonumbers in the first place. An autonumber, no matter whether it is Access tables or SQL tables or any other flavor, MUST NOT have an inherent meaning. If you are doing this because your accountants complain about gaps, fire the accountants for inflexibility. (OK, only partly kidding.) An autonumbered field CANNOT HAVE MEANING other than to uniquely identify a record. Higher or lower numbers in the same field of different records has NO BEARING on the current record. If you try to make it have any other meaning - such as a contiguous sequence number - then you have violated the expressed intent and limitation of autonumber fields. They are unique record IDs and that is ALL that they are, or can be. Not continuous or contiguous, but ONLY unique.

The discussions here have led you to the only concept that can work without giving you gaps... compute the next number in your sequence field programmatically - NOT by autonumber. Then store your new record with this number. The table must have the number in a field that is Indexed (Unique). If you use something like the CurrentDB.Execute with the dbFailOnError option, you can TRAP errors that tell you that your update didn't work because of a duplicate value, in which case pick the next number in sequence and repeat the process until you get it accepted. The option to fail on errors means the DB will do an auto-rollback so you haven't committed anything yet. While the implied loop might be a pain in the toches, you WILL get your unique and consecutive number... usually. But even this method might not work correctly. In theory, competing processes could still enter into - and lose - a race on who gets the next number.

If this absolutely must be contiguously numbered with no gaps EVER at ANY TIME and your life depends on it (told you to shoot the accountants), you need to pre-lock the table by having a separate lock table, one record for each table, that contains the table name, a user name, and a lock field. You put a TRUE in the lock field in this table and put your username in username field, having selected the record to be used for the table you are updating. Then immediately close the recordset to the lock. Then as a separate step, verify that the lock is set and that your name appears as the lock owner. If so, you CAN use the "DMax + 1" method. But you have to remember to erase your name and clear (set FALSE) the lock in the lock table when you are done with this update. No, I'm not kidding you. This is the magnitude of the problem if you absolutely MUST have contiguous numbering.

 

[Isaac]

I'm going to step into this mess another way. You shouldn't be doing this with autonumbers in the first place.

Definitely agree (1st sentence of my post)