Duplicate Values Question

[runnik]

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

 

[KARL DEWEY]

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

Suggest using a Void field - Yes/No - for old serial numbers.

 

[Pat Hartman]

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.

 

[Jamie Collins]

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.

--

 

[Pat Hartman]

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.

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.

 

[Jamie Collins]

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.

--

 

[Pat Hartman]

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]

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.

--

 

[Pat Hartman]

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]

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_Case_of_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.

--

 

[Pat Hartman]

I think you need to understand the audience here. We don't see many
professional developers. Mostly we see people who just happened to fall
into the development arena and have little or no training. Many posters
cannot handle VBA let alone work with SQL Management Studio (BRING BACK
ENTERPRISE MANAGER!!!!!) to create stored procs and triggers so the advice
we tend to offer is that which can be understood and implemented by
untrained people. Think of it as the difference between "the best possible
solution" and "the best solution possible".

I have high hopes for Jet (now called ACE, I think) now that the Access team
has taken over development from the SQL Server team. I am hoping we'll see
some real improvements in functionality and stability. The SQL Server team
thought of Jet as a toy and a competitor so they were not inclined to keep
it current. They totally missed the point of Access as a RAD tool and a
complement to SQL Server.

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_Case_of_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.

--

 

[Jamie Collins]

Think of it as the difference between "the best possible
solution" and "the best solution possible".

Nice one

I have high hopes for Jet (now called ACE, I think) now that the Access team
has taken over development from the SQL Server team. I am hoping we'll see
some real improvements in functionality and stability. The SQL Server team
thought of Jet as a toy and a competitor so they were not inclined to keep
it current. They totally missed the point of Access as a RAD tool and a
complement to SQL Server.

As an MVP you may have the benefit of some inside information but from
here on the outside things look the complete opposite. Cast you mind
back to Access 2000, the last time engine had a major release. Jet 4.0
had significant new functionality:

Description of the new features that are included in Microsoft Jet 4.0
http://support.microsoft.com/kb/275561

My favourites are the DECIMAL and NCHAR data types, truly table-level
CHECK constraints and improved SQL DDL syntax. IMO the SQL Server team
did a great job.

On the flip side, it seems to me that Access 2000 has gone down in
history as a big disappointment. Even today, nearly a decade on and
three major releases of Access later, many of the Jet 4.0 features are
still to be properly exposed in the Access user interface: NCHAR,
CHECK constraints, SET NULL referential actions, WITH COMPRESSION data
types, row-level locking, and more. The ACE version of DAO should have
left ADO dead in the water but, again, the majority of the
abovementioned Jet 4.0 functionality remains absent.

As regards the ACE version of the engine, it seems to me that more has
been lost than gained. Did the request for multi-value data types come
from the Access user base? Was it really neglect on the part of the
SQL Server team that caused replication to disappear from Jet? Can a
SQL DBMS without user level security really be taken seriously? In all
truthfulness, the only good thing I've seen in ACE is the fix for the
DECIMAL sort bug.

Jamie.

--

 

[Pat Hartman]

My understanding is that compatibility with Sharepoint drove most of what
happened with the first release of ACE. The MVP community was generally
upset with the "enhanced" datatypes and the disappearance of user level
security. Many fixes were made to the navigation pane between when we first
saw the demo of A2007 and when it was finally released due to our comments.
The problem was that so many people complained about ULS that the team just
got rid of it thinking that no one wanted to use it. Of course they missed
the point of the complaints. The Nav Pain (SIC) replaces some of the object
protection for a final database but it is at a much higher level than ULS.

 

[Jamie Collins]

Many fixes were made to the navigation pane between when we first
saw the demo of A2007 and when it was finally released due to
[the MVP community's] comments.

I thought we were discussing the *engine* but anyway good to hear that
Access MVPs still carry some clout.

My understanding is that compatibility with Sharepoint drove most of what
happened with the first release of ACE. The MVP community was generally
upset with the "enhanced" datatypes and the disappearance of user level
security.

So why *do* you have "high hopes for Jet... now that the Access team
has taken over"?

I'm not trying to twist your words or whatever. I am genuinely puzzled
by your optimism, based on ACE as released and, as you have said, the
apparent driver being Microsoft corporate rather than MVP community or
Access user base.

[If the answer is NDA -- AFAIK we on the outside have heard *nothing*
about further enhancements to the engine for the next release of
Access -- then fair enough.]

Jamie.

--

 

[Pat Hartman]

The answer is NDA. I have high hopes because I have spoken to the
developers and I believe they feel our pain.

Many fixes were made to the navigation pane between when we first
saw the demo of A2007 and when it was finally released due to
[the MVP community's] comments.

I thought we were discussing the *engine* but anyway good to hear that
Access MVPs still carry some clout.

My understanding is that compatibility with Sharepoint drove most of what
happened with the first release of ACE. The MVP community was generally
upset with the "enhanced" datatypes and the disappearance of user level
security.

So why *do* you have "high hopes for Jet... now that the Access team
has taken over"?

I'm not trying to twist your words or whatever. I am genuinely puzzled
by your optimism, based on ACE as released and, as you have said, the
apparent driver being Microsoft corporate rather than MVP community or
Access user base.

[If the answer is NDA -- AFAIK we on the outside have heard *nothing*
about further enhancements to the engine for the next release of
Access -- then fair enough.]

Jamie.

 

[Jamie Collins]

The answer is NDA. I have high hopes because I have spoken to the
developers and I believe they feel our pain.

That's good then. Sincere thanks for you time.

Jamie.

--