How to keep Queries Organized ?

[Will]

Keeping Queries Organized is getting more difficult on my project.
Ditto for Forms

Yes, I can create Groups but then what about the one or ones you forgot to
put into a group...

How do you check?

This gets complicated as you are working fast and creating stuff on the fly
as you need it...

It would be nice if you could manage Tables, Queries, Forms & Macro's like
you do files in folders.

Is there any possibility that Microsoft may add such capibility to Access?

Are there any other tecniques for managing the growing number of objects as
projects grow?

Maybe I've missed some capibility already built into the program.

thanks for any comments.

 

[Craig Alexander Morrison]

Name your objects so that they group alphabetically.

Name forms so that subforms are listed close by and after the main form,
name queries that return data for the forms or the controls on the forms in
accordance with the form name and the control name.

Try not to design any tables on the fly, try to design your database
carefully at the outset, get this wrong and managing your other objects is
the least of your problems.

Consider whether or not you should have more than one application if the
features being added are many and can be logically grouped.

For example many Insurance Systems we have developed are implemented as 3
distinct applications for the Underwriting, Claims Handling and Accounts
departments. This approach will of course demand that you maintain the
central database tables in a seperate MDB file (or DB2/SQL Server database).

Try to avoid the use of Macros completely, designate a form to load at the
start of your application to get things rolling. Macros can generate errors
that cannot be handled so your application will go up in a puff of smoke.
Users do not seem to like that. (g)

Just a few thoughts. I actually do not use the Groups functionality as I
have been using Access since it first came out and we did not have that
luxury so we had to name things in such a way as to group them.

BTW If you log on via the Microsoft website you can post this as a
suggestion on which people can add their support (or not).

If Track name AutoCorrect actually worked you could also use the Object
Dependencies function.

 

[Al Camp]

Will,
Some points I'd like to offer...
1. Saved Queries - I try to never "save" a query if I can help it. You
can build a query within a form or report without saving it to a named,
saved query... I refer to it as an "internal query". The RecordSource query
for a form or report can be just an SQL statement, rather than the name of
an external saved query.
Some will argue that there are speed issues involved in that method, but
I've built some very large apps, and... given a "reasonably" powered PC...
that problem has never been a serious issue. 99% of the time, the network
is a more critical factor in poor performance than internalizing queries.

2A. Naming - IMHO this may be the most important factor in query (or
any objects in an app) organization. Many folks try to avoid typing long
object and control names in code, so they make thier names abbreviated and
cryptically short. In no time at all they can't tell one form/query/report
from another. Use names that makes sense, make them as long as they need to
be to differentiate themselves from other queries, and... choose your words
carefully.

3. Develop a naming technique that you understand, others can
understand, and stick to it.

4. If you have a complicated form with several subforms, queries, and
associated reports, try to use a common naming convention within that group
of objects... or within that area of "functionality."
frmInventory frmPartsInventory
frmPartsLoc frmPartsInventoryLocationSub
subDisburse (vs.) frmPartsInventoryDisbursementSub
frmReceive frmPartsInventoryReceivingSub
qryParts qryPartsInventory
qryDisbursement qryPartsInventoryDisbursement
patReceiving qryPartsInventoryReceivingPThru
etc.... etc...
That will help to "group" related queries in the query window because of
the inherent name sorting. I know it's not standard Access naming
convention, but I stick with my personal method, and... most other
programmers would understand those names.

I'll be very interested as to what other folks have to say on this
subject. It's a real opportunity to hear others opinions on "meat and
potato" Access issues.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Guest]

Hi Al,

I agree with your suggestions. In years past, I used to always save
recordsources for forms and reports as separate queries, due to concerns
about speed. I now believe those concerns were unfounded. You might even have
a faster application by using SQL statements instead of saved querydefs,
since this forces the query to be recompiled each time. If a user is lax on
running compact and repair, a saved querydef may actually be much slower if
it is not properly optimized for the data at hand. Besides, saving the
recordsources internal to the form or report makes them more portable. You
can import such a form or report into another database, without having to
worry about dependent objects.

One thing to be aware of, however, is that the combo box wizard will not
work unless the recordsource is a table or saved query.

Will: A very nice tool to acquire, if you can afford it, is Total Access
Analyzer from FMS. This product will help to identify unused objects, among
it's many other capabilities.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________



:

Will,
Some points I'd like to offer...
1. Saved Queries - I try to never "save" a query if I can help it. You
can build a query within a form or report without saving it to a named,
saved query... I refer to it as an "internal query". The RecordSource query
for a form or report can be just an SQL statement, rather than the name of
an external saved query.
Some will argue that there are speed issues involved in that method, but
I've built some very large apps, and... given a "reasonably" powered PC...
that problem has never been a serious issue. 99% of the time, the network
is a more critical factor in poor performance than internalizing queries.

2A. Naming - IMHO this may be the most important factor in query (or
any objects in an app) organization. Many folks try to avoid typing long
object and control names in code, so they make thier names abbreviated and
cryptically short. In no time at all they can't tell one form/query/report
from another. Use names that makes sense, make them as long as they need to
be to differentiate themselves from other queries, and... choose your words
carefully.

3. Develop a naming technique that you understand, others can
understand, and stick to it.

4. If you have a complicated form with several subforms, queries, and
associated reports, try to use a common naming convention within that group
of objects... or within that area of "functionality."
frmInventory frmPartsInventory
frmPartsLoc frmPartsInventoryLocationSub
subDisburse (vs.) frmPartsInventoryDisbursementSub
frmReceive frmPartsInventoryReceivingSub
qryParts qryPartsInventory
qryDisbursement qryPartsInventoryDisbursement
patReceiving qryPartsInventoryReceivingPThru
etc.... etc...
That will help to "group" related queries in the query window because of
the inherent name sorting. I know it's not standard Access naming
convention, but I stick with my personal method, and... most other
programmers would understand those names.

I'll be very interested as to what other folks have to say on this
subject. It's a real opportunity to hear others opinions on "meat and
potato" Access issues.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

_________________________________________


Keeping Queries Organized is getting more difficult on my project.
Ditto for Forms

Yes, I can create Groups but then what about the one or ones you forgot to
put into a group...

How do you check?

This gets complicated as you are working fast and creating stuff on the
fly as you need it...

 

[Al Camp]

Tom,

One thing to be aware of, however, is that the combo box wizard will not
work unless the recordsource is a table or saved query.

I never use wizards, so that's not a serious concern... but...

Along your line of thought though... if a query isn't saved, the query
field "properties" (I'm familiar particularly with Format) are not saved
either. In A97 any formatting through the query property box works only
while you run the query from design mode...after you exit the query design,
the formatting vanishes.
In A2003, the Decimal Places property doesn't even show until the query
is saved.

On those occaisions, I just use the formatting in the grid...
FormatField : Format([MyNumField],"#.000")

But, that's not too serious an issue either... so using "internal"
queries whenever possible seems still the way to go.

Good discussion...
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions