Re: Access 2007 Technical Questions

Hi, Klatuu.

You and I disagree on only one point - Validation Rules.

VBA is much more flexible than using Validation Rules, but I prefer letting
the database engine do the work whenever possible, so if a CHECK constraint
or a Validation Rule on a table or column can get the job done, that's my
first choice. But when I can't get these CHECK constraints and Validation
Rules to do everything I want, I write code in the event procedures to
validate the data input.

If I knew ways to control the
behaviour of the application when a rule is violated

About the only control you have with a Validation Rule is the Validation
Text you use to tell the user what's going on. Other than that, your error
handlers in the appropriate event procedures would need to handle what
happens when the Validation Rule is violated, which means "On Error Resume
Next" probably isn't going to be enough.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C148F4DB-FCDC-4A0B-953A-36F719C82657@xxxxxxxxxxxxxxxx
Good comments, Gunny.
You and I disagree on only one point - Validation Rules.
I find them restricitve and prefer to do my own validation.
My prejudice is not set in concrete. If I knew ways to control the
behaviour of the application when a rule is violated, I could be convinced
to
experiment a bit more.

"'69 Camaro" wrote:

Hi, Ronald.

Most times, you want
the validation rules to run, but there are a minor few possible reasons
why
you may want to bypass the validation rules such as user clicking on
the
"Help" command button (if there is one on the form

My validation rules don't run when I invoke online help. Please show
your
code and describe what happens, and perhaps someone can help you on this
problem.

resetting the form

Use Me.Undo to reset the form.

or
even backing out of the form

Hit the <ESC> key twice.

However, when the user goes to click on a command button
on the form, all validation rules on the active control are processed,
and
if any of those validation rules causes the control to be in error, the
Enter Event doesn't even take place on the command button cause the
focus
is
forced to stay on the active control rather than to transfer to the
command
button.

Have you tried putting your validation code in the active control's
OnBeforeUpdate( ) event? You can cancel the update, use a MsgBox to tell
the user about the problem, and the focus will remain on the active
control
whenever the value in the active control doesn't meet your criteria, such
as
when the user tries to leave a required text box empty.

To get around this issue, I had to mimic the CausesValidation Property
and
Validation Event as has been setup in VB6, which meant in order to get
around this issue, I had to UNBIND every single form and control else
this
wasn't going to work.

Wow. That would be the hard way to do it. Hitting that <ESC> key or
writing VBA code to validate the data, and canceling the update if any
problems are encountered, works for me.

It took me a total of 3 full months to do the R&D to
get around this issue.

Wow! You really should have posted some questions in the Access
newsgroups.
We would have gotten you straightened out that same day, probably within
an
hour or two of your post.

Initially, I was using DAO to get around the issue above as in order to
get
data to the any of the BE databases with all forms and controls unbound

Suit yourself if you want to use unbound forms, but that's the beauty of
bound forms in a RAD environment: the developer doesn't have to do a
bunch
of programming to provide data validation, maintain data integrity,
prevent
record locking, et cetera.

can
only use ADO or DAO programming language to manipulate the data.

Well, database developers use a powerful programming language to
manipulate
data. It's fast and reliable. It's called SQL. It's not a common need
to
use DAO or ADO Recordset cursors, unless the data manipulation is
exceptionally complex. I get the impression you're using cursors to do
all
your updates, not queries or bound forms.

However,
there were instances that I found, when the code initially put the
recordset
into Edit Mode, even with the variable set at the module level of the
form,
before it even reaches the Update Method, the EditMode property no
longer
has it's enum value that it suppose to have. This more or less has
rendered
DAO programming useless.

Why use a Recordset to edit data? Why aren't you using a query or a
bound
form? If you used either of these, there would be no reason to check
whether the record was currently being edited, or the record was being
added, et cetera. If you use optimistic locking and there's a record
lock
with a bound form, Access will let you know. Likewise with update
queries.
If there's a problem updating a record, Jet will let you know.

I get the impression that you are a programmer who is experienced in
another
field but has migrated to databases, where the solutions you come up with
use code instead of the database engine's functionality. Using cursors
and
unbound forms are typical symptoms of a programmer with previous
experience
on non-data applications. If this is the case, then I recommend that you
either get training or find an experienced Access developer to show you
the
techniques we use for reliable RAD in Access.

Issue 3: ADO programming doesn't allow for DynamicCursor Keyset
against a
Jet Engine (Critical)

This one is actually documented, so I ask the question, how is one
suppose
to setup a recordset using a Jet Engine to be able to see changes
within
it
when changes do take place?

It's somewhat rare that an update query is too complex to use SQL and
needs
VBA to customize the query. If you're using cursors for record
adds/updates/deletes for more than about 5% of your queries, then you are
probably doing this the hard way.

Without this capacity, it has rendered ADO
programming useless.

DAO, the preferred data access library for Jet, allows one to see changes
in
the Recordset. The DAO library has been available for every version of
Access since at least 2.0 (and possibly 1.0 and 1.1 too, but I can't
verify
that). If you're using ADO on a Jet database, it's because ADO provides
some functionality you need that DAO doesn't. dbSeeChanges isn't one of
them.

Does Access 2007 address this issue?

Access 2007 provides bound fields, and allows queries instead of cursors
for
updating data, and also has dbSeeChanges in the DAO library. The ADO 2.8
library doesn't have dbSeeChanges, so it's not the library you're going
to
be using if you absolutely must use a cursor to update records.

it's the above 3 issues that has more or
less turned me against from using Access as with those issues above,
Access
is not a worthy DB program for a multiple user environment.

Access is a multiuser database, so it works great in a multiuser
environment, provided the database application was not designed for a
single
user environment instead, which is a very common mistake with
inexperienced
developers.

I was going to
have my own financial stuff stored within Access, and then allow my
wife
some capacity to record some things, but with the issues above, it's
not
stable enough to have something like that setup.

First, don't store data in an Access database if it needs to be secured.
Second, if you find that Access isn't stable, then either Access isn't
installed correctly, or hasn't had all of the service packs applied, or
the
developer hasn't designed the database application correctly. What
instability issues or errors are you experiencing?

Instead, I am still using
Excel for all of my financial planning and tracking stuff, which that
has
gotten to be so large, I have already broken that 1 file down into 4
files
and it's about to get split out to a 5th file

Wow! That's doing it the hard way. How do you prevent duplicate
entries?
How do you prevent insertion anomolies? How do you prevent deletion
anomolies? How do you prevent redundancy between the multiple files?
How
do you do data validation? Answer to all of the above: you don't. I'd
recommend storing that data in a relational database, not a bunch of
spreadsheets.

Issue 4: Listbox properties doesn't update until it has lost focus

That's how this control is designed to work.

records wouldn't be updated properly. To get around this issue, I
had to create a class module
<SNIP>
Of course, this meant that I had to learn every single behavior of the
listbox from using both methods, mouse and keyboard.

It's not a bad idea to learn the tool you are using, but you seem to
write a
lot of unnecessary code to "get around issues." Have you tried a timer
event, so that you can read the list box's properties with VBA code
almost
immediately after the list box has lost focus?

Not only that, but I
found in few circumstances with the right combination of the keyboard
and/or
mouse actions on the listbox, the listbox itself freezes up and does
nothing
else, which the only way to get away from that issue has been to back
out
of
the form, and then go back into the form.

I've never seen this behavior in any version of Access, so I can't
comment
on it.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


"Ronald Dodge" <ronald.dodge@xxxxxxxxxxxxxx> wrote in message
news:ewga27jhGHA.1612@xxxxxxxxxxxxxxxxxxxxxxx
I have exerted a lot of energy in the past testing things out in Access
2000/2002/2003, and here's the issues that I have faced.


Issue 1: Error checking isn't user friendly (Very Critical)

The biggest issue that is the very reason why I have had so many other
issues in Access is the fact that Access is not very user friendly when
it
comes to error checking, especially for mouse users. Most times, you
want
the validation rules to run, but there are a minor few possible reasons
why
you may want to bypass the validation rules such as user clicking on
the
"Help" command button (if there is one on the form), resetting the
form,
or
even backing out of the form as 3 possible reasons why to bypass the
validation rules. However, when the user goes to click on a command
button
on the form, all validation rules on the active control are processed,
and
if any of those validation rules causes the control to be in error, the
Enter Event doesn't even take place on the command button cause the
focus
is
forced to stay on the active control rather than to transfer to the
command
button.

To get around this issue, I had to mimic the CausesValidation Property
and
Validation Event as has been setup in VB6, which meant in order to get
around this issue, I had to UNBIND every single form and control else
this
wasn't going to work. It took me a total of 3 full months to do the
R&D
to
get around this issue. Therefore, I ask, "Does Access 2007 have the
CausesValidation Property and Validation Event, that is comparable to
VB6's
property and event?"


Issue 2: EditMode within DAO programming loses it's value for no
apparent
reason (Critical)

Initially, I was using DAO to get around the issue above as in order to
get
data to the any of the BE databases with all forms and controls
unbound,
can
only use ADO or DAO programming language to manipulate the data.
However,
there were instances that I found, when the code initially put the
recordset
into Edit Mode, even with the variable set at the module level of the
form,
before it even reaches the Update Method, the EditMode property no
longer
has it's enum value that it suppose to have. This more or less has
rendered
DAO programming useless.


Issue 3: ADO programming doesn't allow for DynamicCursor Keyset
against a
Jet Engine (Critical)

This one is actually documented, so I ask the question, how is one
suppose
to setup a recordset using a Jet Engine to be able to see changes
within
it
when changes do take place? Without this capacity, it has rendered ADO
programming useless. Does Access 2007 address this issue?

There is a 4th issue as well, but it's the above 3 issues that has more
or
less turned me against from using Access as with those issues above,
Access
is not a worthy DB program for a multiple user environment. I was
going
to
have my own financial stuff stored within Access, and then allow my
wife
some capacity to record some things, but with the issues above, it's
not
stable enough to have something like that setup. Instead, I am still
using
Excel for all of my financial planning and tracking stuff, which that
has
gotten to be so large, I have already broken that 1 file down into 4
files
and it's about to get split out to a 5th file with the data grouped in
the
following categories:

Economy (This contains information about the economy including economic
historic data, which impacts inflation, thus also impacts the spending
side
of the equation)

Debt

History (Our own financial history)

Investments (This isn't actually broken out yet, but it soon will be)

Planning and Summary (This file pretty much contains everything else
that
doesn't fit into any of the above categories and currently also
contains
the
investments)


Issue 4: Listbox properties doesn't update until it has lost focus
when
it's allowed to have multiple selections (Moderate)

This issue caused a lot of problems for me as well. Without addressing
this
issue, records wouldn't be updated properly. To get around this issue,
I
had to create a class module and put in a set of codes to more or less
be
able to record all of the various behaviors, just so as I can get an
accurate picture of what's taking place with the listbox. All of the
things


.