Duplicate Values Question

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.

"Jamie Collins" wrote in message
...
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.

--

On Jan 23, 3:27 pm, "Pat Hartman" please no wrote:
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.

--

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" wrote in message
...
On Jan 23, 3:27 pm, "Pat Hartman" please no wrote:
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.

--

On Jan 23, 9:35 pm, "Pat Hartman" please no wrote:
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.

--

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

"Jamie Collins" wrote in message
...
On Jan 23, 9:35 pm, "Pat Hartman" please no wrote:
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.

--

On Jan 24, 3:38 pm, "Pat Hartman" please no wrote:
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.

--