Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SABDalton
Starting Member
2 Posts |
 Posted - 2012-10-25 : 17:57:36
|
| 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
15732 Posts |
Posted - 2012-10-25 : 19:59:10
|
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) |
 |
|
|
SABDalton
Starting Member
2 Posts |
Posted - 2012-10-26 : 16:59:26
|
| 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. |
|
|
|
|
|
|
Upsized Access to SQL but No Validations carried