Rookie Mistake that cost me about 2 days work to fix. Learn from my pain. (1 Viewer)

[Umpire]

Just want to pass along this incase any other newbie is reading this.

I started out making my database and I really did not pay enough attention to my Table and Field names. (Or queries or reports either.) I read things that said do this or do that. And at one point I started doing things the right way.

Lots of places tell you that you should name things this way or that way. But they fail to explain what happens if you don't.

I was trying to work with a couple forms and Text Strings and I kept getting weird errors and or results. I finally figured out that because I used a dash (-) and blank spaces in my field and table names, Access was trying to use the dash as a minus sign, and shortening my names to only the part before the first blank space. Well no wonder things were not working.

So I bit the bullet and just spend the better part of the last 2 1/2 work days (about 2 days altogether along with my other duties) renaming all my tables and fields to get rid of spaces and dashes. So of course I had to go through all my Queries, Forms, Reports and a couple macros, updating all the field and table names. Pan in the rear let me tell you.

And of course while I was doing the fix, I had about 6 "Need this done now!" situations pop up with my other duties. I normally get 1 or 2 a month.

LEARN FROM MY MISTAKE!

 

[Isaac]

Well said

 

[NauticalGent]

Disabuse yourself from thinking this will be the last time it happens...

 

[The_Doc_Man]

One very painful - but sadly, necessary - lesson.

For what it is worth, you are not the only one to face that. My first Access project for the U.S. Navy was inherited from someone who didn't know that rule AND had written everything based on an active Excel spreadsheet as the back-end file. Not only that, but he had used reserved words for some variable names. I spent a couple of weeks cleaning up that mess.

 

[Galaxiom]

Renaming can be made a lot easier with the Find And Replace in V-tools Total Deep Search.
It is a free plugin for Access. Every Access developer should have it

 

[Uncle Gizmo]

Excellent post Umpire.

The next rookie mistake I often see is what I call:- "Excel In Access"

It is easy to think that MS Access is just a more advanced version of Excel and this leads to bad choices in the table design.

I've covered it in great detail in my blog on my Nifty Access website here:-

Excel in Access (Parts 1, 2 and 3 as Video Instructions) - Nifty Access

Excel in Access (Parts 1, 2 and 3 as Video Instructions) Excel in Access (Part 1) Excel in Access (Part 2) Excel in Access (Part 3) MS Access Your Data With Sub-Forms How to use the Normalization Tool Convert an Excel "Contacts" Spreadsheet to MS Access Convert an Excel "Contacts" Spreadsheet to...

www.niftyaccess.com

 

[Uncle Gizmo]

The next one which I have yet to blog about is "Cookie Cutter Forms".

This is where you create a form for viewing your data. However you find this form is unsuitable for entering data, so you make a copy and modify it so that it is suitable for data entry.

So now you have two forms which are essentially identical with some minor changes.

The issue is similar to the problem you mentioned, where you had to go through and change all of your queries.

With multiple "duplicate" forms you now have multiple forms to change when you make minor changes to your database.

The solution is to add VBA code to the form so that when you open it, it is essentially the same form but offers different functionality.

This leads nicely to the next rookie mistake and that is using macros instead of VBA.

 

[moke123]

Kudos for having the wisdom to fix it now rather than weeks or months from now. It saves both you and us a lot of frustration with any future problems you may encounter.

 

[Minty]

I suspect we might end up linking to this thread quite frequently.

We all see a lot of newcomers who take the oft-repeated advice we proffer with a pinch of salt, and probably end up using a years supply of band-aid fixes and workarounds rather than fessing up (to themselves) and sorting things out.

+1 on the Kudos

 

[Uncle Gizmo]

using a years supply of band-aid fixes and workarounds

The only good thing about a workaround is the fun and challenge of twisting VBA code to do something completely stupid!

I used to love doing that, but I have got wiser with age. (Don't answer that!)

 

[MajP]

So I bit the bullet and just spend the better part of the last 2 1/2 work days (about 2 days altogether along with my other duties) renaming all my tables and fields to get rid of spaces and dashes. So of course I had to go through all my Queries, Forms, Reports and a couple macros, updating all the field and table names. Pan in the rear let me tell you

You should have came here earlier. Someone gave me a database full of "-" in table and field names. Wrote some simple code to replace "-" with "_". Loop the table defs, query defs, fields and replace. Loop the forms and controls and replace in the control source, row source and recordsource. Use vba extensibility to update the code. Not talking much code to do this.

 

[Isaac]

You should have came here earlier. Someone gave me a database full of "-" in table and field names. Wrote some simple code to replace "-" with "_". Loop the table defs, query defs, fields and replace. Loop the forms and controls and replace in the control source, row source and recordsource. Use vba extensibility to update the code. Not talking much code to do this.

I agree, I was surprised when I first learned how easy and not-complicated it was to loop through these objects.
But I can imagine a person worrying that they might have 'missed' something.

 

[Pat Hartman]

The sign of a novice is that he (she, it) thinks that a line of code that is written or object that has been created must be preserved at all costs and they are so invested in the past that they spend enormous amounts of time working around design flaws rather than simply backing up and shoring up the foundation.

You are on your way to greatness. Good for you

 

[Isaac]

The sign of a novice is that he (she, it) thinks that a line of code that is written or object that has been created must be preserved at all costs and they are so invested in the past that they spend enormous amounts of time working around design flaws rather than simply backing up and shoring up the foundation.

You are on your way to greatness. Good for you

That's a wise saying Pat, I agree. I call it "developer maturity", and I am always striving for (and needing to have) more of it - no matter how far along I go.

Another key one is humility. Meaning, after you have spent 5 solid days creating the world's greatest procedure/code block/design, and you get feedback on it from another developer who has solved the whole problem in 3 lines of code, (or it might not be a question of being shorter code, just something that makes it obvious it's a superior design for whatever reason), being the bigger person who can shrug and say "Hmm, Yep - yours is better. Switching now."....Even in front of a room of co-workers. It's hard, but part of life! And the mark of a great developer!

 

[Umpire]

You should have came here earlier. Someone gave me a database full of "-" in table and field names. Wrote some simple code to replace "-" with "_". Loop the table defs, query defs, fields and replace. Loop the forms and controls and replace in the control source, row source and recordsource. Use vba extensibility to update the code. Not talking much code to do this.

Oh it was much more F'ed up than that. I had mixed naming conventions and errors within the same form or table.
Plus I am still new to VBA.
On the upside, I did take the chance to fix a couple minor design/layout error on my forms and I discovered that I had the margins messed up on a couple reports and some spelling errors that I was able to fix while I was fixing everything else.