How to Keep Your Databases from becoming Overwhelming! 22

[SeeJane]

Another trick is to include carriage returns in SQL statements like this.

strSQL = "SELECT Field1, Field2" & vbCrLf _
& "FROM Table1" & vbCrLf _
& "WHERE Condition1;"

This makes the SQL read far more easily in the immediate window (no long strings of text continuing off the screen) and eliminates the need to put a space before each new statement (like " FROM&quot.

I can't clain credit for the idea (saw it somewhere or another), but it has saved me much aggarvation.

CJ

 

[LittleSmudge]

To build on JimHorton's comments earlier - the use of reserved words and B!@s7e8 space characters in object names.

I already have an FAQ on the Relationships and tables board that goes into some details on that issue.

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=2190&CFID=83842124&CFTOKEN=82525635

G LS

 

[LittleSmudge]

One more design tip - to do with naming conventions that really builds on JPeter's original post.

Table names always start with 'tbl' eg. tblContracts

Then the primary key(s) are always constructed in the form of the table name without the tbl prefix but with 'Id' appended. eg ContractsId

Nothing stunning in that one I know - most people do something similar - but it is the next step that's novel.

In another table that refers to it ( On the Many end of the link ) the Foreign Key DOES NOT have the same name as the Primary key it links to. Instead replace 'Id' with 'Ref'

Eg

tblContracts        
ContractsId .1. .    
                |     tblSuppliments
                |     SupplimentsId
                | .m. ContractsRef

In this way, when you are maintaining or debugging code you instantly know whether your code is refering to the PK or FK, and you know which PK in which tbl the FK refers to.


G LS

 

[HFloyd]

Hey, great forum!

Here's something I've figured out recently:

Of course we all use the "frm" prefix, right? But take it a step further -

Subforms: Often you end up with a bunch of subforms - often with multiple subforms on the same form. Try this:

If you have "frmSettings", the subforms should be named like this:
"sfrmSettings-Categories"
"sfrmSettings-UserPrefs"
etc.

The advantages are:

1)All the forms sort alpha in the DB window, so all subforms will be separate from all forms, and all the subforms for a certain form will be next to each other.

2)Self-documenting of the purpose of a form - in the name!

The only thing to watch out for is really L-o-n-g subform names, but if you are naming your forms concisely, this shouldn't be an issue.

Also, I like using "-", but you might prefer to use "_"

Heather

http://www.floydinnovations.com

 

[johnnymac43]

Just a few of my pet hates, which I find (or find missing) in lots of Access applications:

> If you must use Access symbols for command buttons, use them only for their designated purpose, eg. Don't use the 'Close Form' symbol (door) for 'Quit Application', or 'Find Record' (binoculars) for 'Open Form'.

> Put some sort of Version information in the header of your main form, and maintain it religiously.

> If you use switchboards (great for small apps) set them up as a properly structured menu tree, and call the main switchboard frmMainMenu.

> If you are building lots of queries to be executed in sequence on the same object (either manually or in code) use an ascending numeric somewhere in the name, eg. qryUpdateAddress1, qryUpdateAddress2, etc. Make sure that the numeric has sufficient size (leading zeros) to result in the desired display sequence being maintained.

> If sequential actions are to be executed on different objects, put the numeric before the name of the function, eg. qry001NameErrors, qry002AddressErrors, etc.

> Use Message Boxes to advise the user of the progress of sequential actions being executed in code.

> If you are linking to tables with names that are meaningless or nearly so, put something meaningful in the Description field. If you want only a List view, rename the file after it is linked, eg. ACCOMSTF (Staff Accommodation).

Enough for now!

John

 

[KornGeek]

The best tips I have seen are the Ten Commandments of Access.

http://www.mvps.org/access/tencommandments.htm

Some of the most important:

4) Thou shalt write comments in your procedures and explain each variable.
8) Thou shalt not copy and paste other people's code without at least attempting to understand what it does.
10) Thou shalt back-up thy database faithfully, working not on thy Production Database, but on the Prototype Copy, as it is right and good to do.


Sleep is for people with no caffeine.

 

[sathandle]

I'll add my 2 cents worth...

1. When developing an application prior to splitting into BE & FE - name your tables with the following pre-fixes...

tblAdmin...
tblData...

It makes splitting MUCH easier.

2. Each day start a new database that is a compacted and repaired copied of the previous day's work. (Saved me a couple of times... (this week already - and it's only Monday)) - Keep together in a month folder if there are a lot of changes. (Jan 03, Feb 03, etc.)

3. The end user is the most important. Make their life as simple as possible.

4. If you have several combo boxes for one form, put all of their items (the info that drops down...) into one table and name it tblAdminCustomer. Have 3 columns in the table - ID, Value, Attribute
ID = Value stored in tblDataCustomer
Value = info to have dropdown (i.e. Brown, Blue, Green, Hazel, Other)
Attribute = related combo box (i.e. Eye Color). In cboEyeColor set the data source to an SQL statement...

