Database Analyzser

[Mark A. Sam]

Does anyone have experience with the database analyser in A2002/2003? I
have played with it and was surprised that it actally makes the changes,
such as adding indexes, but is it a good or bad idea to use this tool?

Also, one of the suggestions that it has made was to remove modules on forms
that have no code. I have never heard of removing a module. I thought
maybe it changed the HadModule propety to no, but couldn't find that
property on A3003 forms. What doesn removing a module do and can it be
restored?

Thanks for the help and God Bless,

Mark A. Sam

 

[Allen Browne]

Treat the the analyzer's recommendations as advice.
It's not always right, but it's useful in getting you to think.

A form or report that has no module is called Lightweight. The actual
performance difference is less than 1/10th of a second to load, so it's not
huge. Nevertheless, saving memory is a worthwhile goal in my view, not to
mention the minimalistic attitude that the less that's loaded, the less that
can go wrong. In production, it's almost never possible to have lightweight
forms (except perhaps read-only), as you have to handle what the user
enters. OTOH, it's often possible to have lightweight reports just by
reusing a function in a standard module.

I haven't tested Access 3003 yet You should be able to find the
HasModule property on the Other tab of the form's Properties, and yes,
setting that to No is how you can create a lightweight form. There are some
other side-effects in the way you can reference a lightweight form.

 

[Mark A. Sam]

Thank you Allen,

Actually I looked at the analyzer becuase my client wants to up grade from
97 to 2000 and the performance got unbelievably bad when I tested it. A
couple of the main forms have stacks of within subforms, and subforms linked
to subforms and tabs within tabs as well as unidentifyable corruption. He
is upgrading to SQL Server soon, so maybe that help with performance.


God Bless you Allen,

Mark

 

[Dirk Goldgar]

A form or report that has no module is called Lightweight. The actual
performance difference is less than 1/10th of a second to load, so
it's not huge. Nevertheless, saving memory is a worthwhile goal in my
view, not to mention the minimalistic attitude that the less that's
loaded, the less that can go wrong. In production, it's almost never
possible to have lightweight forms (except perhaps read-only), as you
have to handle what the user enters. OTOH, it's often possible to
have lightweight reports just by reusing a function in a standard
module.

