Duplicate Values Question

I have a table containing, among others, a field titled
numSerialNumber ("SN") and a field titled strBoardNumber ("BN"). The
SN field is the primary key, and the BN field is required.
Additionally, the BN field must contain a unique value, unless the SN
gets voided. In that case, the BN field can contain "VOID".

Is there any way to allow this, and to have this check at the table
level? Users will sometimes access the table directly to quickly find
a record that needs to be corrected, and I don't want them to
accidentally change the BN field to a duplicate value.

Or, do I need to just implement something at the form level, and tell
users to view the form in datasheet view if they need to make
corrections?

Jason

unless the SN gets voided.
A primary key can not be voided.

Suggest using a Void field - Yes/No - for old serial numbers.
--
KARL DEWEY
Build a little - Test a little


" wrote:

I have a table containing, among others, a field titled
numSerialNumber ("SN") and a field titled strBoardNumber ("BN"). The
SN field is the primary key, and the BN field is required.
Additionally, the BN field must contain a unique value, unless the SN
gets voided. In that case, the BN field can contain "VOID".

Is there any way to allow this, and to have this check at the table
level? Users will sometimes access the table directly to quickly find
a record that needs to be corrected, and I don't want them to
accidentally change the BN field to a duplicate value.

Or, do I need to just implement something at the form level, and tell
users to view the form in datasheet view if they need to make
corrections?

Jason

Users should NEVER be allowed to work directly with tables or queries.
Create a form in datasheet view if that is most comfortable for them. You
can then put edit code in the various events to validate data.

Jet doesn't support triggers which would solve your problem but it is really
a non-problem since if the back end were SQL Server, you wouldn't even
consider letting the users at the tables directly.

wrote in message
...
I have a table containing, among others, a field titled
numSerialNumber ("SN") and a field titled strBoardNumber ("BN"). The
SN field is the primary key, and the BN field is required.
Additionally, the BN field must contain a unique value, unless the SN
gets voided. In that case, the BN field can contain "VOID".

Is there any way to allow this, and to have this check at the table
level? Users will sometimes access the table directly to quickly find
a record that needs to be corrected, and I don't want them to
accidentally change the BN field to a duplicate value.

Or, do I need to just implement something at the form level, and tell
users to view the form in datasheet view if they need to make
corrections?

Jason

On Jan 18, 9:28 pm, "Pat Hartman" please no wrote:
if the back end were SQL Server, you wouldn't even
consider letting the users at the tables directly.

The general approach in SQL Server land is to create a 'library' of
stored procedures to allow controlled access to the data. And speaking
of SQL Server, the OP could google the term "nullbuster" as a possible
approach to their problem.

Users should NEVER be allowed to work directly with tables or queries.

Drawbridge mentality g! If you think that Access 2007 means that Jet
is not dead then it must also mean that user level security in Jet is
dead, which would mean it's even more important than ever for
effective table constraints to be in place to prevent data corruption
via typos by sys admins (i.e. any user) and bugs in front end
applications. Speaking of which:

Create a form in datasheet view if that is most comfortable for them. You
can then put edit code in the various events to validate data.

"The poster was asked about some basic data integrity issue and in the
discussion, he replied that all the validation would be done in the
front end application program, so we did not have to bother with
constraints on the [database] side. Golly gee whiz, do you suppose
that might be a bad idea? Let's just list some of the assumptions and
situations required for this approach to work... This application and
database code will remain the same forever... Nobody else will ever
write new code against this database... If anyone does write new
application code against this database, he or she will always get the
constraints correct and maintain the data integrity... If several
programmers write new application code against this database, they
will always get the constraints correct and identical..."

http://www.dbazine.com/ofinterest/oi-articles/celko25/

Jamie.

--

My general approach when working with SQL server does NOT include the use of
stored procedures except in rare cases. I use Jet queries with selection
criteria and bind them to forms and reports.

"Jamie Collins" wrote in message
...
On Jan 18, 9:28 pm, "Pat Hartman" please no wrote:
if the back end were SQL Server, you wouldn't even
consider letting the users at the tables directly.

The general approach in SQL Server land is to create a 'library' of
stored procedures to allow controlled access to the data. And speaking
of SQL Server, the OP could google the term "nullbuster" as a possible
approach to their problem.

Users should NEVER be allowed to work directly with tables or queries.

Drawbridge mentality g! If you think that Access 2007 means that Jet
is not dead then it must also mean that user level security in Jet is
dead, which would mean it's even more important than ever for
effective table constraints to be in place to prevent data corruption
via typos by sys admins (i.e. any user) and bugs in front end
applications. Speaking of which:

Create a form in datasheet view if that is most comfortable for them.
You
can then put edit code in the various events to validate data.

"The poster was asked about some basic data integrity issue and in the
discussion, he replied that all the validation would be done in the
front end application program, so we did not have to bother with
constraints on the [database] side. Golly gee whiz, do you suppose
that might be a bad idea? Let's just list some of the assumptions and
situations required for this approach to work... This application and
database code will remain the same forever... Nobody else will ever
write new code against this database... If anyone does write new
application code against this database, he or she will always get the
constraints correct and maintain the data integrity... If several
programmers write new application code against this database, they
will always get the constraints correct and identical..."

http://www.dbazine.com/ofinterest/oi-articles/celko25/

Jamie.

--

