Evil Access Stuff
By Richard Rost
3 months 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.)
- Split Forms (I've seen several times where VBA code that works on a single form stops working on a split form)
- 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 (TechHelp video coming up on this one soon). Super Evil.
- 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 as numbers YOU care about. 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 values in tables. Use relational combo boxes on forms instead.
These things are frowned upon:
- 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.
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...
You may want to read these articles from the 599CD News: