How to Keep Your Databases from becoming Overwhelming! 22

[JPeters]

I marked this as a tip, because over time (hopefully) it will become a tip - I'm sure it will.

Any of you ever notice how your databases start relatively small, seem simple enough but after a few weeks and some coding the queries begin to pile up, the modules grow, and there seem to be more and more forms! Sometimes I have so many queries doing different things for different forms that I forget which does what (some of us aren't too hot at SQL coding - or we'd implement them into the form's class modules, but that's why we have Tek-Tips!) And I can get lost in all the names that my databses produce!

I'm posting this to offer some solutions to the overwhelming databases... I'll start.

This is taught in Access Designing 101, it's in all the books... yet I still work on databases that haven't been built this way. Mark every item with a unique name!
I can't tell you how many times this has saved me. If you let, for instance, a form's items have default names such as combo9 - you'll quickly forget which box is combo9 and which is combo12. A better suggestion is cmbAgenda for an agenda box. Labeling the beginning of each item is a life saver. Preceede the names of tables, forms, quiries, etc with something like this: tbl - table (example frmAgenda); qry - Query (example qryUpdateAgenda); frm - Form (example frmAgendaItems); mcr - Macro; mdl - Module; lbl - label; txt - text box; and so forth and so on. Keep broader items (such as form names) simple, and more specific items (such as those annoying text boxes), well, more specific.
Using this simple, relatively non-time consuming idea will save you tons of headache and code searching when the database has grown... and if you leave your company and a new employee takes on the task of managing your databases, it will save him/her a head-ache ... and it will save the company money (time = money).

Who's up next? We need some more ideas to make databses more efficient! -JPeters
These things take time...

 

[oharab]

What a good idea.
A big list of lots of tips, who could ask for more.

My tip is thus:

Don't just keep all your functions and subroutines lumped in one big module.
Break your modules down into meaningful chunks. Have one module for the export routine and another for your imports. One for generic error handling routines and another miscellaneous little functions like GetDBLocation etc.
If you give everything meaningful names and use the description field, it makes finding your way round your code much, much easier.

B ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[MissDebzie]

There are standard naming conventions which might take a bit of getting used to, but make life so much easier for yourself and other developers in the long run.

Check out this link...

http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q110264&

Enjoy!
Debs

 

[allanon]

KISS (KEEP IT SIMPLE STUPID)

I know that there are always many complex things that must be done in an application. There always has been, always will be. Just because a process is complex does not mean that the code to do the process needs to be complex. Break complex operation down into several simple operaterion and clearly indicate that they belong together.

When programming in access I would suggest to anyone to put a little code as possible into a form or report. Place as much code as you can into modules and macros. This makes maintaining and debug MUCH easier.

When you write something ask yourself what it would be like for you to maintain this if you did not write it. We all know it is harder to work on someone elses code then our own.

I would recommend a book for everyone here to read. I have read it from cover to cover once and have re-read several parts and I do a lot of mentoring and I suggest this to everyone no mattter how long you have been programming. The book is CODE COMPLETE by McCONNEL. Exelent book no matter what you level or programming language.

 

[BanditWk]

In working with Access, and my experience, I would also like to throw out this:

I keep the databases under 100 Meg a piece. One "backend" database would hold all the data, and the "frontend" would hold all forms, queries, reports, etc with Links to the backend data. Sometimes, I've come across where the backend tables get so large, that I have to split them out - doing this, I have been able to maintain locality with types of data. For me, Clients would be in one database, Creditors in another database, Client Expenses in another database, etc and by the time you get them all linked to the front end, it will be like the data is all there.

The primary reason for doing this is that it's easier to backup and should one database go bad, it may not be pertinent data and you don't risk "losing it all". This has also helped me in maintaining and manipulating my databases.

HTH Roy McCafferty
aka BanditWk
Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

"I do this because I know I can and I'm bored at work - no need to send flowers, wine, dinner, or coffee... Just send me a smile to show me that I've helped." ~ Roy McCafferty, seen on a corner in Las Vegas with a sign that said, 'Will work

 

[oharab]

Another couple of tips:
This one builds on from Roys: You should always split your db so that users have a front end on their local machine linked to the remote backend.
If you are worried about keeping user files up to date, have a look at faq705-2010.

Use action queries instead of updating recordsets in code where possible.

Think about data normalization. A well designed database is more likely to support more users & run quicker.

B ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[cheerio]

If you have Access 2000 make use of the Groups capability.

Effectively, you can create lists by topic of tables, queries, reports etc. For example MonthEnd, DailyProcess, YearEnd.

This allows you to find a particular query quickly instead of sorting through dozens in the normal view.

Ken

 

[Paddyo]

How About this

Make sure you declare your variables properly.

