Microsoft Access Support
- Ongoing Support & Upgrades for your Database
- Enhance your Database with New Features
- Upsize your Database to the Web
Note: I ‘ll be presenting on this topic at the Access with SQL Server group online: http://accessusergroups.org/sql-server-with-access/, please join me on September 13th at 6:30 PM CST, join the group to get an email with all of the meeting details, it’s free!
Do you need to guarantee that a number in a field will only be used once and never duplicated by another user?
Have you had a situation where you needed more than one autonumber in a table?
Have you ever needed a lower and an upper limit of sequential numbers, and you could not go beyond it?
Do you sometimes have a list of numbers that you want to recycle after you get past the last one?
In SQL Server, there is a feature that can handle this quite easily, and it is called a sequence. It is available starting in SQL Server 2012.
Like an autonumber, it can assure that a unique number will be given out each time, unless it recycles.
Recently I was asked to implement a sequence for a client, where multiple users will create new records and have to “fetch” the next number in a specific sequence. We could not use an autonumber because the customer was limited to a certain range, not to exceed an upper threshold. When the numbers where exhausted, management would replenish the sequence anew.
Why using an Access table does not work
Prior to upgrading to SQL Server, users would share a table that would keep tabs on what is the next number to use, the problem with this approach is that it’s not fool proof, two users may request the same number at the exact same time, violating the business rule.
Creating and using a SQL Server Sequence
Before you can use a sequence, it must be created with the following syntax in SQL Server, you only need to do this once:
CREATE SEQUENCE dbo.seqPolicyNumber AS int MIN 50005000 MAX 50005999;
Use the following statement to retrieve the next sequence number:
SELECT NEXT VALUE FOR dbo.seqPolicyNumber as NextValue
Your users will need update permissions to use the sequence, but they should not be able to alter the range of the sequence. Update permissions can be given using this syntax:
GRANT UPDATE ON dbo.seqPolicyNumber TO [MyDatabaseUserOrRole];
To get the next value of a sequence from an Access VBA program, you can use the following statement to read the next value into an ADODB recordset.
strSQL = “SELECT NEXT VALUE FOR dbo.seqPolicyNumber as NextValue”
OpenMyRecordset rs, strSQL
NextValue = rs(“NextValue”)
This is how we typically open an ADODB recordset in our firm. For more information on how you can use OpenMyRecordset, you can click on another article in our blog:
Easy ADODB Recordsets and Commands in Access
The nice thing about the syntax to get the next sequence number is that it is very easy to use in T-SQL. You just substitute NEXT VALUE FOR <sequence name> where you would normally get a value from a field name, parameter, or a constant. The following shows how it can be used in an Insert statement.
INSERT dbo.Orders (OrderID, Name, Qty)
VALUES (NEXT VALUE FOR dbo.OrderNumberSequence, ‘ Tire ‘, 2);
More flexibility than Autonumber
A sequence can offer more flexibility than an autonumber in Access, or an IDENTITY field in SQL Server. First, you can only have one autonumber or identity field in a table. Although you can reseed an IDENTITY field, you cannot recycle values. IDENTITY fields are still useful for primary keys, when we want some arbitrary number to identify the record, and it has no meaning. Sequences ranges however can have embedded meaning.
You are also not restricted to using integers like an IDENTITY, but sequence numbers can also be decimal or numeric. Also you can increment down in your sequence instead of just up.
Also a sequence is not tied to any specific table and can be used across tables as new sequence numbers are needed for a particular table.
Replenish the Sequence
When you want to change the range for a sequence, like when you need a new range of policy numbers, it should be done with a stored procedure. The following is a stored procedure that can do this.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [dbo].[usp_AlterPolicySequence] (
@SeqName AS sysname,
@InpMin AS int,
@InpMax AS int
) WITH EXECUTE AS OWNER AS
SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX),
IF NOT EXISTS (
FROM sys.sequences AS s
WHERE s.name = @SeqName
AND s.schema_id = SCHEMA_ID(‘ dbo ‘)
THROW 50000, ‘ The sequence name is not valid. ‘, 1;
IF @InpMin IS NULL OR @InpMax IS NULL
THROW 50000, ‘ The values cannot be null. ‘, 1;
SET @sql = CONCAT(N’ ALTER SEQUENCE [dbo]. ‘, QUOTENAME(@SeqName), N’
We just completed a massive 39 database conversion to SQL Server for a client in Tennessee which was quite
When we design Access with SQL Server solutions we almost always add CreatedBy and CreatedOn to all of our