Beginner's question about formatting date/time

[Access Developer]

Many users and developers handle millions of records, quite capably and
handily, with Access-Jet databases, so "as an MS MVP," he wouldn't know what
you claim. In fact, in some databases and environments, using Access and
Jet (or now Access and ACE) is substantially faster.

There can be other reasons, and good ones, for using Access as a front-end
to a server DB back end, but "merely" dealing with a few millions of records
is not always a reason, much less a good reason.

Larry Linson
Microsoft Office Access MVP

AND with large data sets (I have some sets with millions of records that
get
searched by date) [....]

If you have millions of records to search then Access is not for you, but
as
an MSMVP you already know that, don't you? You should have switched to
SQL
Server, SQL Anywhere, or something else more robust . Access was always
intended as a small database engine. It happens to be excellent at what
it
does, but it's no replacement for a more robust database designed for
millions
of records.

 

[David Kaye]

There can be other reasons, and good ones, for using Access as a front-end
to a server DB back end, but "merely" dealing with a few millions of records
is not always a reason, much less a good reason.

All I can say is that as lead developer of medical software for the organ
transplant arena, with our software suite used in medical clinics throughout
North America, we didn't find the Access engine robust enough. Sure, it was
great to work with and I hated to see it go, but we found we had to compact
and index daily. I automated the process, but it was clear that this was only
a make-do until we migrated to SQL Server.

I happen to love Access/Jet and I still use it today. But I'm talking from my
experience having had to use it in an environment where we needed a robust
database.

 

[John Spencer]

I avoided responding to your earlier message, but since this thread has been
re-opened.

I will agree that using a server database has some advantages over using
Jet/ACE. The two biggest advantages for me are:
Security is a big advantage since it is easier to set up and more robust.
Backup and Recovery in case of failure is much better if set up correctly.

However, there are advantages to using Jet/ACE as the data source. In over 10
years I have had one index failure with any of the multiple Access databases.
And almost no corruption problems that were not traceable to network issues
or user actions. I will admit that those could have been avoided if I were
using an SQL server. One advantage to NOT using a server is that there is no
need for knowledge about SQL server at the user site. If the user site has an
SQL server and administrators then I always attempt to use the SQL server.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[David W. Fenton]

(e-mail address removed) (David Kaye) wrote in

AND with large data sets (I have some sets with millions of
records that get searched by date) [....]

If you have millions of records to search then Access is not for
you, but as an MSMVP you already know that, don't you? You should
have switched to SQL Server, SQL Anywhere, or something else more
robust . Access was always intended as a small database engine.
It happens to be excellent at what it does, but it's no
replacement for a more robust database designed for millions of
records.

"Millions of records" is a red herring in regard to problems that
happen when your queries are written so as to be unable to use
indexes. It doesn't take anything close to a million records for
that to be a problem.

And, of course, as others have said, Jet/ACE can handle "millions of
records" just fine -- it depends on what kinds of records and how
the schema is put together and what you're doing with them. I've had
production apps with 100s of thousands of records in multiple
related Jet/ACe tables and there were never any performance problems
at all.

 

[David Kaye]

"Millions of records" is a red herring in regard to problems that
happen when your queries are written so as to be unable to use
indexes. It doesn't take anything close to a million records for
that to be a problem.

You're assuming that I wrote queries that couldn't use indices. I never said
that and I have never posted any queries from that project, so there's no way
you could second-guess me, either.

The queries, as I remember were nearly all based on unique patient ID numbers,
assigned when the patients were entered into the system. We couldn't use
first and last names except as a fuzzy check, assuming they were even provided
by the labs.

The test results from anywhere from 3 to 8 vendors were imported in various
ways (servers, email, ftp, etc), parsed, and saved as test result records
using the patient ID as a non-unique index. All test results were saved as
long as they passed CRC or some other check. Even duplicate results had to be
saved, since some labs sent them, and they wouldn't flag if they were re-sends
or corrections.

Pulling up patient records would also query the test results for that patient.
Depending on the criteria set by the user, the test results would be pulled by
the unique test identifier code, the ICD-9 (disease) code that the test was
related to, the alpha name of the test, and/or the date/time the result came
in.

On a typical base of about 900 patients we'd probably pull about 20,000 tests
a day.

As I said before, we'd have to compact and re-index the Access 2000 database
nightly in order to keep the speed reasonable. That was my only complaint
about Access.

 

[Access Developer]

The reasons John Spencer cited would be much more applicable to justifying
use of a server database for medical data than performance.

BTW, don't get your knickers in a knot. David did not "accuse you" of not
taking advantage of indices; and he is absolutely correct that "too many
records" is a "red herring" for databases done without detailed knowledge of
how Jet (and now ACE) works, or databases done by people who don't know what
they are doing. I don't think you've said anything to indicate that you
fall in either of those categories and I know that David is sharp enough to
realize that, as well; on the other hand, at the time he wrote that, you had
not added explanations that would indicate that you did not fall in that
category, as I believe you now have.

 

