Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Upsized Access to SQL but No Validations carried
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 10/25/2012 :  17:57:36  Show Profile  Reply with Quote
Hi there,

I decided to use SQL server to back up data as part of a quality managment system. I have been using Access to store unique lot numbers. When I upsize to SQL, none of the data validation steps carried over. Furthermore, the autonumber skips when a transaction fails-- which is not aligned with the quality management system protocols.

(1) Is there a way to set up validation warnings or pop up messages to make sure data fields are being filled in?
(without causing a transaction failure, thus a skipped autonumber)

(2) Can I set up a date validation so that future dates are not used on the creation of the data record?


Most Valuable Yak

15732 Posts

Posted - 10/25/2012 :  19:59:10  Show Profile  Visit robvolk's Homepage  Reply with Quote
Are you still using Access as a data entry front end? If so, I don't believe you can avoid skipping the auto number without reprogramming your forms. Once an INSERT begins in SQL Server the identity (auto-number) value is incremented even if the INSERT fails or is rolled back. This is by design.

You can add a constraint to a table to control dates (run this directly on SQL Server through a pass-through query or using Management Studio):
Go to Top of Page

Starting Member

2 Posts

Posted - 10/26/2012 :  16:59:26  Show Profile  Reply with Quote
Thanks Rob Volk,

I definitely need the autonumber for quality management. I can remove the linked table, keep the validations in Access, then upsize the data to SQL.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000