On Jan 21, 1:52 pm, "Pat Hartman" please no wrote:
My general approach when working with SQL server does NOT include the use of
stored procedures except in rare cases.

I think you'll find that the majority of SQL Server MVPs do the exact
opposite.

Jamie.

--

That's because SQL Server MVP's are not Access MVPs and don't understand
Access and how it interacts with SQL Server and other ODBC data sources. I
didn't say that I didn't ever use stored procedures or pass-through queries,
only that they are not my first choice. I use them when necessary.

"Jamie Collins" wrote in message
...
On Jan 21, 1:52 pm, "Pat Hartman" please no wrote:
My general approach when working with SQL server does NOT include the use
of
stored procedures except in rare cases.

I think you'll find that the majority of SQL Server MVPs do the exact
opposite.

Jamie.

--

On Jan 22, 3:20 pm, "Pat Hartman" please no wrote:
SQL Server MVP's ... don't understand
Access and how it interacts with SQL Server and other ODBC data sources.

Shame, I'd hoped there would at least be one or two who had a foot in
both camps but you'd know better than I.

I
didn't say that I didn't ever use stored procedures or pass-through queries,
only that they are not my first choice.

Interesting because you said "rarely", which is different in my book
than "they are not my first choice". A cultural difference and nothing
important, I'd conclude

I use them when necessary.

I strive for best practise even when it isn't necessary and it is
widely considered best practise in the SQL Server community to not
grant applications direct access to the tables, regardless of the
inconvenience individual developers, and instead provide stored procs
for the applications to invoke.

Jamie.

--

We live in different universes. What is best practice for Web development
is not necessarily best practice for client/server development. And what is
best practice for WinForms development is not necessarily best practice for
Access development. You live in a SQL Server -centric world. My world
includes RDBMS' of all types which is why you almost certainly know more
about SQL Server than I do. I am a generalist. I know a little about a lot
of things. Large clients generally have a multitude of applications running
against various database servers. My Access applications frequently need to
access data from more than one RDBMS. Because of that, I rely heavily on
Jet SQL which will work regardless of which back end I connect to. I don't
always know when I start development where the BE will reside. I always use
development techniques with an eye to potentially upsizing to some non-Jet
BE. Relying on Jet to sort it all out gives me the ultimate in flexibility.
Only if I have a problem do I resort to stored procedures and views because
those are always RDBMS specific and therefore, they are limiting..

"Jamie Collins" wrote in message
...
On Jan 22, 3:20 pm, "Pat Hartman" please no wrote:
SQL Server MVP's ... don't understand
Access and how it interacts with SQL Server and other ODBC data sources.

Shame, I'd hoped there would at least be one or two who had a foot in
both camps but you'd know better than I.

I
didn't say that I didn't ever use stored procedures or pass-through
queries,
only that they are not my first choice.

Interesting because you said "rarely", which is different in my book
than "they are not my first choice". A cultural difference and nothing
important, I'd conclude

I use them when necessary.

I strive for best practise even when it isn't necessary and it is
widely considered best practise in the SQL Server community to not
grant applications direct access to the tables, regardless of the
inconvenience individual developers, and instead provide stored procs
for the applications to invoke.

Jamie.

--

On Jan 22, 11:00 pm, "Pat Hartman" please no wrote:
We live in different universes. What is best practice for Web development
is not necessarily best practice for client/server development. And what is
best practice for WinForms development is not necessarily best practice for
Access development. You live in a SQL Server -centric world. My world
includes RDBMS' of all types which is why you almost certainly know more
about SQL Server than I do. I am a generalist. I know a little about a lot
of things. Large clients generally have a multitude of applications running
against various database servers. My Access applications frequently need to
access data from more than one RDBMS. Because of that, I rely heavily on
Jet SQL which will work regardless of which back end I connect to. I don't
always know when I start development where the BE will reside. I always use
development techniques with an eye to potentially upsizing to some non-Jet
BE. Relying on Jet to sort it all out gives me the ultimate in flexibility.
Only if I have a problem do I resort to stored procedures and views because
those are always RDBMS specific and therefore, they are limiting..

Pat, sincere thanks for sharing your thoughts.

I don't think we're so different in our approaches -- no offence
intended

I previously worked on a shrink wrap (but highly customizable) product
where the user could choose one of Jet, Oracle and SQL Server for the
'back end'; most of the devs used linked tables in Access to have a
'standard' SQL interface. I went from there to a product that used
Intersystems Caché: for the underlying horror, see
http://thedailywtf.com/Articles/A_Ca...the_MUMPS.aspx but
thankfully it has a fairly decent SQL gateway. I was fine with that
because my personal 'response' to having to deal with multiple SQL
engines was to concentrate on Standard SQL. If I know more about Jet
and SQL Server respectively (i.e. non-Standard, proprietary details)
then it's because I've also worked on products which use exclusively
those engines or have had to deviate because the engine doesn't
support certain parts of the Standard. The problem with the 'linked
tables' approach IMO is that you make Jet SQL the common denominator
and Jet has been left far behind as regards Standard SQL.

Perhaps where we are most different is that I 'buy in' to the concept
of tiered architecture. I find it most strange that people round here
(I'm not thinking of anyone in particular) would go out of their way
to encourage people not to use SQL parameters but instead to hard code
form's and their controls' names into Jet SQL procs and views, to
avoid validation rules in tables in favour of validation in front end
forms, and so on.

Jamie.

--