[David Kaye]

BTW, don't get your knickers in a knot. David did not "accuse you" of not
taking advantage of indices; [....]

David Fenton is an asshole from another newsgroup. He looks for any way to
berate me, so his response was no surprise to me. I've seen his posts for
more than a decade.

 

[John W. Vinson]

BTW, don't get your knickers in a knot. David did not "accuse you" of not
taking advantage of indices; [....]

David Fenton is an asshole from another newsgroup. He looks for any way to
berate me, so his response was no surprise to me. I've seen his posts for
more than a decade.

Don't feel like the Lone Ranger, David. Fenton's knowledge is deep, his mind
is quick, and his tact and gentleness are imperceptible.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[James A. Fortune]

Many users and developers handle millions of records, quite capably and
handily, with Access-Jet databases, so "as an MS MVP," he wouldn't know what
you claim.  In fact, in some databases and environments, using Access and
Jet (or now Access and ACE) is substantially faster.

There can be other reasons, and good ones, for using Access as a front-end
to a server DB back end, but "merely" dealing with a few millions of records
is not always a reason, much less a good reason.

 Larry Linson
 Microsoft Office Access MVP

I agree that it's possible for Access to handle millions of records,
but it's not always desirable. In our zeal to defend Access'
capabilities, we might be overlooking a few facts. Even with indexes/
indices, I think that the search time to find a record is O(log(N)),
so the increase in search time going from 100's of thousands of
records to millions of records will be noticeable. Also, more server
cores will be available in the future for processing and other
backends will likely take advantage of those before Access can. I
have used Access with a table approaching a million records and
noticed that the table required more care than what was usually needed
with smaller Access tables. It seems like larger tables need more
time to do a write. If that is so, then the risk of corruption rises
also. Each individual has to evaluate the tradeoffs when considering
upgrading to Oracle, or MySQL or even SQL Server ;-). So 'David
Kaye's scenario is plausible. Besides, the table will only get
bigger . I suspect that in the absence of, say, SQL Server
specific optimization or taking advantage of multiple cores, that the
conventional wisdom that moving to a SQL Server backend will not, per
se, speed up a database is likely accurate.

J. A. Fortune
(e-mail address removed)

 

[Bob Quintal]

BTW, don't get your knickers in a knot. David did not "accuse
you" of not taking advantage of indices; [....]

David Fenton is an asshole from another newsgroup. He looks for
any way to berate me, so his response was no surprise to me. I've
seen his posts for more than a decade.

Don't feel like the Lone Ranger, David. Fenton's knowledge is
deep, his mind is quick, and his tact and gentleness are
imperceptible.

What you been smokin'?

Fenton is a pompous, arrogant clown. He called me an asshole and
plonked me for defending the use of natural keys in tables.

 

[John W. Vinson]

What you been smokin'?

Fenton is a pompous, arrogant clown. He called me an asshole and
plonked me for defending the use of natural keys in tables.

That's what I was trying to say, more gently. He's arrogant, tactless, and
rude. Perhaps unfortunately he's also knowledgable and often correct.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David W. Fenton]

(e-mail address removed) (David Kaye) wrote in

BTW, don't get your knickers in a knot. David did not "accuse
you" of not taking advantage of indices; [....]

David Fenton is an asshole from another newsgroup. He looks for
any way to berate me, so his response was no surprise to me. I've
seen his posts for more than a decade.

On the contrary, because of the past history we have, when I
encounter your posts here, I look for every opportunity to support
whatever it is you have to say that I find has merit.

In the present case, you haven't exactly posted wrong information,
but you seem unable to admit that your original solution to the
problem was not optimal. I didn't say you were wrong, only that
your solution had major downsides.

But it's clear you won't take YES for an answer, as was demonstrated
in the first thread in an Access newsgroup I encountered you in:

http://tinyurl.com/2afx9a8 =>
http://groups.google.com/group/microsoft.public.access/browse_thread/
thread/639e3adf1fb53619/5209ee9a898d480d

In that case, you completely flew off the handle for no reason
whatsoever -- I was saying YOU WERE CORRECT in your advice, and you
managed to get insulted by that.

With that kind of reaction, what can I possibly ever do to not
offend you?

 

[David W. Fenton]

BTW, don't get your knickers in a knot. David did not "accuse
you" of not taking advantage of indices; [....]

David Fenton is an asshole from another newsgroup. He looks for
any way to berate me, so his response was no surprise to me.
I've seen his posts for more than a decade.

Don't feel like the Lone Ranger, David. Fenton's knowledge is
deep, his mind is quick, and his tact and gentleness are
imperceptible.

What you been smokin'?

Fenton is a pompous, arrogant clown. He called me an asshole and
plonked me for defending the use of natural keys in tables.

