SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

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

SABDalton
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?

Thanks!

robvolk
Most Valuable Yak

USA
15557 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):
ALTER TABLE myTable ADD CONSTRAINT Check_Date CHECK(myDateColumn <= CURRENT_TIMESTAMP)
Go to Top of Page

SABDalton
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  
 New 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.03 seconds. Powered By: Snitz Forums 2000