Advantage - one 'major' table per form instead of 1 table for each combobox.

Happy VBA all!

SAThandle





definition of 'less behind': "not fully caught up, digging out slowly, one-week delay to "The IT hit the fan."

 

[sha76]

Just like to add to sathandle's point about end users:
These are the people who are going to be using your database every day, they often benefit most from the easiest things (input fields in the same order as the data they're adding, logical tab order).
Talk to them! Have them test your database. They're experts in their jobs (OK, there are some exceptions to that one!), you are not! The better the relationship you build with them, the better the feedback you get & it's not always just about cosmetic details. It also makes people alot more open to any major changes you have to make.
I realise this isn't always easy, particularly if you're doing a contract for another company, but it's definitely worth the effort.

Sharon

 

[HenriM]

MessageBoxes.

Just a little tip when you want to display a long message which can become difficult to read. You can space it out by using the carriage return (CHR(10) in your code:-

'Msg("IMPORTANT NOTICE" & CHR(10)&CHR(10)_
&"If you want to create an Access Database _
give yourself lots of time." &CHR(10)&CHR(10)_
& "It's a bit like knitting treacle."

I am sure that there must be ASCII codes to centre the "IMPORTANT NOTICE" and justify the text but I haven't found them yet.

HenriM

 

[chrissie1]

about the ned users. Dont ever think they are good at access otherwise they would have written the database themselves. Always presume a user to be the biggest idiot around...

Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[IrishJim]

On the same track as Johnniemac43--
About half of my jobs are sequential-type projects (Extract data from corporate databases, join, tweak, print or export)
In these cases, I have found it invaluable to have the sequence of queries in the name (qry01_Pull_Provider_Ids, qry02_Get_Names, etc.)

Secondly, if they are make-table queries, I name the table after the query:

qry01_Pull_Provider_Ids ---> tbl01_Pull_Provider_Ids
qry02_Get_Names         ---> tbl02_Get_Names

and so on.

It makes it easier to instantly know which query made which table! Also easier to plop them into a macro, in the right order. Invariably it will come back to me later, when my brain isn't on the same track...I can fix problems, re-do the rest of the job the same way as before.

 

[KornGeek]

Chrissie1,
I'm a little confused by your statement:
"Always presume a user to be the biggest idiot around, even if he is the president of the US."

Shouldn't that be "esepcially if"? Maybe it's just me.

I have to agree with those of you who say it's worthwhile to spend some time with the end users. I recommend cobbling together an Alpha version ASAP just to create a feel for how you envision it to work. It's OK to have many bugs and even major bugs at this stage. Just get it thrown together and get some end-user feedback on it. This way, you can be sure you're heading in the right direction from the start, instead of using the Ready, Fire, Aim approach.
Sleep is for people with no caffeine.

 

[LittleSmudge]

HenriM's comment recently,
'Msg("IMPORTANT NOTICE" & CHR(10)&CHR(10)_
&"If you want to create an Access Database _
give yourself lots of time." &CHR(10)&CHR(10)_
& "It's a bit like knitting treacle."

Can be done neater and easier to read by using the VB constant vbLf , as in :-

MsgBox "IMPORTANT NOTICE" & vbLF & vbLf _
     & "If you want to create an Access Database " _
     & "give yourself lots of time." & vbLf & vbLF _
     & "It's a bit like knitting treacle."

Remember that if you are using a VB constant in a VB string ( Eg. constructing something to go into a Text Box Control on a form rather than a message box ) then you'll need to use vbCrLf rather than vbLF

vbLF = Chr(10) Line Feed Only

vbCrLf = Chr(13) & Chr(10) Carraige Return + Line Feed


'ope-that-'elps.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[oharab]

If you are using Access 97, then you can format the msgbox to look more like the Access Error MsgBoxes using @

strMsg = "Error " & err & " has occurred at line " & erl
strMsg = strMsg & "@" & err.description
strMsg = strMsg & "@Click Yes to continue or No to try again"
if MsgBox(strMsg, vbCritical + vbYesNo, "fSomeFunction&quot = vbNo Then

will give you

Error 3222 has occurred at line 104
Some kind of error description
Click Yes to continue or No to try again


Unfortunately this doesn't work in 2000 or 2002 :-((

Also if you put a number at the beginning of each line, the erl function will give you the number of each line. There are addins out there wihch will take care of that for you.

Ben ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[GummowN]

My naming convention goes beyond the norm for queries, they are structured as:
type_dept_uniqueID_Description_Developer_LinkIDs

eg
qsel_Mgt_00045_TestQuery_NG_00046_00047

This mean that it is a select query, for the management department, is query number 45, <name>, written by NG, and is used in queries 46 and 47.

This is all managed from a form. If a query is editted to link into another query, it will update all other queries in the database with the new name. IE if query 45 above was only linked to 46. And you created query 47 and in that linked to query 45, when you saved query 47, it would update query 45 name to include link to 47, then update the SQL for queries 46 and 47 so that they reference the new name and not the old name.

This is crowned by a treeview on the form which displays how the queries all link together. A top level query will have the linkID the same as the uniqueID.

It took some work sorting it out, and still need polishing but it saves a lot of hassle when a query fails and you want to troubleshoot from the base up.

 

[Garridon]

And this one--

If your database contains fields for different departments, use generic department names (i.e., Department A) for field and table names, not actual names. That way, if your company does a reorganization or renames their departments, you don't have to fix it. A commented guide to the corresponding departments can be commented in one of the modules.

 

[chrissie1]

shouldnt we change the title of this &quot;tip&quot; to how to keep your forumtip from becoming overwhelming Christiaan Baes
Belgium
&quot;What a wonderfull world&quot; - Louis armstrong

 

[GDGarth]

Great tips!

Here's a couple more:

Automate database compacting, so it actually gets done on a regular basis.



Before you compact, save multiple copies of both front and back end.


I've seen the tips on experimenting with a copy of the database, and I agree. I take it one step further, and make a copy of every form, query, etc. before I make changes. Then, if it doesn't work, I can just delete the object, and rename the backup to the original name.

 

[Darrylles]

Hi,

My tuppence-worth, if I may.

Some topics already touched-upon by: ClydeDoggie, RedBird, LittleSmudge....
[tt]
*) NEVER use macros - unclear, hard to maintain, document
and debug (I think Clyde advised using macros, although
he also advised using VBA). Macros seem to produce
masses of duplication (as opposed to VBA functions with
arguments). If you don't know VB code, then learn it
quick-style, it'll save a LOT of time.
*) Document EVERYTHING in summary. This includes all table,
form, report descriptions. Include what other objects
that this object refers to also.
Add a description header to all procs/functions, and
comment code. Even if you think it's simple now, when
you come back - you'll be stumped for a while.
*) With Proc's/Functions; if you look at a block of code's
action within a large function and you can imagine a name
for what it's doing, then make it a function in it's own
right and call it from the main.
*) With Proc's/Functions; always think 'generic'. I.e. is it
possible that this function MAY be reqd. by another form
(or other function)? If so - invest a little more time
and use arguments to pass object names rather than 'hard-
coding' them within the code.
*) ALWAYS diagram table relationships - it makes it simple
for a new developer to pick up what the db is about in a
shorter space of time. (We all immediately
understand 'pitchers').
*) Naming conventions are really underused/undervalued,
more because of laziness than anything. (I've 'knocked'
stuff together because my boss was on my back), but then
I've gone back and renamed everything - probably
doubling the time taken.
tbl,frm,rpt,qry,lbl,txt,chk,opt,cmb,lst,cmd all
prefixes, all obvious. Whatever your preference - use it
and stick to the same throughout the app.
For subform names, I use the _sub suffix, but always use
the Parent form name as a prefix. e.g. Parent =
frmFormA, subform = frmForma_Formb_sub. This way, all
sub-forms related to the parent appear under the parent
(if alphabetically sorted).
*) No matter what the pressures; before hitting the
keyboard - visualize not only the current problem /
solution, but also what problems or possibilities it may
lead to. The solution will work as planned, but if you
had thought about it, you could also have saved a lot of
work for the future by pre-empting what else will be
asked for when it's completed by designing your solution
with the 'future possibility' in mind.
Remember - you give the boss the solution he wanted, he
then has his 'pitcher' and then immediately visualizes
what else he can have stemming from this. He usually
can't visualize more than one step at a time ( ;-) ).
[/tt]
These are but a few 'standardisation' ideas (some already added), but I know that I have a lot more, and I will no-doubt add other posts. I know that the majority of you will also have a lot more.

MS should already have compiled such a list - depending on whether user or developer, but it hasn't and that's why the thread owner (JPeters) started this thread.

I think that you will all feel that this 'list' of 'standardized' Access development techniques/tips is not at all efficient or even that helpful. It could take hours to read each and every entry (some advice opposing other advice).

I feel that a website could easily be produced where entries could be vetted, voted on and introduced in categories - standardized Access development tips by category.

If anyone is interested, then please let me know.

If Tek-Tips can see a way of doing this within the Tek-Tips site - (a Tek-Tips/MS Access/ sub-site) even better.

Kind Regards,

Darrylle &quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; darrylles@totalise.co.uk

 

[Tiggertoo]

I have an old database (one of my very first) that had lots of reports. In design view I put label boxes and typed necessary information to help me remember what was needed or done. Then I opened the labels properties and changed visible to &quot;no&quot;. The info is there for your to read in design but doesn't show in preview or on the printed report. Also works on forms.