Please cite the URL for the post where I plonked you. If you
actually read the thread, you'll find that the discussion had
NOTHING to do with natural keys (it was a thread about "magic date
values", where you gave correct advice that they were a bad idea,
and I seconded your good advice, and then you got mad at me for, um,
er, something).

I plonked you because you had a hissy fit for no reason whatsoever,
except that you were bringing inapplicable history from a completely
different newsgroup to the discussion. I plonked you because you got
offended over the fact that I was SUPPORTING your good advice.

With you, it's heads you win, tails I lose, so I don't think there's
much point in my reading your posts (with the exception of threads
where you're replying to my own posts).

 

[David W. Fenton]

Please cite the URL for the post where I plonked you.

OK, my bad. I wasn't reading carefully. I thought I was responding
to David Kaye.

I have no idea why I plonked Quintal, but I'm sure it wasn't merely
because he chose to defend natural keys.

But you may have the last word.

 

[David Kaye]

What you been smokin'?

Fenton is a pompous, arrogant clown. He called me an asshole and
plonked me for defending the use of natural keys in tables.

Uh, he's on your side. He said,

Tact and gentleness are IMPERCEPTIBLE -- another word for non-existent.

 

[Bob Quintal]

OK, my bad. I wasn't reading carefully. I thought I was responding
to David Kaye.

I have no idea why I plonked Quintal, but I'm sure it wasn't merely
because he chose to defend natural keys.

************************
from http://www.rhinocerus.net/forum/databases-ms-access/420852-
simple-database-layout-am-i-right-track-3.html

My answer:
If the fields have defined joins from the relationships tool, they
don't need to be created in the query. If they need to be toggled
to a different type, I just click to SQL View and replace the word
inner with left or right, or left to inner as applicable. Your
issues with joins are more psychological than real.

his frustration boils over:
That's a very assholish thing to say. Multi-joins are more
complicated to work with, even if *you* consider that complication
insignificant.

His reply to Mr Vinson:

No, we're not. I just killfiled Bob because he's not an
intellectually honest debater, as proven by his long post minimizing
the real objections to multi-column keys as merely psychological.
************************

But you may have the last word.

Fenton's psychological problems extend way beyond his refusal to
entertain natural keys.

 

[David Kaye]

On the contrary, because of the past history we have, when I
encounter your posts here, I look for every opportunity to support
whatever it is you have to say that I find has merit.

Okay, that's a fascinating take on the situation. Given that it's hit me out
of the blue, I'm willing to consider it the next time such a situation comes
up.

In the present case, you haven't exactly posted wrong information,
but you seem unable to admit that your original solution to the
problem was not optimal. I didn't say you were wrong, only that
your solution had major downsides.

Okay, I'll go with that. The left-side function definitely has drawbacks, but
I didn't see how it would affect a standard (aka small) Access database.
However, on second thought, people here appear to be straining Access to the
max, so I probably shouldn't have suggested the function in the first place.

In that case, you completely flew off the handle for no reason
whatsoever -- I was saying YOU WERE CORRECT in your advice, and you
managed to get insulted by that.

In the case in point I was reiterating what I had said and you got pissy
because I did so. The *reason* I reiterated was because with so many posts
and the fact that we're both David, it can be very confusing to the casual
observer who is saying what. I mention the casual observer here because I
know that Usenet is read by far more people (maybe thousands of times more)
than the person who initially asked the question. So, when I restate
something (you might call it restating the obvious) it's to clarify as much as
I can, given the pitfalls of the medium.

Also, in that particular case the date field idea was both bad programming
practice *and* error-prone as well.

With that kind of reaction, what can I possibly ever do to not
offend you?

Just be nice to me, that's all I ask.

 

[Bob Quintal]

(e-mail address removed) (David Kaye) wrote in

Uh, he's on your side. He said,


Tact and gentleness are IMPERCEPTIBLE -- another word for
non-existent.

Rereading, I see that I mistook the word imperceptible for the word
impeccable.

My apologies to John.

However that leaves the statements of Fenton being deep and his mind
being quick open for debate.

 

[David Kaye]

However that leaves the statements of Fenton being deep and his mind
being quick open for debate.

I happen to agree that DF is of deep and quick mind.

 

[Access Developer]

David Fenton is an asshole from another newsgroup.

Are you under the impression that people should post in only one newsgroup,
David?

He looks for any way to berate me, so his response
was no surprise to me.

It is my observation that David "does not suffer fools gladly". Perhaps you
should carefully examine your posts in this thread to see if that might be
the reason you felt his sharp words. I've never observed him "stalking"
anyone in order to "berate" them.

I've seen his posts for more than a decade.

Then you should know, by now, that David is very knowledgeable, that he has
provided excellent assistance to uncounted numbers of newsgroup
participants, that he can have a sharp tongue, and that (as I observed
above) he does not suffer fools gladly.

Larry Linson