Evil Access Stuff
By Richard Rost
3 years ago
I'm trying to come up with a list of all of the evil stuff in Access that I have to continuously tell people NOT to do. Help me out here guys. You should never use:
- Spaces in your Object names (tables, fields, etc.) Say "OrderDetailT" not "Order Detail Table"
- Reserved words (Date, Name, etc.)
- Attachment Data Type (NEVER store attachments inside your database. See ABCD Part 5). Super Evil.
- OLE Objects or Pictures stored inside the database (see Images).
- Multi-Valued Fields
- Hyperlink Fields. Use Short Text instead.
- Google Drive (or DropBox, etc.) to share an Access database. See this page if you want remote access.
- A single shared database file on a network with multiple users opening it (Split your DB)
- AutoNumbers are NOT for You! They're for Access' internal use ONLY! Don't worry if they're out of sequence or you're missing some. If you need sequential numbers, use a custom counter.
- Switchboards. Make your own Menu Forms.
- Calculated fields in tables. Use calculated query fields instead. Tables shouldn't contain calculations.
- Lookup Wizard or lookup values in tables. Use relational combo boxes on forms instead.
- Subdatasheets in tables or queries. No. Use forms with subforms instead to show relationships. See this for more information.
- Data Macros. I'm sorry. I'll never get used to having macros inside tables. It breaks every rule that I've ever been taught about relational databases. I don't like macros to begin with. Macros in Forms are a pain. Macros in Tables are an abomination. Stick to Forms and Event Programming (VBA).
- The Append Only property for long text fields
These things are frowned upon:
- SendKeys - try not to use this if at all possible. There's almost always a better way.
- Split Forms - problems with VB, limited formatting, subform issues
- Plurals in your object names. Try to be consistent even if it doesn't sound right. CustomerT not CustomersT.
- Not commenting your code!
- Not naming all of your objects like command buttons (I'm guilty of this)
- Not backing up your database nightly!
- Not backing up offsite!
- Not backing up before a major code revision (don't lose a whole day's work)
- Third-party add-ons and references. Use internal Access objects if possible, or at least objects that come with Office.
- ActiveX Objects - unreliable
- Navigation Forms - use a Main Menu form, or my subform technique in ABCD Part 2
- Tab Controls. I just don't like them. They're a pain to use.
- From @NoLongerSet - Overuse of DoCmd.SetWarnings False and On Error Resume Next - my reply: Yeah... I pretty much only use SetWarnings around a RunSQL command (and even then I try to use db.Execute if possible). Resume Next is lazy error handling, but sometimes I like it if I don't feel like doing something the LONG way. LOL.
- Cascade Updates are dumb. Cascade Deletes are dangerous! Handle it yourself in VBA/SQL code.
- Layout Mode and groupings of controls. Sure, use them, arrange your controls how you like, but then right-click and Remove Layout when you're done with it. Don't leave it on.
Be careful of this stuff:
- Adding VBA references to outside libraries. Version numbers and DLLs can change in future Office editions.
What can you think of? I know I've mentioned a lot of things in my 20+ years of teaching Access classes. But you know me... old brain can't rememeber it all...
|