If an data item is going to always be an integer then declare it as an integer. Not only does it save space but integers get processed quicker also.


One other tip is relating to some of the above tips. If you have written a database and come back to it sometime later, it can be quite difficult to remember what form or query etc does what. Why not enter a description of the function of the form or query into the properties for it. To do this right click on the item and select properties. You can then enter the description.

Thats all for now

 

[Rhonin]

Here is a simple one that most either miss, or just ignore.

Do not work with the operating app.
ALWAYS! ALWAYS! ALWAYS! work on a copy.
This especially applies for FE/BE apps.
copy both the FE and BE, relink and perform all of your modifications on that one.

I cannot count the number of instances where if I had not followed this simple rule I would have ended up with major problems.

Rhonin
"too many questions, too little time..."

 

[JPeters]

Great Suggestion Rhonin.

I've got another one of my own. Lately I've been repeating this in threads over and over again.

If you're working with a long string of text that you call a lot of data from. Something like: TextBox = "Hello" & Fieldname & txtbox1 & "Thank you".

TEST THIS OUT PRIOR TO SETTING IT IN YOUR CODE USING A MESSAGE BOX. DO NOT JUST RUN IT, SEE WHAT THE STRING LOOKS LIKE BEFORE PUTTING IT IN YOUR CODE. Phew.

It's real easy to do. Just make a module or create a button that performs a message box in your form.

MsgBox (String)

That's all there is too it, and you can see exactly what it looks like and exactly what the computer will try to compute. If it looks wrong, you can fix it before it messes stuff up.

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[Garridon]

For the description properties of queries, I indicate what they are being used for (i.e., form1, form2, form3). That way, it's easy to find the query that runs a particular form or report.

For the code, include comments stating what the code is being used for, and comments for any particular lines of code, where it might not be immediately clear what the line is supposed to do. This also helps if someone else has to work on the database.

For time saving, adopt a naming convention. In Database One, don't call the Last Name field strManagerLastName and in Database Two strStudentLastName. Call them both strLastName--then you can cut and paste code without having to make as many changes.
Linda Adams
Linda Adams/Emory Hackman Official Web site

http://www.hackman-adams.com

Official web site for actor David Hedison:

http://www.david-hedison.com

 

[grtammi]

It's a little more confusing, and I use Visual C++ to do it, but I compile all my little code modules that I use in all my databases into .dll files ...

Greg

 

[JimHorton]

All,
And another tip that may be the most common 'mistake' I see (and I'm not sure if it's mentioned in any of the links above, I didn't check all of them out), but when it comes to naming conventions....

NEVER use reserved words or spaces in object or field names!

ie, a query named "Query that gets year-end totals for the report we run at yearend"

Imagine writing sql in code with that name--using the atrocious brackets [] every time.

And OrderDate and CustomerName are much prefered over "Date" and "Name", repsectively--Not only because those are reserved words--and requiere brackets--but when a new coder starts looking at the db, he'd ask "What date?? Birthdate? Anniversary Date? Date customer first got laid?", etc etc.

The other most mistake is not commenting...even if *you* think the code is 'self documenting", when someone else has to maintain it or understand it, I can guarantee you even the best developers will not appreciate having to pore through loads of code to see the end result of something you're doing. And this includes tagging "End If", ie I always tag as such:
End If 'Endif CustName <> &quot;&quot;
rstCust.MoveNext '(Ok, *this* should be self-documenting)
Loop 'Loop rstCust
End If 'Endif/Else RstCust.NoMatch/RstCust Found
End If 'Endif/else rstOrd.Eof/rstOrd has recs
End If 'End If bCheckCust = True

...so when you're looking at a row of End if or Next or Loop statements, you don't have to try to line up the If's with the Endifs (assuming the coder hasn't broken the 'always indent' rule!) by looking at the ruler or margin.

I could go on and on, but this thread already has some excellent tips for controlling projects that could turn into a monster.
--Jim

 

[Steve101]

Here's one that I hope will be useful. Its to do with code readability.

When assigning a value to a long string, use the string concatenation and line continuation features of VBA, to break up the string and make it more readable. For example, as an alternative to using either of the following two ways of assigning a value to an SQL String:

(a) The 'strung out' way:
ThisSQL = &quot;SELECT Surname, FirstName, OrderDate, Country FROM tblOrders WHERE Surname = 'SMITH' AND FirstName = 'John' ORDER BY OrderDate

(b) The 'verbose' way:
ThisSQL = &quot;&quot;
ThisSQL = ThisSQL & &quot;SELECT Surname, FirstName, OrderDate, Country &quot;
ThisSQL = ThisSQL & &quot;FROM tblOrders &quot;
ThisSQL = ThisSQL & &quot;WHERE Surname = 'SMITH' &quot;
ThisSQL = ThisSQL & &quot;AND FirstName = 'John' &quot;
ThisSQL = ThisSQL & &quot;ORDER BY OrderDate &quot;


