Go to Top
  • No products in the cart.

Access developer

Access Programmer

Microsoft Access Support

  • Ongoing Support & Upgrades for your Database
  • Enhance your Database with New Features
  • Upsize your Database to the Web

Find out more
Report Generator

Report Generator

  • Use one lifetime license for all your company needs
  • Print or export reports to PDF or Excel
  • Send reports via Outlook email

Find out more
Upgrade to SQL Server

Upgrade to SQL Server

  • Preserve your Access Database
  • Experience High Speed Performance
  • Feel at Ease with Enhanced Security and Reliability

Find out more

At AccessExperts.com you will find, at any given working day, at least one Access developer that can help you "Discover the power of your data!"™ through reporting, custom software, and data collection. Your business is unique, your software should be too!

Access MVPs Juan Soto and Andy Tabisz host the MVP Tech Talks about Access Web Apps

Our Clients

logos

Testimonials

Melissa

Melissa

XeBee Records

IT Impact's three part design: Web App, Web Data Hosting and Access application, has allowed us to grow 100% for the last two years and automated a lot of our processes. Thank you IT Impact!
Scott

Scott

Dynametal Technologies

Thank you IT Impact for your assistance, knowledge and professionalism in working with us in converting the Access Database to SQL! Our system infrastructure is very complex and their expert Access developer, have helped us convert and simplify our database systems.
Trish

Trish

Claim Services, Inc

IT Impact upgraded our Access system to SQL Server and added Document Center plus workflow to get us to a paperless process. The IT Impact Access developer is very easy to work with and efficiently helps with any challenge! Well done IT Impact!

Latest articles from our Access Blog

Avoid number conflicts with SQL Sequences

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
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_AlterPolicySequence] (
@SeqName AS sysname,
@InpMin AS int,
@InpMax AS int
) WITH EXECUTE AS OWNER AS
BEGIN
SET NOCOUNT ON;

DECLARE @sql nvarchar(MAX),
@err nvarchar(MAX);

IF NOT EXISTS (
SELECT NULL
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’

Read More