Allen, I have a number of lightweight forms in my applications. They're
generally forms that use common logic for validation, etc., so I've
abstracted the logic to functions in standard modules, which are called
directly (as =functionname() expressions from the form's event
properties. True, the majority of my forms need more complex and
form-specific logic than that, but I wouldn't say using lightweight
forms was "almost never possible."

 

[Allen Browne]

That's interesting, Dirk.

Placing the function name directly in to the property is good for many
events. Its limitation is that you can't get the argument for the events.

In any bound form that accepts data, I personally handle:
- the form's Error event, so I need the event procedure to get the DataErr;
- the form's KeyPress event, so I need the KeyAscii value.
The KeyPress is a lazy way of preventing the user from entering characters
that mess up your SQL clauses, such as the pipe character (Ascii 124).

 

[Albert D. Kallal]

Are you running a split mdb?

There is a good number of settings you need to change to get your
performance back.

Turn off things like track-name auto correct. Sub-datasheets etc. Further,
if you are running a split mdb, then keep a connection open. Do the above
will restore your performance back to previous levels.

As mentioned in this thread, use of light weight forms is really not
noticeable, and I would not worry about them.

For fixing performance, and restoring it to 97 levels, check out

Check out:
http://www.granite.ab.ca/access/performancefaq.htm

 

[Allen Browne]

Fair enough.

The pipe character may no longer be relevent in Jet 4, but try this in A97:
? DLookup("MyField", "MyTable", "MyField = '|'")
Naturally enough I didn't want to set validation rules on every text field
of every table.

Regarding the Form_Error, I don't trap every error, but I do dejargonize a
few of the common ones, e.g.:
3314 This is a required field. Make an entry, or press <Esc> to undo.
The idea is to give the user information not only on what's wrong, but on
how to get themselves out of trouble.

 

[Dirk Goldgar]

Fair enough.

The pipe character may no longer be relevent in Jet 4, but try this
in A97: ? DLookup("MyField", "MyTable", "MyField = '|'")
Naturally enough I didn't want to set validation rules on every text
field of every table.

Interesting! I never knew about that. KB article 178070 explains it
for me. It seems not to be a problem in Jet 4, though. But for earlier
versions, how annoying!

I'm not sure which would be more trouble, adding a KeyPress event
procedure to every form or setting a validation rule for every text
field. Probably you use code to add the event proc, or else create
every form by modifying a template. You could easily write a routine to
run through the TableDefs and add validation rules to text fields, and
run that routine once. Or else add the rules to text boxes on forms,
which would be about as easy.

I guess a third approach would be to use a correcting function to wrap
the values when building SQL statements and criteria strings, as
suggested by the KB article. Then, if one were scrupulous about the use
of the fixup function, one would need neither the KeyPress event code
nor the validation rules.

Hmm, it seems Filter By Form is going to be vulnerable to this sort of
problem no matter what you do, if anyone ever filters for the pipe
character.

Regarding the Form_Error, I don't trap every error, but I do
dejargonize a few of the common ones, e.g.:
3314 This is a required field. Make an entry, or press <Esc>
to undo. The idea is to give the user information not only on what's
wrong, but on how to get themselves out of trouble.

Not a bad notion, that. Some of these at least I forestall with a
public function called directly from the form's BeforeUpdate event.

 

[Mark A. Sam]

Albert,

Are you running a split mdb?

Yes. That is why it suprises me that Access adds the indexes to the backend
DB.

There is a good number of settings you need to change to get your
performance back.

Turn off things like track-name auto correct. Sub-datasheets etc. Further,

I have never used subdatasheets and don't pay attention to autocorrect, so I
never considered turning them off. I will investigate that.

if you are running a split mdb, then keep a connection open. Do the above
will restore your performance back to previous levels.

What do you mean keep the connection open?


Thanks for the advice.

God Bless,

Mark A. Sam

 

[Albert D. Kallal]

"Mark A. Sam" <[email protected]

Yes. That is why it suprises me that Access adds the indexes to the backend
DB.

All index stuff is, and will, and should be in the backend file. You can't
create, or set a index in the front end.

What do you mean keep the connection open?

Keeping the connection open simply means that some where (anywhere), the
front end opens a table to the back end and KEEPS IT OPEN while you run your
application. You can accomplish this by just opting a form that is bound to
a table and then minimize it. You can also open a reocrdset in code.
Regardless, the trick is to keep the back end connection opened. (the
performance change is often dramatic...).

 

[Mark A. Sam]

Hello Albert,

I understand that. I just thought it surprising that Access would make the
change to the backend db.

God Bless,

Mark

 

[Mark A. Sam]

Albert,

Are you running a split mdb?
Yes

There is a good number of settings you need to change to get your
performance back.

Turn off things like track-name auto correct. Sub-datasheets etc.

I don't understand this. I could find no info on what these features are
let alone turning them off. (Must be one of Microsoft's best kept secrets
)

Further,

if you are running a split mdb, then keep a connection open.

I don't understand. I am using linked tables.


I am really trying to get my client to upgrade to Access2002, which runs
very well. I hope you can address these questions in the meantime.

God Bless,

Mark A. Sam

 

[Mark A. Sam]

Hello John,

I guess I am a little confused about the open recordset: Here is what is on
the page:

"
Global Recordset which is always open
Alternatively you can create a dummy (test) table in the backend file and
create code in the front-end file which opens a recordset on this table and
persist the recordset until the front-end app is closed. To do so:

Create an empty form. .

Declare a recordset variable in the global declarations section.

In the OnOpen event open a recordset against any table.
"



I don't know if I am to open the recordset against a table link or set a
database variable and set it against that. If it is a table link, is there
a differnce between that and having a form open in the frontend?

ALso, the above says to create a dummy table and open a recordset on it, but
then it says to open a recorset against any table.



Thanks for any feedback.



Mark

 

[Mark A. Sam]

A2k is definately the "Acces of Evil".