consider using the following syntax. It is compact, concise, and nicely aligns the SQL code for easy reading:

ThisSQL = &quot;SELECT Surname, FirstName, OrderDate, Country &quot; & _
&quot;FROM tblOrders &quot; & _
&quot;WHERE Surname = 'SMITH' &quot; & _
&quot;AND FirstName = 'John' &quot; & _
&quot;ORDER BY OrderDate &quot;

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Steve101]

Of course my prior mail would have presented better using mon spaced text; ie.

[tt] ThisSQL = &quot;SELECT Surname, FirstName, OrderDate, Country &quot; & _
&quot;FROM tblOrders &quot; & _
&quot;WHERE Surname = 'SMITH' &quot; & _
&quot;AND FirstName = 'John' &quot; & _
&quot;ORDER BY OrderDate &quot;
[/tt]
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[djsiders]

Lots of great tips here. And as mentioned, we have all probably seen code that ignores even the most &quot;obvious&quot; of these. I program mainly in VB, even when using Access DB's but most would apply to either method. Two things I always concentrate on doing are:

1) Break tasks down into as many simple functions or subs as possible while using a little bit of judgement as to how far to go. I often create functions (or subs) for tasks that at the time, I think I may only use in one place. It seems like I often end up using them all over the code. I also try to make these routines &quot;generic&quot; in nature so that I can use the same logic with different parameters. This takes a little longer at first, but as soon as you start re-using the code in multiple places, your efficiency goes way up. Also changing the logic means that you make the change to the code in one place, and anywhere that it is used is changed as well.

2) I try to use constants for values that are used in multiple places. For example if you use a constant named FIL_DB_NAME instead of hardcoding in the path and filename of the database, it makes your code easier to read, and also tells the person reading it what the file name is. Also if you have several filenames defined this way, and you start them all off with the same beginning (FIL_ in this case) then when you need a file name, you don't have to rememeber the actual name. Just type in FIL_ (or whatever your constant name starts with), press CRTRL + SPACE and the intellisense will bring up all of your constants together in a list. Scan down the list, pick the one with the decription you want and fill in the rest of the name. This also makes changing a value easier. Just change the value in the constant assignment, and it's changed anywhere the constant is used. I even go as far as to make a module that contains nothing but constant declarations (call it modConstants or something like that). If I need to change a constant, I just go to that module.

 

[JIRRERA]

I don't know if anyone else has run into this with Access 2000, but I find that just normal editing of existing forms, modules, reports, etc. causes the database to begin to &quot;bloat&quot; with each successive save, physically taking up more an more disk space. Compacting shrinks it less and less over time. This is especially true if I do any copying, pasting and/or deleting of objects. My solution is to regularly make a backup of the db. Then create a new one and import all the objects into it (remember to click the &quot;Advanced&quot; button and include toolbars and import specs). This also seems to help keep if free of errors beyond what compacting/repair and decompiling/compiling does. Sounds simple but it's a lifesaver for me.

 

[ClydeDoggie]

Diagram all the table relationships that you can. If tables have a relationship but your are maintaining ref. integrity through code/queries you can still link them, just don't check the ref. integrity box. This gives other developers a quick view of what is going on in terms of data flow and keeps your data cleaner. Also, don't diagram relationships in the front end of a split db, they don't show in the backend and are don't have any effect on data integrity.

 

[oharab]

Josh!
Looks like you are on the way with your plan for total world (well forum) domination! Have you noticed that you have knocked me off my 2nd place in the top experts list!

I must now think of some dastardly way to exact my revenge!!!

Another tip should do it!

It's another obvious one, but how often is it done? Before you start coding your database, get out some paper and a pencil (or pen if you prefer) and think about what is needed. Write down what you want the database to do and then split that up into how you are going to do it.

At this stage, try not to think in terms of code, but in tasks. If it starts to look like a task will require lots of little tasks underneath it, stop & have a think about finding a better way. Could the data be better normalized? Do I need to store this here?

I often find that after I have &quot;drawn&quot; my database (I usually start with a map of tasks joined together by a mass of lines) the processes can often be more streamlined.

Just my 2ps worth!

B ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

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

 

[redbirdofthesouth]

Remember maintainability:
Comment your code.
Document what you have done and provide complete technical documentation with the app.
You could get hit by a bus tomorrow and someone else will have to figure out what you have been doing; without comments and documentation maintenance becomes extremely difficult and irritating.
Think about your long term goals with this: where is the technology going? Who will be using or maintaining this in the future? What changes are likely to occur?
Use basic systems analysis and software engineering principals in designing any system and it will be successful: plan and document!