Deprecated: Optional parameter $default declared before required parameter $err_msg is implicitly treated as a required parameter in /home4/tylering/public_html/b2e/inc/_core/_param.funcs.php on line 1401

Deprecated: Optional parameter $memorize declared before required parameter $err_msg is implicitly treated as a required parameter in /home4/tylering/public_html/b2e/inc/_core/_param.funcs.php on line 1401

Deprecated: Optional parameter $default declared before required parameter $err_msg is implicitly treated as a required parameter in /home4/tylering/public_html/b2e/inc/_core/_param.funcs.php on line 2320

Deprecated: Optional parameter $memorize declared before required parameter $err_msg is implicitly treated as a required parameter in /home4/tylering/public_html/b2e/inc/_core/_param.funcs.php on line 2320

Deprecated: Creation of dynamic property GeneralSettings::$count_col_key_names is deprecated in /home4/tylering/public_html/b2e/inc/settings/model/_abstractsettings.class.php on line 121

Deprecated: Creation of dynamic property AbstractSettings::$count_col_key_names is deprecated in /home4/tylering/public_html/b2e/inc/settings/model/_abstractsettings.class.php on line 121

Deprecated: Optional parameter $coll_ID declared before required parameter $selected_value is implicitly treated as a required parameter in /home4/tylering/public_html/b2e/inc/collections/model/_blog.funcs.php on line 1942

Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /home4/tylering/public_html/b2e/inc/links/model/_link.funcs.php on line 1024

Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /home4/tylering/public_html/b2e/inc/links/model/_link.funcs.php on line 1024

Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /home4/tylering/public_html/b2e/inc/links/model/_link.funcs.php on line 1024

Deprecated: Using ${var} in strings is deprecated, use {$var} instead in /home4/tylering/public_html/b2e/inc/links/model/_link.funcs.php on line 1024

Deprecated: Optional parameter $action declared before required parameter $read_only_list is implicitly treated as a required parameter in /home4/tylering/public_html/b2e/inc/maintenance/model/_maintenance.funcs.php on line 624

Deprecated: Optional parameter $overwrite declared before required parameter $read_only_list is implicitly treated as a required parameter in /home4/tylering/public_html/b2e/inc/maintenance/model/_maintenance.funcs.php on line 624

Deprecated: Optional parameter $renderers declared before required parameter $params is implicitly treated as a required parameter in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 2013

Deprecated: Optional parameter $current_renderers declared before required parameter $params is implicitly treated as a required parameter in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 2158

Deprecated: Optional parameter $current_renderers declared before required parameter $params is implicitly treated as a required parameter in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 2336

Deprecated: Creation of dynamic property UserSettings::$count_col_key_names is deprecated in /home4/tylering/public_html/b2e/inc/settings/model/_abstractsettings.class.php on line 121

Deprecated: htmlspecialchars(): Passing null to parameter #1 ($string) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_init_login.inc.php on line 81

Deprecated: Optional parameter $prefix declared before required parameter $dbIDname is implicitly treated as a required parameter in /home4/tylering/public_html/b2e/inc/_core/model/dataobjects/_dataobjectcache.class.php on line 133

Deprecated: Creation of dynamic property CollectionSettings::$count_col_key_names is deprecated in /home4/tylering/public_html/b2e/inc/settings/model/_abstractsettings.class.php on line 121

Warning: Cannot modify header information - headers already sent by (output started at /home4/tylering/public_html/b2e/inc/_core/_param.funcs.php:1401) in /home4/tylering/public_html/b2e/inc/_core/_template.funcs.php on line 467

Deprecated: Optional parameter $chapter_mode declared before required parameter $item_index is implicitly treated as a required parameter in /home4/tylering/public_html/b2e/inc/items/model/_itemlistlight.class.php on line 2404

Deprecated: Optional parameter $params declared before required parameter $content_is_displayed is implicitly treated as a required parameter in /home4/tylering/public_html/b2e/inc/items/model/_itemlistlight.class.php on line 2593

Deprecated: Creation of dynamic property ItemList2::$filterset_name is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemlistlight.class.php on line 114

Deprecated: preg_match(): Passing null to parameter #2 ($subject) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_param.funcs.php on line 1407

Deprecated: preg_match(): Passing null to parameter #2 ($subject) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_param.funcs.php on line 1407

Deprecated: Optional parameter $dbprefix declared before required parameter $dbIDname is implicitly treated as a required parameter in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 76

Deprecated: Creation of dynamic property ItemQuery::$dbtablename is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 78

Deprecated: Creation of dynamic property ItemQuery::$dbprefix is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 79

Deprecated: Creation of dynamic property ItemQuery::$dbIDname is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 80

Deprecated: Creation of dynamic property ItemQuery::$Blog is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 231

Deprecated: Creation of dynamic property ItemQuery::$cat_array is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 232

Deprecated: Creation of dynamic property ItemQuery::$cat_modifier is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 233

Deprecated: strpos(): Passing null to parameter #1 ($haystack) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10535

Deprecated: explode(): Passing null to parameter #2 ($string) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10540

Deprecated: strpos(): Passing null to parameter #1 ($haystack) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10535

Deprecated: explode(): Passing null to parameter #2 ($string) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10540

Deprecated: Creation of dynamic property ItemQuery::$assignees_logins is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 525

Deprecated: Creation of dynamic property ItemQuery::$author_assignee is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 557

Deprecated: strpos(): Passing null to parameter #1 ($haystack) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10535

Deprecated: explode(): Passing null to parameter #2 ($string) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10540

Deprecated: strpos(): Passing null to parameter #1 ($haystack) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10535

Deprecated: explode(): Passing null to parameter #2 ($string) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10540

Deprecated: strpos(): Passing null to parameter #1 ($haystack) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10535

Deprecated: explode(): Passing null to parameter #2 ($string) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10540

Deprecated: strpos(): Passing null to parameter #1 ($haystack) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10535

Deprecated: explode(): Passing null to parameter #2 ($string) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10540

Deprecated: Creation of dynamic property ItemQuery::$m is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 842

Deprecated: Creation of dynamic property ItemQuery::$w is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 843

Deprecated: Creation of dynamic property Item::$objtype is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemlight.class.php on line 164

Deprecated: Creation of dynamic property Item::$datestart is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemlight.class.php on line 175

Deprecated: Creation of dynamic property Item::$objtype is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemlight.class.php on line 164

Deprecated: Creation of dynamic property Item::$datestart is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemlight.class.php on line 175

Deprecated: Creation of dynamic property Item::$objtype is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemlight.class.php on line 164

Deprecated: Creation of dynamic property Item::$datestart is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemlight.class.php on line 175

Deprecated: Creation of dynamic property Item::$objtype is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemlight.class.php on line 164

Deprecated: Creation of dynamic property Item::$datestart is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemlight.class.php on line 175

Deprecated: Creation of dynamic property Item::$objtype is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemlight.class.php on line 164

Deprecated: Creation of dynamic property Item::$datestart is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemlight.class.php on line 175

Deprecated: Creation of dynamic property Item::$objtype is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemlight.class.php on line 164

Deprecated: Creation of dynamic property Item::$datestart is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemlight.class.php on line 175

Deprecated: Creation of dynamic property Item::$objtype is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemlight.class.php on line 164

Deprecated: Creation of dynamic property Item::$datestart is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemlight.class.php on line 175

Warning: Cannot modify header information - headers already sent by (output started at /home4/tylering/public_html/b2e/inc/_core/_param.funcs.php:1401) in /home4/tylering/public_html/b2e/inc/_core/_template.funcs.php on line 40

Warning: Cannot modify header information - headers already sent by (output started at /home4/tylering/public_html/b2e/inc/_core/_param.funcs.php:1401) in /home4/tylering/public_html/b2e/inc/_core/_template.funcs.php on line 405

Warning: Cannot modify header information - headers already sent by (output started at /home4/tylering/public_html/b2e/inc/_core/_param.funcs.php:1401) in /home4/tylering/public_html/b2e/inc/_core/_template.funcs.php on line 406

Warning: Cannot modify header information - headers already sent by (output started at /home4/tylering/public_html/b2e/inc/_core/_param.funcs.php:1401) in /home4/tylering/public_html/b2e/inc/_core/_template.funcs.php on line 407

Warning: Cannot modify header information - headers already sent by (output started at /home4/tylering/public_html/b2e/inc/_core/_param.funcs.php:1401) in /home4/tylering/public_html/b2e/inc/_core/_template.funcs.php on line 408

Deprecated: Creation of dynamic property ItemQuery::$dbtablename is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 78

Deprecated: Creation of dynamic property ItemQuery::$dbprefix is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 79

Deprecated: Creation of dynamic property ItemQuery::$dbIDname is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 80

Deprecated: Creation of dynamic property ItemQuery::$Blog is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 231

Deprecated: Creation of dynamic property ItemQuery::$cat_array is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 232

Deprecated: Creation of dynamic property ItemQuery::$cat_modifier is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 233

Deprecated: strpos(): Passing null to parameter #1 ($haystack) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10535

Deprecated: explode(): Passing null to parameter #2 ($string) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10540

Deprecated: strpos(): Passing null to parameter #1 ($haystack) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10535

Deprecated: explode(): Passing null to parameter #2 ($string) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10540

Deprecated: Creation of dynamic property ItemQuery::$assignees_logins is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 525

Deprecated: strpos(): Passing null to parameter #1 ($haystack) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10535

Deprecated: explode(): Passing null to parameter #2 ($string) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10540

Deprecated: strpos(): Passing null to parameter #1 ($haystack) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10535

Deprecated: explode(): Passing null to parameter #2 ($string) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10540

Deprecated: strpos(): Passing null to parameter #1 ($haystack) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10535

Deprecated: explode(): Passing null to parameter #2 ($string) of type string is deprecated in /home4/tylering/public_html/b2e/inc/_core/_misc.funcs.php on line 10540

Deprecated: Creation of dynamic property ItemQuery::$m is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 842

Deprecated: Creation of dynamic property ItemQuery::$w is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_itemquery.class.php on line 843

Warning: Cannot modify header information - headers already sent by (output started at /home4/tylering/public_html/b2e/inc/_core/_param.funcs.php:1401) in /home4/tylering/public_html/b2e/skins/_rss2/index.main.php on line 45

Warning: Cannot modify header information - headers already sent by (output started at /home4/tylering/public_html/b2e/inc/_core/_param.funcs.php:1401) in /home4/tylering/public_html/b2e/skins/_rss2/index.main.php on line 46
Switched Keys - Category: "Access" https://tylerhosting.com/b2e/dbell.php Switched Keys, the blogs of Dana Bell of Tyler, Texas en-US http://blogs.law.harvard.edu/tech/rss 60 Hoot - Word Game Study Tool https://tylerhosting.com/b2e/dbell.php/hoot-word-game-study-tool Thu, 11 Aug 2016 21:52:00 +0000 Dana Bell Information Technology Personal Access Scrabble 372@https://tylerhosting.com/b2e/
Deprecated: Creation of dynamic property Item::$cache_has_content_parts is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 4911

Deprecated: Creation of dynamic property tinymce_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property calendar_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property calendar_plugin::$dbtable is deprecated in /home4/tylering/public_html/b2e/plugins/_calendar.plugin.php on line 53

Deprecated: Creation of dynamic property calendar_plugin::$dbprefix is deprecated in /home4/tylering/public_html/b2e/plugins/_calendar.plugin.php on line 54

Deprecated: Creation of dynamic property calendar_plugin::$dbIDname is deprecated in /home4/tylering/public_html/b2e/plugins/_calendar.plugin.php on line 55

Deprecated: Creation of dynamic property quicktags_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property auto_anchors_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property shortlinks_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property custom_tags_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property archives_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property archives_plugin::$dbtable is deprecated in /home4/tylering/public_html/b2e/plugins/_archives.plugin.php on line 54

Deprecated: Creation of dynamic property archives_plugin::$dbprefix is deprecated in /home4/tylering/public_html/b2e/plugins/_archives.plugin.php on line 55

Deprecated: Creation of dynamic property archives_plugin::$dbIDname is deprecated in /home4/tylering/public_html/b2e/plugins/_archives.plugin.php on line 56

Deprecated: Creation of dynamic property email_elements_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property financial_contribution_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property inlines_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property ping_b2evonet_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property ping_pingomatic_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property twitter_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property webmention_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property webmention_plugin::$ping_service_process_message is deprecated in /home4/tylering/public_html/b2e/plugins/webmention_plugin/_webmention.plugin.php on line 52

Deprecated: Creation of dynamic property webmention_plugin::$ping_service_setting_title is deprecated in /home4/tylering/public_html/b2e/plugins/webmention_plugin/_webmention.plugin.php on line 53

Deprecated: Creation of dynamic property autolinks_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property videoplug_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property auto_p_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property mermaid_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property texturize_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property nofollow_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property content_blocks_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property table_contents_plugin::$classfile_path is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_plugins.class.php on line 413

Deprecated: Creation of dynamic property Item::$renderers_validated is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 11107

Deprecated: Creation of dynamic property Item::$pages is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 2412

Deprecated: Creation of dynamic property ItemSettings::$count_col_key_names is deprecated in /home4/tylering/public_html/b2e/inc/settings/model/_abstractsettings.class.php on line 121
<h2>The seed</h2> <p>About six weeks ago I was interviewed for a job with <a href="http://genesisworld.com/" target="_blank">GenesisWorld</a> for a software developer using C#/ASP.NET/T-SQL. The position I applied for was an entry-level job. As a part of the interview, I had to do some coding, in C#. I knew I had used C# before in a school project, and it was in a C#/ASP.NET/T-SQL combination, but discovered that I really did not use much C#, and both the book and the project focused on the ASP.NET side.</p> <p>Through that process, though, a seed was planted, and I decided I needed (wanted) to learn more about C#. I prefer lower-level languages like C, but since C# is on the short list of programming languages in most demand, I set out to learn it and the best way to learn it is through a major project.</p> <p>Besides, the other key language in demand was C++, but the latest version of Visual Studio I could install was 2010, and that year Microsoft didn't include IntelliSense for C++.</p> <h2>Word games</h2> <p>At the same time I was re-exploring my interest in word games like Scrabble and Words with Friends. The Scrabble community has been transformed a bit since the last time I was serious about it. The word list has been updated twice since the turn of the century and now Hasbro/Merriam claims copyright on the word list and is enforcing that claim. As a result many of the study tools have been left by the wayside, including Lexpert, the one I used quite often.</p> <p>So I decided to write an application that would go toward replacing some of the study tools that had been abandoned. Hoot was born as a result. Since the word list used in Scrabble is affectionately called the Official Word List (OWL), I decided on Hoot as a suitable name for the program. After about 6 weeks, Hoot is available for public consumption and constructive criticism.</p> <h2>Hoot Features</h2> <p><a href="/b2e/media/blogs/dbell/beginners.png?mtime=1470946944"><img class="loadimg" style="float: right;" src="/b2e/media/blogs/dbell/beginners.png?mtime=1470946944" alt="" width="292" height="240" /></a></p> <p>Hoot will do most of the functions of the program I used previouslyd With Hoot you can search for words based on beginning or ending letter(s), the size of the word, the words that are contained (subwords), or include the word (extensions), anagrams, and well ...</p> <p>The provided help file contains this listing of searches you can do in Hoot.</p> <p> </p> <ul> <li>Beginners - Shortcut lists for Scrabble beginners</li> <li>Letter Count - Lists by number of letters in the word. Can filter by initial letter</li> <li>Begins/Ends - List of words beginning or ending with a certain letter, prefix or suffix. Select from list or enter in box. </li> <li>Contains Letters - Lists that contain any of the letters entered</li> <li>Anagrams - List of words that can be made by rearranging letters in the words. Includes sub-anagrams, super-anagrams, standard stems.</li> <li>Stems - List of words that can be rearranged after adding a letter to form several other words.</li> <li>Hooks - List of words that can be formed by adding a letter to the front or back of a word</li> <li>Anagram Hooks - List of words that can be formed by rearranging the letters in the search and adding a hook letter </li> <li>Extensions - List of words that contain a certain word</li> <li>SubWords - List of words found within a given word</li> <li>Pattern - List of words that match a certain pattern</li> <li>Parallel Plays - List of words that can be played parallel to a word</li> <li>Vowel Heavy - List of words with lots of vowels</li> <li>No Vowel - List of words with no vowels</li> <li>Consonant Dumps - List of words with lots of consonants</li> <li>Q not U - List of words containing a Q, but not a U</li> <li>Palindromes - List of words spelled the same frontward and backward.</li> <li>Named Lists - Custom lists or various subjects</li> <li>Text File Lists - Custom lists saved in a text file.</li> </ul> <div class="image_block"><a href="/b2e/media/blogs/dbell/wordjudge.png?mtime=1470946959"><img class="loadimg" style="float: left;" src="/b2e/media/blogs/dbell/wordjudge.png?mtime=1470946959" alt="" width="200" height="276" /></a></div> <p>Most windows also have a Show hooks option that will display the letters that can be added in front or behind the word to form another word. <br />For example, the word AX is alternately displayed as</p> <p style="padding-left: 30px;"><span style="font-family: 'courier new', courier, monospace;">flmprstwz AX e</span></p> <p>As users try out Hoot they may also discover another feature. As the search screen windows are resized, the list and controls respond to the size, so you could make the list a long skinny one to see more words at once, or a shorter one to save screen space.</p> <p>Searches also have a slide show mode, where words in a list are displayed individually in a separate window, and <br />optionally rotated on a timed basis.</p> <p>Hoot also has a Word Judge screen to judges the validity of a play. The Word Judge can be locked, and displayed separately from the program using a commandline option.</p> <h2>Hoot Shortcomings</h2> <p>Of course, there are many shortcomings, simply because incorporating the features takes time. You might also refer to these as plans for future releases if I continue development.</p> <p>Searches are generally displayed first by word length and then alphabetically. They cannot be re-sorted unless you use the slide show.</p> <p>You can change a few things about the display, but more customizations can be added.</p> <p>One of the plans is to allow users to hide words they already know.</p> <p>Exporting: You can't export words from the list.</p> <p>Access as a database, which includes some SQL limitations.</p> <p>Multiple windows, which some people may find handy, but an optional single screen would appeal to a lot of people.</p> <p>A more intelligent installer (Wix).</p> <h2>Learning Curve</h2> <p>From a programmer's point of view, I've learned a lot about C# and the .NET framework. C# is very similar to Java, and the .NET framework is quite extensive. As a Microsoft project, I am quite familiar with navigation from previous experience with VBA. Some of the things that I excitedly learned to do include</p> <ul> <li>Opening other databases (by changing the connection string run-time).</li> <li>Managing and minimizing spikes in memory use.</li> <li>Using ListBoxes, Lists, TableAdapters, and old-style OLE db access routines. </li> <li>Develop fast, efficient searching algorithms. Learn what to do when.</li> <li>Filtering keystrokes.</li> <li>Managing window states and events.</li> </ul> <p>And that's just off the top. It was/is an educational experience, and I enjoy education. </p> <h2>Download</h2> <p>Download the installer or the help file that describes the features from <a title="Hoot - Word Game Study Tool" href="/hoot/" target="_blank">www.tylerhosting.com/hoot/</a>. </p><div class="item_footer"><p><small><a href="https://tylerhosting.com/b2e/dbell.php/hoot-word-game-study-tool">Original post</a> blogged on <a href="http://www.tylerhosting.com/b2e/">Switched Keys</a>.</small></p></div>
Deprecated: Creation of dynamic property autolinks_plugin::$setting_nofollow_auto is deprecated in /home4/tylering/public_html/b2e/plugins/_autolinks.plugin.php on line 439

Deprecated: Creation of dynamic property autolinks_plugin::$setting_autolink_defs_coll_db is deprecated in /home4/tylering/public_html/b2e/plugins/_autolinks.plugin.php on line 442

Deprecated: Creation of dynamic property autolinks_plugin::$setting_autolink_urls is deprecated in /home4/tylering/public_html/b2e/plugins/_autolinks.plugin.php on line 443

Deprecated: Creation of dynamic property autolinks_plugin::$setting_autolink_emails is deprecated in /home4/tylering/public_html/b2e/plugins/_autolinks.plugin.php on line 444

Deprecated: Creation of dynamic property autolinks_plugin::$setting_autolink_username is deprecated in /home4/tylering/public_html/b2e/plugins/_autolinks.plugin.php on line 445

Deprecated: Creation of dynamic property autolinks_plugin::$setting_autolink_tag is deprecated in /home4/tylering/public_html/b2e/plugins/_autolinks.plugin.php on line 446

Deprecated: Creation of dynamic property PluginSettings::$count_col_key_names is deprecated in /home4/tylering/public_html/b2e/inc/settings/model/_abstractsettings.class.php on line 121

Deprecated: Creation of dynamic property PluginSettings::$plugin_ID is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_pluginsettings.class.php on line 36

Deprecated: Creation of dynamic property PluginSettings::$count_col_key_names is deprecated in /home4/tylering/public_html/b2e/inc/settings/model/_abstractsettings.class.php on line 121

Deprecated: Creation of dynamic property PluginSettings::$plugin_ID is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_pluginsettings.class.php on line 36

Deprecated: Creation of dynamic property autolinks_plugin::$replacement_link_array is deprecated in /home4/tylering/public_html/b2e/plugins/_autolinks.plugin.php on line 341

Deprecated: Creation of dynamic property videoplug_plugin::$video_width is deprecated in /home4/tylering/public_html/b2e/plugins/videoplug_plugin/_videoplug.plugin.php on line 152

Deprecated: Creation of dynamic property videoplug_plugin::$video_height is deprecated in /home4/tylering/public_html/b2e/plugins/videoplug_plugin/_videoplug.plugin.php on line 153

Deprecated: Creation of dynamic property PluginSettings::$count_col_key_names is deprecated in /home4/tylering/public_html/b2e/inc/settings/model/_abstractsettings.class.php on line 121

Deprecated: Creation of dynamic property PluginSettings::$plugin_ID is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_pluginsettings.class.php on line 36

Deprecated: Creation of dynamic property PluginSettings::$count_col_key_names is deprecated in /home4/tylering/public_html/b2e/inc/settings/model/_abstractsettings.class.php on line 121

Deprecated: Creation of dynamic property PluginSettings::$plugin_ID is deprecated in /home4/tylering/public_html/b2e/inc/plugins/model/_pluginsettings.class.php on line 36

Deprecated: Creation of dynamic property auto_p_plugin::$use_auto_br is deprecated in /home4/tylering/public_html/b2e/plugins/_auto_p.plugin.php on line 126

Deprecated: Creation of dynamic property auto_p_plugin::$add_p_in_block is deprecated in /home4/tylering/public_html/b2e/plugins/_auto_p.plugin.php on line 127

Deprecated: Creation of dynamic property auto_p_plugin::$skip_tags is deprecated in /home4/tylering/public_html/b2e/plugins/_auto_p.plugin.php on line 128

Deprecated: Creation of dynamic property nofollow_plugin::$setting_rel_options is deprecated in /home4/tylering/public_html/b2e/plugins/_nofollow.plugin.php on line 159

Deprecated: Creation of dynamic property nofollow_plugin::$setting_target_options is deprecated in /home4/tylering/public_html/b2e/plugins/_nofollow.plugin.php on line 172

The seed

About six weeks ago I was interviewed for a job with GenesisWorld for a software developer using C#/ASP.NET/T-SQL. The position I applied for was an entry-level job. As a part of the interview, I had to do some coding, in C#. I knew I had used C# before in a school project, and it was in a C#/ASP.NET/T-SQL combination, but discovered that I really did not use much C#, and both the book and the project focused on the ASP.NET side.

Through that process, though, a seed was planted, and I decided I needed (wanted) to learn more about C#. I prefer lower-level languages like C, but since C# is on the short list of programming languages in most demand, I set out to learn it and the best way to learn it is through a major project.

Besides, the other key language in demand was C++, but the latest version of Visual Studio I could install was 2010, and that year Microsoft didn't include IntelliSense for C++.

Word games

At the same time I was re-exploring my interest in word games like Scrabble and Words with Friends. The Scrabble community has been transformed a bit since the last time I was serious about it. The word list has been updated twice since the turn of the century and now Hasbro/Merriam claims copyright on the word list and is enforcing that claim. As a result many of the study tools have been left by the wayside, including Lexpert, the one I used quite often.

So I decided to write an application that would go toward replacing some of the study tools that had been abandoned. Hoot was born as a result. Since the word list used in Scrabble is affectionately called the Official Word List (OWL), I decided on Hoot as a suitable name for the program. After about 6 weeks, Hoot is available for public consumption and constructive criticism.

Hoot Features

Hoot will do most of the functions of the program I used previouslyd With Hoot you can search for words based on beginning or ending letter(s), the size of the word, the words that are contained (subwords), or include the word (extensions), anagrams, and well ...

The provided help file contains this listing of searches you can do in Hoot.

 

  • Beginners - Shortcut lists for Scrabble beginners
  • Letter Count - Lists by number of letters in the word. Can filter by initial letter
  • Begins/Ends - List of words beginning or ending with a certain letter, prefix or suffix. Select from list or enter in box. 
  • Contains Letters - Lists that contain any of the letters entered
  • Anagrams - List of words that can be made by rearranging letters in the words. Includes sub-anagrams, super-anagrams, standard stems.
  • Stems - List of words that can be rearranged after adding a letter to form several other words.
  • Hooks - List of words that can be formed by adding a letter to the front or back of a word
  • Anagram Hooks - List of words that can be formed by rearranging the letters in the search and adding a hook letter 
  • Extensions - List of words that contain a certain word
  • SubWords - List of words found within a given word
  • Pattern - List of words that match a certain pattern
  • Parallel Plays - List of words that can be played parallel to a word
  • Vowel Heavy - List of words with lots of vowels
  • No Vowel - List of words with no vowels
  • Consonant Dumps - List of words with lots of consonants
  • Q not U - List of words containing a Q, but not a U
  • Palindromes - List of words spelled the same frontward and backward.
  • Named Lists - Custom lists or various subjects
  • Text File Lists - Custom lists saved in a text file.

Most windows also have a Show hooks option that will display the letters that can be added in front or behind the word to form another word.
For example, the word AX is alternately displayed as

flmprstwz AX e

As users try out Hoot they may also discover another feature. As the search screen windows are resized, the list and controls respond to the size, so you could make the list a long skinny one to see more words at once, or a shorter one to save screen space.

Searches also have a slide show mode, where words in a list are displayed individually in a separate window, and
optionally rotated on a timed basis.

Hoot also has a Word Judge screen to judges the validity of a play. The Word Judge can be locked, and displayed separately from the program using a commandline option.

Hoot Shortcomings

Of course, there are many shortcomings, simply because incorporating the features takes time. You might also refer to these as plans for future releases if I continue development.

Searches are generally displayed first by word length and then alphabetically. They cannot be re-sorted unless you use the slide show.

You can change a few things about the display, but more customizations can be added.

One of the plans is to allow users to hide words they already know.

Exporting: You can't export words from the list.

Access as a database, which includes some SQL limitations.

Multiple windows, which some people may find handy, but an optional single screen would appeal to a lot of people.

A more intelligent installer (Wix).

Learning Curve

From a programmer's point of view, I've learned a lot about C# and the .NET framework. C# is very similar to Java, and the .NET framework is quite extensive. As a Microsoft project, I am quite familiar with navigation from previous experience with VBA. Some of the things that I excitedly learned to do include

  • Opening other databases (by changing the connection string run-time).
  • Managing and minimizing spikes in memory use.
  • Using ListBoxes, Lists, TableAdapters, and old-style OLE db access routines. 
  • Develop fast, efficient searching algorithms. Learn what to do when.
  • Filtering keystrokes.
  • Managing window states and events.

And that's just off the top. It was/is an educational experience, and I enjoy education. 

Download

Download the installer or the help file that describes the features from www.tylerhosting.com/hoot/

]]>
https://tylerhosting.com/b2e/dbell.php/hoot-word-game-study-tool#comments https://tylerhosting.com/b2e/dbell.php?tempskin=_rss2&disp=comments&p=372
Excelling With Access https://tylerhosting.com/b2e/dbell.php/excelling-with-access Fri, 16 Jan 2015 15:04:00 +0000 Dana Bell Information Technology Business Accounting Microsoft Office Access Excel 343@https://tylerhosting.com/b2e/
Deprecated: Creation of dynamic property Item::$cache_has_content_parts is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 4911

Deprecated: Creation of dynamic property Item::$renderers_validated is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 11107

Deprecated: Creation of dynamic property Item::$pages is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 2412

Deprecated: Creation of dynamic property ItemSettings::$count_col_key_names is deprecated in /home4/tylering/public_html/b2e/inc/settings/model/_abstractsettings.class.php on line 121
<h1>Introduction</h1> <p>If all of your financial records are maintained in Excel spreadsheets then you may be missing out on many of the advantages of using Access databases. Microsoft Access may already be available in your version of Microsoft Office. It is a component of Office Professional (and some other versions) and a non-development version of the Access runtime is downloadable from Microsoft. It is also available separately. The biggest investment would be the time to learn or have someone trained to develop databases with it.</p> <p>You will not want to convert all of your information to an Access database, of course. Access and Excel have different strengths and the two can be integrated. You can use Access for some needs and Excel for others. You can develop Excel spreadsheets and use them later to develop (upgrade to) an Access database. You can even use Excel to analyze data stored in Access or export Access data to Excel. The reason so many people use Excel for everything is that so few people know how to use Access, and vice-versa. Companies are actually moving away from Excel for some tasks, and using standalone products for those functions. In many cases, developing an Access database could be a more effective, economical, and versatile alternative.</p> <p>There have been other articles<a title="" href="#_ftn1">[1]</a> written about the choice between Excel and Access, and the benefits of each. Some good points have been made, but others are a bit more superficial. This may not remedy that, but the additional insight may help you develop a plan that fits your company or organization. One guideline<a title="" href="#_ftn2">[2]</a> I’ve read has been that Excel is better for numbers than text. Excel does have more advanced statistical functions and it can be a better tool for data analysis. However, Access is an excellent resource for creating complex financial reports, and makes it easier for multiple users to access the data. Both have their strengths and weaknesses, and you are not limited to using just one.</p> <h1>Excel Spreadsheets</h1> <p>Microsoft Excel and other forms of spreadsheets are everywhere, and access to them is often built into other software, such as the Document Management System in Lacerte. Other programs, like Microsoft Word have the ability to embed spreadsheets in their documents. Other companies provide on-line access to their spreadsheet applications at no cost. Workers are so accustomed to working with Excel it would be unthinkable to be without the self-calculating grids in a spreadsheet.</p> <h2>Advantages</h2> <p>As previously mentioned Excel has an extensive collection of built-in formulas and is particularly effective in statistical analysis. It is also useful in what-if scenarios, cost-benefit analysis, and financial calculations. Excel can accommodate any type of information that can be organized in rows and columns. Some reasons to consider Excel over another option may include</p> <h3>Data &amp; Features</h3> <ul> <li>Each cell in a spreadsheet can contain different types of data.</li> <li>Cells in a spreadsheet can calculate a value and include references to other cells in the spreadsheet.</li> <li>Spreadsheet cells are self calculating.</li> <li>Detailed formatting options allow you to format every cell in the spreadsheet separately to meet your needs.</li> </ul> <h3>Learning</h3> <ul> <li>Learning Curve. The basics of Excel can be learned easily and quickly, whether self-taught or through formal training.</li> <li>Training availability. Classes on Excel are readily available at colleges and universities, as well as on-line.</li> <li>Multiple levels of expertise. While users with basic understanding of Excel can use Excel, more advanced experts can find other features of Excel useable.</li> </ul> <h3>Usability</h3> <ul> <li>Familiarity. Excel doesn’t require extensive training to gain a basic understanding of spreadsheets. Anyone with some office training will likely have learned something about Excel and can use Excel on their first day on the job.</li> <li>Accessible. Any user can create a spreadsheet; they don’t have to wait for someone else to develop it or use special software to create spreadsheets.</li> <li>Availability. Excel is standard on most every business computer.</li> <li>Standardized. Spreadsheets may be consider the industry standard for financial information reporting and analysis, and Excel is the most common format.</li> <li>Portability. Excel spreadsheets are portable. Users can pass around spreadsheets internally, email, or post them for upload or download on an internal or external website. Many applications can open or view Excel spreadsheets. Applications that don’t read/write Excel often can import and/or export in a spreadsheet format. Sometimes that format is in <a href="http://www.tellmewhatis.com/csv-comma-separated-values">csv</a>, which is not a true spreadsheet, but Excel is usually the default application that opens csv files.</li> </ul> <h3>Features</h3> <ul> <li>Formula Library. There is an extensive library of functions that can be used.</li> <li>Graphics. A wide variety of graphic features and charts are easy to create.</li> <li>Expandability - Advanced programming capabilities are built-in to Excel and include macros and a special version of the Visual Basic programming language.</li> </ul> <p>Excel is considered to be the one software package that office professionals and particularly prospective accounting staff will need to know. A candidate's qualifications for a job will obviously include several areas of accounting, but the prime candidates should have an expert knowledge of Excel.</p> <p>What constitutes an expert knowledge can be debated, though. From an accountant's point of view, that would include knowing many of the financial and formatting formulas, a few special features of Excel (Pivot Tables, etc.), and proficiency with basic functions and shortcuts so that he will be able to create and modify spreadsheets quickly. For an IT professional those may be just the basic requirements, and real expertise would involve writing macros, creating forms, and writing VBA code to enhance the spreadsheet, simplify use, and integrate with other applications or data sources.</p> <p>Even so, the need for Excel is unquestionable. But from a practice management perspective, Excel’s value may be overstated. Excel has many limitations and risks.</p> <h2>How Spreadsheets Work</h2> <p>Everyone reading this probably knows how Excel works, but for comparison later, this is a short version of how spreadsheets allow us to do the work we do.</p> <ul> <li>A spreadsheet has many cells arranged in rows and columns.</li> <li>Each cell can have formatting, formula, and can refer to other cells in calculations.</li> <li>Rows typically represent records with columns representing characteristics (fields) of the record.</li> <li>Alternate display of information is possible in a spreadsheet with Pivot tables and charts.</li> <li>Workbooks can have multiple spreadsheets with cells that refer to cells in other spreadsheets.</li> </ul> <h2>Limitations</h2> <p>Excel has limitations on what it can do easily. While more advanced users can create complex spreadsheets, many users will limit their use to the more basic features of Excel. This is particularly true when it comes to structured or three dimensional data. When users ignore the limitations or stretch the capabilities of Excel or their own abilities, the resulting spreadsheet may be prone to errors. In some cases, it might be better to consider a database, especially if the requirements are complex and the same type of data is frequently used by other clients. Limitations of Excel may include the following.</p> <ul> <li>Relational data. Excel doesn’t handle relational data very well. The two-dimensional ability of spreadsheets may not be able to consider multiple related variables.</li> <li>Data structure modifications. Excel cannot easily grow with changes in data structure. Changes in data structure may require many modifications to many cells in order to adapt to new requirements.</li> <li>Spreadsheet maintenance. Reusable spreadsheets also have limitations. When someone develops a spreadsheet and then hands over the spreadsheet to someone else to use as a template, there is no control over what changes may be made, and if they are made properly. Improvements in the original also cannot easily be distributed to successors of the previous version of the spreadsheet.</li> <li>Data synchronization. If the same spreadsheet is updated by other users, there is no easy way to merge or synchronize the independently developed spreadsheets.</li> <li>Version control. With spreadsheet distributions there is no version control so that there may be many spreadsheets based on an original that are not updated with the latest calculations.</li> <li>Backups. Spreadsheets are typically loosely stored on user systems and there may not be specific backup procedures that protect and verify the data.</li> <li>Don’t forget to save! Excel must be regularly saved to disk whereas Access databases save entered data after entering each record.</li> </ul> <h2>Risk</h2> <p>In addition to the limitations, and often because of the limitations, Excel contains potential risks.</p> <h3>Security</h3> <p>Security is one risk associated with indiscriminate use of Excel. Since spreadsheets are so easily shared, confidentiality of data may be a concern, as with any standalone document. Likewise, as with other documents, loss could be a concern if there isn’t a reliable IT policy in place for backups and data security.</p> <h3>Errors</h3> <p>While security is the concern of many professionals, the biggest risk may be simply the errors that spreadsheets may contain. The actual data may not be reliable, or information produced from the data may be inaccurate. Much has been written about the prevalence of errors in business spreadsheets. Some articles have estimated that almost 90% of spreadsheets have errors,<a title="" href="#_ftn3">[3]</a> and the risks involve the possibility of material errors in a report. Further, one paper<a title="" href="#_ftn4">[4]</a> analyzing errors in various companies found that errors were common at every level of the organization and that people are only moderately good at correcting them.</p> <p>Errors can also be made when creating a spreadsheet, when using it, or when making modifications to update or customize it. The errors can be associated with using the wrong formulas, the wrong fields, entering data outside of a formula’s field range, or improper formatting of numbers. Some errors could occur as spreadsheets age. If the formula uses a common value (hard-coded) in a formula and that value changes, the formula has to be changed. One example may be a tax return analysis that includes 7.5% as the floor for medical expenses. The risk is greater with more people in the department, and the wider the distribution of the spreadsheet.</p> <h3>Types of Errors</h3> <p>Errors can range from simple mistyping to errors in more complicated formulas. One article<a title="" href="#_ftn5">[5]</a> analyzed spreadsheet error using this list of common error types.</p> <ol start="1"> <li>Hard-coding in a formula – one or more numbers appear in formulas</li> <li>Reference error – a formula contains one or more incorrect references to other cells</li> <li>Logic error – a formula is used incorrectly, leading to an incorrect result</li> <li>Copy/Paste error – a formula is wrong due to inaccurate use of copy/paste</li> <li>Omission error – a formula is wrong because one or more of its input cells is blank</li> <li>Data input error – an incorrect data input is used</li> </ol> <p>In general, errors can be segregated into those created during development of a spreadsheet, those that are the result of usage, or a combination of the two.</p> <h3>Development Errors</h3> <p>Development Errors include the actual errors made during spreadsheet creation, although many of those can be eliminated with company-wide development and usage standards.</p> <h4>Logic and Formula Errors</h4> <p>Logic and formula errors may be the most common types of errors made during development. While formula errors are inherently logical errors, other errors could be the result of business logic errors, or incorrect interpretation or legal requirements.</p> <p>Formula errors are particularly onerous because they can cascade across a spreadsheet and incrementally distort the reporting for long periods of time. This is particularly true when formulas reference formulas in other cells. Dependencies must continually be evaluated when spreadsheets are updated or re-purposed. A lack of understanding of differences between similar formulas can produce accurate records initially and later change as more data or more types of data are entered. A formula that gets an average by taking a total and dividing by a count of cells would usually be an inappropriate use of Excel formulas.</p> <h4>Operational Errors</h4> <p>Operational errors refer primarily to the use of spreadsheets. Some errors may be made by using the wrong spreadsheet for a particular task, or for the wrong client. Spreadsheets should always clearly document their purpose and any assumptions that are not defined in formulas.</p> <p>Reproduction is another risk of Excel spreadsheets. In one of my jobs we used an initially standardized worksheet for each client. However, sometimes it was duplicated from a previous year, and sometimes developed from the template. Often the inherited spreadsheet had issues, either with enhancements or overridden formulas.</p> <p>In many cases these spreadsheets are re-used from year to year or by different employees and for different clients. Unfortunately, when an error is introduced into a formula and not discovering it until later, there may not be a trail for finding errors in previously shared worksheets. The more complex and the more community oriented the worksheet the less likely it will be discovered early. Other types of errors are also discussed in a paper presented by Panko.<a title="" href="#_ftn6">[6]</a></p> <h3>Usage Errors</h3> <p>Not only are errors possible in the development of a spreadsheet and improper operational standards, spreadsheet usage could be a source of inaccurate spreadsheet calculations. There are many possible usage errors, but the most destructive ones are those involving unprotected cells.</p> <h4>Unprotected Cells</h4> <p>If a spreadsheet is used by others, there is a risk that a user deletes or overrides a formula. When formula cells are unprotected, at every use of the spreadsheet there is a potential of corruption somewhere on the spreadsheet. Formula cells can be protected in Excel but the process is seldom done for in-house spreadsheets because it is so time-consuming.</p> <h4>Blended Errors</h4> <p>Combining development errors with usage errors may be another source of corruption. The coding may be correct if the user uses it as designed, but there may not be any error-checking. For example, a macro that relies on a hard-coded value to insert rows could be rendered worthless or destructive if the user manually inserts or deletes rows.</p> <h3>The Hidden Costs</h3> <p>There have been estimates of the cost of errors but the actual costs associated with spreadsheets are often not known. The financial impact of most errors may be minimal, but the risk is still there if there aren’t policies in place to monitor spreadsheet development and maintenance. Even when the actual errors are small, when they are part of a formula, the effects can be cumulative.</p> <p>The biggest risk may be that errors can go undetected for years. Excel can help you find errors in a formula’s syntax and it does provide warning tips about cells, but it doesn’t know if the formula accurately summarizes the information the user needs. Errors are possible in the simplest spreadsheets. The more a user strays from the primary features of Excel, and tries to use a spreadsheet like a database (Risks of Using Excel as a Database<a title="" href="#_ftn7">[7]</a>), the greater the risk.</p> <p>“If the spreadsheet is ridiculously complex and impossible to read then nobody has a chance of actually reading it – and determining if there are any errors of it in the first place. That just becomes impossible."<a title="" href="#_ftn8"><strong>[8]</strong></a></p> <h3>Error Detection</h3> <p>Detecting errors can be as simple as regularly checking formulas being used in a spreadsheet, but there are other methods for detecting errors.</p> <h4>Formula Auditing</h4> <p>Show formulas. When developing a spreadsheet, Ctrl-` will toggle between formula and values. This will enable you to see which values have formulas. A cursory view of each formula may help verify its accuracy. This toggle can also enable you to spot when a column of formulas has been interrupted with a value typed into one of the formula cells. When editing a formula, selecting a cell reference will highlight the range being used, further verifying the proper reference. Newer versions of Excel feature additional operations that will evaluate formulas checking for obvious errors, trace errors, or trace references from or to a given cell.</p> <h4>Peer Review or Collaboration</h4> <p>One of the most effective means of error detection is peer review, or having someone else that is knowledgeable about the project at hand to closely look at the spreadsheet. If the project is one that can be useful to others, collaboration may need to be considered. A team project subjects each member to the scrutiny of other members.</p> <h4>IT Auditing</h4> <p>While most CPA firms spend some time performing audits for their clients during the tax off-season I wonder how many of them audit the IT processes of those clients. Even more critical how many of those firms who are involved in peer reviews of their operations have audits done on their own IT operations. An IT audit will not only look at potential spreadsheet issues, but uncover security and other IT risks. The CPA profession has high standards to maintain in providing their services.</p> <h3>Error Prevention</h3> <p>Identifying errors and the ways errors are creating will be the first step to preventing errors. That goal can be achieved at two levels. Create a system to follow in developing and controlling spreadsheet development. A company-wide standard will be the most effective in developing accurate and functional spreadsheets. The following guidelines are given in “How do You Know Your Spreadsheet is Right?”<a title="" href="#_ftn9">[9]</a>:</p> <ul> <li>Draft effectively before creating a spreadsheet.</li> <li>Know who sponsors, develops, reads and audits each document.</li> <li>Create a prototype that allows developers to refine definitions and user-friendliness in the real version.</li> <li>Create a spreadsheet with change in mind, as most sheets develop over time.</li> <li>Keep reports, input and logistics in separate areas of the spread.</li> <li>Opt for one formula per row or column.</li> <li>Use links to other sheets and external sources wisely.</li> </ul> <p>Secondly, each individual should develop good skills and practices when creating spreadsheets. Consider these few, more specific, guidelines for developing spreadsheets.</p> <h4>Field Naming</h4> <p>Naming fields and variables will improve the clarity of many functions that refer to other cells or ranges. Even better, Excel will keep a list of all names used and you can go to them from the Name Box just left of the formula bar. You can also use the Name Manager to review names and their references.</p> <p>The field naming advantages are also effective when using tables in Excel 2007 and later. Tables (and their named reference) can adjust for insertions, deletions, and still maintain boundaries.</p> <p>Avoid hard-coded values in formulas. Never assume a value will never change. If a value represents a set amount or percentage, create a separate cell for that value, and improve on that by naming the variable. A lookup section can be useful for collections of values that are easy to change, and lookup when needed. State’s sales tax rates would be one example.</p> <h4>Protection</h4> <p>After completing development of a spreadsheet, protect it and any formulas. Even if you use a universal company password, protection will help prevent accidental overwriting of formulas used in the spreadsheet. More advanced restrictions could also define what information is relevant in a spreadsheet.</p> <h4>Questions to Ask</h4> <p>There are many ways to evaluate spreadsheet development. This is a simplified series of questions you might ask about new spreadsheets.</p> <h5>Do you need a spreadsheet?</h5> <p>Create spreadsheets to get new information instead of just transferring your existing information to Excel.</p> <h5>Are you using the right information?</h5> <p>Know what information you have to work with, and if it contains what you need to get useful information.</p> <h5>Are you using the right formulas?</h5> <p>A formula that uses Sum/Count may not get an average if values are missing from the range being used in the calculation. Using average may (or may not) be the function you want. Statistical formulas in particular require values that meet a certain range of values.</p> <h5>Are you using the formulas right?</h5> <p>While Excel will accept the entry of a formula, it can’t tell if the arguments in the formula have been swapped. Incorrect cell references will also make even the best thought out formula generate meaningless information.</p> <h5>Are you getting the right information?</h5> <p>If the information you are getting doesn’t make sense, you may need to verify formulas or re-examine the logic behind the calculations.</p> <h2>Development and Maintenance</h2> <p>Development of spreadsheets is typically task-oriented. When a new task presents itself a new spreadsheet is developed and put to use for that person or project. For that reason, the skill set required to create spreadsheets is not much different from simply using spreadsheets and the line between use and development of spreadsheets is blurred. This is particularly true if the spreadsheet simply totals multiple columns of data.</p> <p>Often complex tasks require more complex worksheets. While someone with basic spreadsheet skills may devote considerable time with such projects, there is a significant difference between the skill set required to develop complex spreadsheets and a user with basic knowledge. Not only will such projects be more time-consuming (cost-benefit ratio), they are prone to errors. The user/developer mentality is so common that the concept of developing and maintaining spreadsheets may seem foreign to many managers. The error level of average spreadsheets, however, accents the need to rethink the development process of Excel and spreadsheets and consider demarcation.</p> <p>Improving quality control of Excel spreadsheets is mentioned here only briefly. For more information review the articles included in the footnotes. “Are Your Excel Spreadsheets Accurate?” mentions some basic ways to guard against errors and detect errors in a spreadsheet.<a title="" href="#_ftn10">[10]</a> “Is your Love for Excel Misplaced?” looks at a few things that may help to identify reasons for inaccuracies including the need to define the purpose of a spreadsheet before creating it.<a title="" href="#_ftn11">[11]</a></p> <p>Standards have also been developed for spreadsheet development and are available on the Spreadsheet Standards Review Board website.<a title="" href="#_ftn12">[12]</a> Related to this organization is a structured site devoted to Excel maintenance called “Best Practice Modeling.”<a title="" href="#_ftn13">[13]</a> Site pages discuss Excel development, including a description of auditing tools available, Modular Development, and a Knowledge Base of industry specific applications. While a system can help to organize the development process, it is the individual developer, whether secretary or IT professional that creates spreadsheets and a guide on “How to Make Spreadsheets Error-Proof”<a title="" href="#_ftn14">[14]</a> could be a good initial step. “How do you know your spreadsheet is right?” presents some best practices to use in developing spreadsheets. <a title="" href="#_ftn15">[15]</a></p> <p>Much can be done to improve quality of Excel-generated information but there are cases where a database might be more appropriate.  </p> <h1>Access Databases</h1> <p>Databases are similar to spreadsheets in that data is often represented in rows and columns. The biggest difference is that database information is relational. Each table in a database is typically related to one or more other tables in the database. Relationships are usually one-to-many. Thus, for each client, there may be one or more invoices. For each invoice there may be one or more items. For each employee there may be one or more clients he is responsible for. Unlike a spreadsheet, the data entered is separate from the way it is gathered or presented, so the cells do not have display formatting associated with them. Forms and reports, however, can display the data in many different ways.</p> <p>Databases are the primary sources for Enterprise Resource Planning (ERP) packages, in part because they are highly efficient at data management. On a local level, databases can also be created on an individual PC and shared on the local network using Microsoft Access. Microsoft Access provides users with all the tools for developing functional databases. Whether or not you have a network database or other ERP, learning how to develop using Access may fill the need for many tasks.</p> <h2>History</h2> <p>In the earlier times, data processing was separate from business functions of a company. Computer programmers developed programs that users accessed to generate reports. Most of those programs were database-based. Business users didn’t concern themselves with formatting and output. Their job was to enter data, and request suitable reports from that data. When the first business PC showed up, the first major spreadsheet (Visicalc) helped users to get many of their financials done without an IT department. Lotus 1-2-3 soon followed and then Excel.</p> <p>As focus moved to spreadsheets, companies found a way to eliminate, downsize, or refocus their IT department. As a result IT-developed databases were replaced by desktop produced spreadsheets. Today, many of the larger companies have ERP systems which are replacing some of the spreadsheet applications. The software industry has also developed specialized database software to handle specific tasks. In accounting today, database software handles tax services, audit functions, and many bookkeeping and financial accounting functions. Progressive companies may do well to bring database-driven information back into other areas.</p> <h2>Database Advantages</h2> <p>Excel and spreadsheets have a useful purpose in an organization, but there are times when a database is more appropriate use of resources. When appropriate, the advantages of using an Access database are multi-faceted. The following categories illustrate the benefits of an Access database.</p> <h3>Data Management</h3> <ul> <li>Data efficiency. A single database can handle the tasks of many duplicate spreadsheets.</li> <li>Data growth. Unlike a spreadsheet that becomes unwieldy as it grows, databases operations are generally not affected by growth in the size of the database.</li> <li>Integration. A database can handle many different tables of different types of information and integrate all of them. General information about a client can be stored with financial transactions of the client. Everything is (should be) linked to everything else.</li> <li>Pictures. Pictures, web links, and other objects can be stored in Access as part of a record. Links to separate supporting documents can also be added to a record.</li> <li>Joint databases. A database can reference tables in a separate database. A user may or may not be able to add records in a supporting database. This is useful if some of the data is more confidential or security is important.</li> <li>Multi-level access. It’s possible to limit access to some data in a database on a user-by-user basis. Limits could be inability to change, or inability to view confidential data by some users.</li> <li>Consistent calculations. Calculations in Excel are cell by cell. If a cell is missing a formula in a column the totals will be incorrect. In a database, the total can be generated by multiplying two fields for each record, without having to assign a formula to each cell in the table.</li> </ul> <h3>Usability (ease of use)</h3> <ul> <li>Easy to use. Access databases are easy for users to enter relevant data, since the development is done prior to deployment.</li> <li>Navigation. Access databases are typically designed to display forms for data entry with set navigation controls. This is better than trying to enter data in a long string of cells in a grid.</li> <li>Data entry forms. Although it is possible to create a data entry form in Excel, forms are the primary tools in Access for accepting data from users. If source documents vary, separate forms can be used to enter data from source documents without having to change the database table structures.</li> <li>Formula protection. Database calculations are separate from data entry, so there is no concern about writing over a cell formula with a constant value.</li> </ul> <h3>Structure</h3> <ul> <li>Access can represent multiple dimensions, while Excel is typically limited to two. In Excel, for each account you would have rows and columns. For additional accounts you would add another spreadsheet or section.</li> <li>All cells in a column are the same. In Excel, cells in a column of table data (fields) are formatted individually. In an Access database, the formatting of a field is the same for all records.</li> <li>Data types must be defined in Access, whereas Excel may (incorrectly) redefine data types, format, and precision based on data entered.</li> <li>Validation. In addition to data type definitions, data entry can be validated in Access for consistency.</li> <li>Primary keys. Every record in an Access database has a primary key that distinguishes that record from any other.</li> <li>Normalization. The development process of normalization in an Access database insures that tables are properly related to others and that data is stored efficiently.</li> <li>Referential integrity. Access can internally monitor the integrity of data in tables. If a value in a table references another table, the reference must exist.</li> <li>Cascading additions and deletions. Referential integrity also controls stray, orphan data. When a table adds or deletes a value with references to a related table, the related tables are added or deleted. For example, you can avoid having invoices for customers that no longer exist if you choose.</li> </ul> <h3>Reporting</h3> <ul> <li>Data and presentation are distinct layers. In IT, the separation of data and how it is presented are considered separate layers.</li> <li>Flexible reporting. Not only do separate data and presentation layers protect the data from accidental corruption (cell overwriting), reporting from an Access database is more flexible. When properly developed, reports can integrate data from many areas to provide more meaningful information. Using queries, you can get reports on select data or generate full reports of larger sets of data with multiple levels of subtotals, for example. Slice and dice.</li> <li>Same data, separate reporting. Because reports are separate from the data in a database, you can create a new report without having to make a copy of the data. In Excel, a different reporting format often means making a copy of the first spreadsheet and modifying the copy. At that point, you have two separate copies of the data that would need to be modified if the data changes.</li> <li>Reporting formats. You can export information from a database in a number of other formats, including Excel. Access does have the resources to prepare complex queries, but it doesn’t have all the statistical features of Excel, so you can store the data in Access and export to Excel to analyze if necessary.</li> </ul> <h3>Development and Maintenance</h3> <ul> <li>Longer development cycle. Often considered a disadvantage of databases, the longer development cycle reflects the nature of development. Spreadsheets are often not validated to insure they are accurate. Spreadsheet testing may be limited to formula syntax. Databases are generally tested to insure that they are functioning properly.</li> <li>The calculations and the data entry are separate. Users cannot alter calculations created in a report using data entry.</li> <li>Although a different type of expertise is required, Access databases can be more easily managed once developed. Similarly, automation through macros and VBA modules is available in Excel, but is more of a part of Access, with each form or report having its own code.</li> <li>Access databases can be centralized, with users sharing a single data source accessed over the internal network.</li> </ul> <h2>How does a database work?</h2> <ul> <li>Each table represents an entity and has multiple records.</li> <li>Each record must have a unique value (keys) to identify itself.</li> <li>Tables are linked with other tables through those keys.</li> <li>Forms and reports query the tables to get the information to be presented.</li> <li>Multiple reports can be designed using the same data, without having to alter that data.</li> </ul> <h3>Information Presentation</h3> <p>One characteristic of a database is in its layered process. In a spreadsheet, data and presentation information are integrated into each cell. In a database project there are at least two layers; the data layer and the presentation layer. By segregating the tasks of entering information and creating reports users can focus on just one area at a time. Furthermore, once the data is in, the information can be presented in any number of ways, with selected levels of detail.</p> <h2>Database Limitations</h2> <p>Extensive use of Access databases is not for everyone.  Databases may be a new process for many cases, and staff will have to adjust to the operations and limits. Databases separate the two processes, development and use, so access to the database will depend on whether the person has development skills. Other limitations include the following.</p> <ul> <li>Steeper learning curve. While use of a well-interfaced database can be a breeze, most any type of development or maintenance comes with a steep learning curve. Extensive training is required to teach someone to develop databases.</li> <li>Different expertise. Expertise Database development requires a different level of expertise and developers are not readily available. Developers must understand database concepts, not just the steps to navigate the environment and create formulas.</li> <li>Long development cycle. It is more time consuming to create a database than to create a spreadsheet. The upside is that changes are easier to make and the database may have many purposes.</li> <li>Commitment required. Not only does a company need to develop the database application, they should be able to maintain it, add features when needed, adjust for changes in policy or law, and make corrections when needed. Some databases can exist for years after development without maintenance, but that may not be the norm.</li> <li>Not error proof. Poorly designed databases, like poorly designed spreadsheets can appear to generate useful data. Test and retest.</li> <li>Reporting. Access reports on all data the same way. Without some specific coding, it’s not possible to highlight or add special formatting for specific value(s).</li> </ul> <h2>Database Risks and Control</h2> <p>There are risks associated with database development, and executives will not have as much control. While a senior partner can review spreadsheet formulas, he will likely not have the knowledge to check a database table, formula, or report for errors, other than possibly verifying the information with spreadsheet calculations.</p> <p>Errors in a database are not easy to detect by users other than double-checking the data entry. A comprehensive development process is needed to insure errors are detected and prevented prior to deployment of the database system. When centralized, loss of data can be catastrophic. Because a database contains large amounts of data, the potential loss is large. Incremental backups are crucial.</p> <h2>Database Development</h2> <p>Following the introduction of the PC in the mid-1980’s, with the limitation that not much software was available, one of the main reasons a company would hire a computer guy was to set up a database. Today, given the universal use of SQL and normalization, and the availability of affordable database software, the value and versatility of a database is greater than ever.  Most any size firm can begin the process if they have a resident database guru, either hired for that or trained in-house. While a company may not need a full-time IT department to begin using more Access databases, there must be someone available who has a good understanding of how to develop a database. A very simple database can be developed with minimal training but the real benefits of a database are based on a well-developed database program.</p> <p>Database Development training is not unlike training requirements of an Excel. Although formal training is not required extensive training is a requirement. It will require more than a single introduction to Access. A possible misconception is that a person can take a single class in database development and be able to handle all of a company’s database needs. Just as Excel experts gain their expertise through progressive experience and often several classes in the subject, Access training requires more than a rudimentary understanding of Access basics. Given the time and opportunity, however, a staff member can grow into being a database guru.</p> <p>You don’t have to invest in a dedicated IT department, but you do have to make an investment. Many managers may decide it’s not worth it, or that other projects are more in line with their business. You can begin a database team. Start with a few projects, evaluate, improve and refine them. Then integrate one or more of them. As you grow you can consider more complex applications, network databases, or web based (cloud) databases.</p> <h3>Identifying potential database projects</h3> <p>It’s not always apparent what project to consider for database development. One clue may be the level of frustration you have with a complex spreadsheet. The prevalence of task-oriented software is one indication of the power and flexibility of Access databases. A lot of such software is database driven and provides safeguards to many common risks. A good guide is to look at what things are done with separate software.</p> <h4>Tax Software</h4> <p>I wouldn’t recommend creating your own tax software in an Access database because it is so complex, but a look at what tax software includes may help you picture what the inside of an Access might look like. Tax database doesn’t just contain all the data for a single client. At end of tax-season, your tax software may contain 1000’s of completed forms with hundreds of fields for 100’s of clients in a single database. The fields of one form may be dependent upon the values other forms. Underlying the forms is the collection of related tables. Tax software selects only the information required from those tables for a particular client for a particular year and groups it into a single tax return.</p> <p>Similarly, any requirement that has multiple layers of the same type of data may be better represented in a database. Spreadsheets that consists of multiple levels of spreadsheet data is a prime candidate for an Access database.</p> <h4>Sectional Spreadsheets</h4> <p>Excel files may contain several (many) sections in the same spreadsheet, with each section using the same formulas. In one case I looked at, a survey project, modifying the spreadsheet was time-consuming. Formatting was fixed and analytical tools did not fit the fragmented format of the spreadsheet. It was essential many spreadsheets on one spreadsheet.</p> <h4>Workbooks</h4> <p>Another Excel strategy was to actually create multiple spreadsheets in the same workbook, with one spreadsheet acting as a summary of the others. I’ve seen over 50 spreadsheets tabs in a workbook and a full sheet of cross-spreadsheet formulas that summarized the other worksheets. As such the data is fragmented and producing the spreadsheets for distribution was very time-consuming. Just the pure volume of information and number of formula cells made it challenging, if not impossible, to insure the calculations were accurate. Not all workbooks are like this, but when repeating spreadsheets are common then a database may be a better choice.</p> <p>These types of worksheets are actually useable, although they require a very organized developer to use or manage it without introducing the potential for error and associated risks.</p> <h4>Management Tools</h4> <p>Access is not only an alternative to spreadsheets and other digital resources. In many cases, a database can be a new thing that improves management of a crucial element of important business processes. That has been the process that led me to develop databases for inventory control, order processing, and resource management, rather than use Excel as an intermediate step. As companies become more comfortable with databases and improve their internal development potential they will find new uses for this technology to save time and provide improved reporting for clients and within the company.</p> <h1>Conclusion</h1> <p>The first lesson to be learned from this is that companies need to establish a system of spreadsheet development, particularly when spreadsheets are distributed throughout the company. Occasional audits of spreadsheets will help to identify problems and risks associated with spreadsheets. Depending on the structure of the organization, consideration may need to be given to protecting some spreadsheets. In conjunction with implementing a quality control system for spreadsheet development, continuous training will help prevent users from getting stale on features of Excel that they do not use often.</p> <p>Secondly, sometimes a spreadsheet is not the right tool. If all you have is a hammer, everything looks like a nail. Sometimes you really need a screwdriver. Sometimes you need to have a database. If your firm or department is large enough or has the staffing, consider investing the time to train a couple of people in database development.</p> <p>This is a simplified coverage of Access databases. Without more exposure to databases you may not fully see the nature of development and daily use of databases. As time permits, more information may be posted to illustrate the effectiveness of internally developed Access database, the process of developing a database, and a program to train staff to develop and use databases.</p> <div><br clear="all" /><hr align="left" size="1" width="33%" /> <div> <p><a title="" href="#_ftnref1">[1]</a> <a href="http://www.fmsinc.com/microsoftaccess/DataAnalysis/versus-excel.html">http://www.fmsinc.com/microsoftaccess/DataAnalysis/versus-excel.html</a></p> </div> <div> <p><a title="" href="#_ftnref2">[2]</a> <a href="http://blogs.technet.com/b/hub/archive/2010/01/15/where-should-i-put-my-data-excel-or-access.aspx">http://blogs.technet.com/b/hub/archive/2010/01/15/where-should-i-put-my-data-excel-or-access.aspx</a></p> </div> <div> <p><a title="" href="#_ftnref3">[3]</a> <a href="http://www.marketwatch.com/story/88-of-spreadsheets-have-errors-2013-04-17">http://www.marketwatch.com/story/88-of-spreadsheets-have-errors-2013-04-17</a></p> </div> <div> <p><a title="" href="#_ftnref4">[4]</a> <a href="http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm">http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm</a></p> </div> <div> <p><a title="" href="#_ftnref5">[5]</a> <a href="http://www.strategy-at-risk.com/2009/03/03/the-risk-of-spreadsheet-errors/">http://www.strategy-at-risk.com/2009/03/03/the-risk-of-spreadsheet-errors/</a></p> </div> <div> <p><a title="" href="#_ftnref6">[6]</a> Panko, R. R., &amp; Halverson, R. P., Jr. (1996, January). Spreadsheets on Trial: A Framework for Research on Spreadsheet Risks. <em>Proceedings of the Twenty-Ninth Hawaii International Conference on System Sciences, </em>Maui, Hawaii.</p> </div> <div> <p><a title="" href="#_ftnref7">[7]</a> <a href="http://www.freerconsulting.com/news/excel">http://www.freerconsulting.com/news/excel</a></p> </div> <div> <p><a title="" href="#_ftnref8">[8]</a> <a href="http://www.cnbc.com/id/100923538">http://www.cnbc.com/id/100923538#</a>.</p> </div> <div> <p><a title="" href="#_ftnref9">[9]</a> <a href="http://arxiv.org/ftp/arxiv/papers/1301/1301.5878.pdf">http://arxiv.org/ftp/arxiv/papers/1301/1301.5878.pdf</a></p> </div> <div> <p><a title="" href="#_ftnref10">[10]</a> <a href="http://www.k2e.com/tech-update/articles/484-articles-are-your-excel-spreadsheets-accurate">http://www.k2e.com/tech-update/articles/484-articles-are-your-excel-spreadsheets-accurate</a></p> </div> <div> <p><a title="" href="#_ftnref11">[11]</a> <a href="http://www.proformative.com/articles/your-love-excel-misplaced">http://www.proformative.com/articles/your-love-excel-misplaced</a></p> </div> <div> <p><a title="" href="#_ftnref12">[12]</a> <a href="http://www.ssrb.org/links.html">http://www.ssrb.org/links.html</a></p> </div> <div> <p><a title="" href="#_ftnref13">[13]</a> <a href="https://www.bestpracticemodelling.com/resources?page=/resources/standards">https://www.bestpracticemodelling.com/resources?page=/resources/standards</a></p> </div> <div> <p><a title="" href="#_ftnref14">[14]</a> <a href="http://www.eusprig.org/hdykysir.pdf">www.eusprig.org/hdykysir.pdf</a><em> </em></p> </div> <div> <p><a title="" href="#_ftnref15">[15]</a> <a href="http://arxiv.org/ftp/arxiv/papers/1301/1301.5878.pdf">http://arxiv.org/ftp/arxiv/papers/1301/1301.5878.pdf</a></p> </div> </div><div class="item_footer"><p><small><a href="https://tylerhosting.com/b2e/dbell.php/excelling-with-access">Original post</a> blogged on <a href="http://www.tylerhosting.com/b2e/">Switched Keys</a>.</small></p></div>
Introduction

If all of your financial records are maintained in Excel spreadsheets then you may be missing out on many of the advantages of using Access databases. Microsoft Access may already be available in your version of Microsoft Office. It is a component of Office Professional (and some other versions) and a non-development version of the Access runtime is downloadable from Microsoft. It is also available separately. The biggest investment would be the time to learn or have someone trained to develop databases with it.

You will not want to convert all of your information to an Access database, of course. Access and Excel have different strengths and the two can be integrated. You can use Access for some needs and Excel for others. You can develop Excel spreadsheets and use them later to develop (upgrade to) an Access database. You can even use Excel to analyze data stored in Access or export Access data to Excel. The reason so many people use Excel for everything is that so few people know how to use Access, and vice-versa. Companies are actually moving away from Excel for some tasks, and using standalone products for those functions. In many cases, developing an Access database could be a more effective, economical, and versatile alternative.

There have been other articles[1] written about the choice between Excel and Access, and the benefits of each. Some good points have been made, but others are a bit more superficial. This may not remedy that, but the additional insight may help you develop a plan that fits your company or organization. One guideline[2] I’ve read has been that Excel is better for numbers than text. Excel does have more advanced statistical functions and it can be a better tool for data analysis. However, Access is an excellent resource for creating complex financial reports, and makes it easier for multiple users to access the data. Both have their strengths and weaknesses, and you are not limited to using just one.

Excel Spreadsheets

Microsoft Excel and other forms of spreadsheets are everywhere, and access to them is often built into other software, such as the Document Management System in Lacerte. Other programs, like Microsoft Word have the ability to embed spreadsheets in their documents. Other companies provide on-line access to their spreadsheet applications at no cost. Workers are so accustomed to working with Excel it would be unthinkable to be without the self-calculating grids in a spreadsheet.

Advantages

As previously mentioned Excel has an extensive collection of built-in formulas and is particularly effective in statistical analysis. It is also useful in what-if scenarios, cost-benefit analysis, and financial calculations. Excel can accommodate any type of information that can be organized in rows and columns. Some reasons to consider Excel over another option may include

Data & Features

  • Each cell in a spreadsheet can contain different types of data.
  • Cells in a spreadsheet can calculate a value and include references to other cells in the spreadsheet.
  • Spreadsheet cells are self calculating.
  • Detailed formatting options allow you to format every cell in the spreadsheet separately to meet your needs.

Learning

  • Learning Curve. The basics of Excel can be learned easily and quickly, whether self-taught or through formal training.
  • Training availability. Classes on Excel are readily available at colleges and universities, as well as on-line.
  • Multiple levels of expertise. While users with basic understanding of Excel can use Excel, more advanced experts can find other features of Excel useable.

Usability

  • Familiarity. Excel doesn’t require extensive training to gain a basic understanding of spreadsheets. Anyone with some office training will likely have learned something about Excel and can use Excel on their first day on the job.
  • Accessible. Any user can create a spreadsheet; they don’t have to wait for someone else to develop it or use special software to create spreadsheets.
  • Availability. Excel is standard on most every business computer.
  • Standardized. Spreadsheets may be consider the industry standard for financial information reporting and analysis, and Excel is the most common format.
  • Portability. Excel spreadsheets are portable. Users can pass around spreadsheets internally, email, or post them for upload or download on an internal or external website. Many applications can open or view Excel spreadsheets. Applications that don’t read/write Excel often can import and/or export in a spreadsheet format. Sometimes that format is in csv, which is not a true spreadsheet, but Excel is usually the default application that opens csv files.

Features

  • Formula Library. There is an extensive library of functions that can be used.
  • Graphics. A wide variety of graphic features and charts are easy to create.
  • Expandability - Advanced programming capabilities are built-in to Excel and include macros and a special version of the Visual Basic programming language.

Excel is considered to be the one software package that office professionals and particularly prospective accounting staff will need to know. A candidate's qualifications for a job will obviously include several areas of accounting, but the prime candidates should have an expert knowledge of Excel.

What constitutes an expert knowledge can be debated, though. From an accountant's point of view, that would include knowing many of the financial and formatting formulas, a few special features of Excel (Pivot Tables, etc.), and proficiency with basic functions and shortcuts so that he will be able to create and modify spreadsheets quickly. For an IT professional those may be just the basic requirements, and real expertise would involve writing macros, creating forms, and writing VBA code to enhance the spreadsheet, simplify use, and integrate with other applications or data sources.

Even so, the need for Excel is unquestionable. But from a practice management perspective, Excel’s value may be overstated. Excel has many limitations and risks.

How Spreadsheets Work

Everyone reading this probably knows how Excel works, but for comparison later, this is a short version of how spreadsheets allow us to do the work we do.

  • A spreadsheet has many cells arranged in rows and columns.
  • Each cell can have formatting, formula, and can refer to other cells in calculations.
  • Rows typically represent records with columns representing characteristics (fields) of the record.
  • Alternate display of information is possible in a spreadsheet with Pivot tables and charts.
  • Workbooks can have multiple spreadsheets with cells that refer to cells in other spreadsheets.

Limitations

Excel has limitations on what it can do easily. While more advanced users can create complex spreadsheets, many users will limit their use to the more basic features of Excel. This is particularly true when it comes to structured or three dimensional data. When users ignore the limitations or stretch the capabilities of Excel or their own abilities, the resulting spreadsheet may be prone to errors. In some cases, it might be better to consider a database, especially if the requirements are complex and the same type of data is frequently used by other clients. Limitations of Excel may include the following.

  • Relational data. Excel doesn’t handle relational data very well. The two-dimensional ability of spreadsheets may not be able to consider multiple related variables.
  • Data structure modifications. Excel cannot easily grow with changes in data structure. Changes in data structure may require many modifications to many cells in order to adapt to new requirements.
  • Spreadsheet maintenance. Reusable spreadsheets also have limitations. When someone develops a spreadsheet and then hands over the spreadsheet to someone else to use as a template, there is no control over what changes may be made, and if they are made properly. Improvements in the original also cannot easily be distributed to successors of the previous version of the spreadsheet.
  • Data synchronization. If the same spreadsheet is updated by other users, there is no easy way to merge or synchronize the independently developed spreadsheets.
  • Version control. With spreadsheet distributions there is no version control so that there may be many spreadsheets based on an original that are not updated with the latest calculations.
  • Backups. Spreadsheets are typically loosely stored on user systems and there may not be specific backup procedures that protect and verify the data.
  • Don’t forget to save! Excel must be regularly saved to disk whereas Access databases save entered data after entering each record.

Risk

In addition to the limitations, and often because of the limitations, Excel contains potential risks.

Security

Security is one risk associated with indiscriminate use of Excel. Since spreadsheets are so easily shared, confidentiality of data may be a concern, as with any standalone document. Likewise, as with other documents, loss could be a concern if there isn’t a reliable IT policy in place for backups and data security.

Errors

While security is the concern of many professionals, the biggest risk may be simply the errors that spreadsheets may contain. The actual data may not be reliable, or information produced from the data may be inaccurate. Much has been written about the prevalence of errors in business spreadsheets. Some articles have estimated that almost 90% of spreadsheets have errors,[3] and the risks involve the possibility of material errors in a report. Further, one paper[4] analyzing errors in various companies found that errors were common at every level of the organization and that people are only moderately good at correcting them.

Errors can also be made when creating a spreadsheet, when using it, or when making modifications to update or customize it. The errors can be associated with using the wrong formulas, the wrong fields, entering data outside of a formula’s field range, or improper formatting of numbers. Some errors could occur as spreadsheets age. If the formula uses a common value (hard-coded) in a formula and that value changes, the formula has to be changed. One example may be a tax return analysis that includes 7.5% as the floor for medical expenses. The risk is greater with more people in the department, and the wider the distribution of the spreadsheet.

Types of Errors

Errors can range from simple mistyping to errors in more complicated formulas. One article[5] analyzed spreadsheet error using this list of common error types.

  1. Hard-coding in a formula – one or more numbers appear in formulas
  2. Reference error – a formula contains one or more incorrect references to other cells
  3. Logic error – a formula is used incorrectly, leading to an incorrect result
  4. Copy/Paste error – a formula is wrong due to inaccurate use of copy/paste
  5. Omission error – a formula is wrong because one or more of its input cells is blank
  6. Data input error – an incorrect data input is used

In general, errors can be segregated into those created during development of a spreadsheet, those that are the result of usage, or a combination of the two.

Development Errors

Development Errors include the actual errors made during spreadsheet creation, although many of those can be eliminated with company-wide development and usage standards.

Logic and Formula Errors

Logic and formula errors may be the most common types of errors made during development. While formula errors are inherently logical errors, other errors could be the result of business logic errors, or incorrect interpretation or legal requirements.

Formula errors are particularly onerous because they can cascade across a spreadsheet and incrementally distort the reporting for long periods of time. This is particularly true when formulas reference formulas in other cells. Dependencies must continually be evaluated when spreadsheets are updated or re-purposed. A lack of understanding of differences between similar formulas can produce accurate records initially and later change as more data or more types of data are entered. A formula that gets an average by taking a total and dividing by a count of cells would usually be an inappropriate use of Excel formulas.

Operational Errors

Operational errors refer primarily to the use of spreadsheets. Some errors may be made by using the wrong spreadsheet for a particular task, or for the wrong client. Spreadsheets should always clearly document their purpose and any assumptions that are not defined in formulas.

Reproduction is another risk of Excel spreadsheets. In one of my jobs we used an initially standardized worksheet for each client. However, sometimes it was duplicated from a previous year, and sometimes developed from the template. Often the inherited spreadsheet had issues, either with enhancements or overridden formulas.

In many cases these spreadsheets are re-used from year to year or by different employees and for different clients. Unfortunately, when an error is introduced into a formula and not discovering it until later, there may not be a trail for finding errors in previously shared worksheets. The more complex and the more community oriented the worksheet the less likely it will be discovered early. Other types of errors are also discussed in a paper presented by Panko.[6]

Usage Errors

Not only are errors possible in the development of a spreadsheet and improper operational standards, spreadsheet usage could be a source of inaccurate spreadsheet calculations. There are many possible usage errors, but the most destructive ones are those involving unprotected cells.

Unprotected Cells

If a spreadsheet is used by others, there is a risk that a user deletes or overrides a formula. When formula cells are unprotected, at every use of the spreadsheet there is a potential of corruption somewhere on the spreadsheet. Formula cells can be protected in Excel but the process is seldom done for in-house spreadsheets because it is so time-consuming.

Blended Errors

Combining development errors with usage errors may be another source of corruption. The coding may be correct if the user uses it as designed, but there may not be any error-checking. For example, a macro that relies on a hard-coded value to insert rows could be rendered worthless or destructive if the user manually inserts or deletes rows.

The Hidden Costs

There have been estimates of the cost of errors but the actual costs associated with spreadsheets are often not known. The financial impact of most errors may be minimal, but the risk is still there if there aren’t policies in place to monitor spreadsheet development and maintenance. Even when the actual errors are small, when they are part of a formula, the effects can be cumulative.

The biggest risk may be that errors can go undetected for years. Excel can help you find errors in a formula’s syntax and it does provide warning tips about cells, but it doesn’t know if the formula accurately summarizes the information the user needs. Errors are possible in the simplest spreadsheets. The more a user strays from the primary features of Excel, and tries to use a spreadsheet like a database (Risks of Using Excel as a Database[7]), the greater the risk.

“If the spreadsheet is ridiculously complex and impossible to read then nobody has a chance of actually reading it – and determining if there are any errors of it in the first place. That just becomes impossible."[8]

Error Detection

Detecting errors can be as simple as regularly checking formulas being used in a spreadsheet, but there are other methods for detecting errors.

Formula Auditing

Show formulas. When developing a spreadsheet, Ctrl-` will toggle between formula and values. This will enable you to see which values have formulas. A cursory view of each formula may help verify its accuracy. This toggle can also enable you to spot when a column of formulas has been interrupted with a value typed into one of the formula cells. When editing a formula, selecting a cell reference will highlight the range being used, further verifying the proper reference. Newer versions of Excel feature additional operations that will evaluate formulas checking for obvious errors, trace errors, or trace references from or to a given cell.

Peer Review or Collaboration

One of the most effective means of error detection is peer review, or having someone else that is knowledgeable about the project at hand to closely look at the spreadsheet. If the project is one that can be useful to others, collaboration may need to be considered. A team project subjects each member to the scrutiny of other members.

IT Auditing

While most CPA firms spend some time performing audits for their clients during the tax off-season I wonder how many of them audit the IT processes of those clients. Even more critical how many of those firms who are involved in peer reviews of their operations have audits done on their own IT operations. An IT audit will not only look at potential spreadsheet issues, but uncover security and other IT risks. The CPA profession has high standards to maintain in providing their services.

Error Prevention

Identifying errors and the ways errors are creating will be the first step to preventing errors. That goal can be achieved at two levels. Create a system to follow in developing and controlling spreadsheet development. A company-wide standard will be the most effective in developing accurate and functional spreadsheets. The following guidelines are given in “How do You Know Your Spreadsheet is Right?”[9]:

  • Draft effectively before creating a spreadsheet.
  • Know who sponsors, develops, reads and audits each document.
  • Create a prototype that allows developers to refine definitions and user-friendliness in the real version.
  • Create a spreadsheet with change in mind, as most sheets develop over time.
  • Keep reports, input and logistics in separate areas of the spread.
  • Opt for one formula per row or column.
  • Use links to other sheets and external sources wisely.

Secondly, each individual should develop good skills and practices when creating spreadsheets. Consider these few, more specific, guidelines for developing spreadsheets.

Field Naming

Naming fields and variables will improve the clarity of many functions that refer to other cells or ranges. Even better, Excel will keep a list of all names used and you can go to them from the Name Box just left of the formula bar. You can also use the Name Manager to review names and their references.

The field naming advantages are also effective when using tables in Excel 2007 and later. Tables (and their named reference) can adjust for insertions, deletions, and still maintain boundaries.

Avoid hard-coded values in formulas. Never assume a value will never change. If a value represents a set amount or percentage, create a separate cell for that value, and improve on that by naming the variable. A lookup section can be useful for collections of values that are easy to change, and lookup when needed. State’s sales tax rates would be one example.

Protection

After completing development of a spreadsheet, protect it and any formulas. Even if you use a universal company password, protection will help prevent accidental overwriting of formulas used in the spreadsheet. More advanced restrictions could also define what information is relevant in a spreadsheet.

Questions to Ask

There are many ways to evaluate spreadsheet development. This is a simplified series of questions you might ask about new spreadsheets.

Do you need a spreadsheet?

Create spreadsheets to get new information instead of just transferring your existing information to Excel.

Are you using the right information?

Know what information you have to work with, and if it contains what you need to get useful information.

Are you using the right formulas?

A formula that uses Sum/Count may not get an average if values are missing from the range being used in the calculation. Using average may (or may not) be the function you want. Statistical formulas in particular require values that meet a certain range of values.

Are you using the formulas right?

While Excel will accept the entry of a formula, it can’t tell if the arguments in the formula have been swapped. Incorrect cell references will also make even the best thought out formula generate meaningless information.

Are you getting the right information?

If the information you are getting doesn’t make sense, you may need to verify formulas or re-examine the logic behind the calculations.

Development and Maintenance

Development of spreadsheets is typically task-oriented. When a new task presents itself a new spreadsheet is developed and put to use for that person or project. For that reason, the skill set required to create spreadsheets is not much different from simply using spreadsheets and the line between use and development of spreadsheets is blurred. This is particularly true if the spreadsheet simply totals multiple columns of data.

Often complex tasks require more complex worksheets. While someone with basic spreadsheet skills may devote considerable time with such projects, there is a significant difference between the skill set required to develop complex spreadsheets and a user with basic knowledge. Not only will such projects be more time-consuming (cost-benefit ratio), they are prone to errors. The user/developer mentality is so common that the concept of developing and maintaining spreadsheets may seem foreign to many managers. The error level of average spreadsheets, however, accents the need to rethink the development process of Excel and spreadsheets and consider demarcation.

Improving quality control of Excel spreadsheets is mentioned here only briefly. For more information review the articles included in the footnotes. “Are Your Excel Spreadsheets Accurate?” mentions some basic ways to guard against errors and detect errors in a spreadsheet.[10] “Is your Love for Excel Misplaced?” looks at a few things that may help to identify reasons for inaccuracies including the need to define the purpose of a spreadsheet before creating it.[11]

Standards have also been developed for spreadsheet development and are available on the Spreadsheet Standards Review Board website.[12] Related to this organization is a structured site devoted to Excel maintenance called “Best Practice Modeling.”[13] Site pages discuss Excel development, including a description of auditing tools available, Modular Development, and a Knowledge Base of industry specific applications. While a system can help to organize the development process, it is the individual developer, whether secretary or IT professional that creates spreadsheets and a guide on “How to Make Spreadsheets Error-Proof”[14] could be a good initial step. “How do you know your spreadsheet is right?” presents some best practices to use in developing spreadsheets. [15]

Much can be done to improve quality of Excel-generated information but there are cases where a database might be more appropriate.  

Access Databases

Databases are similar to spreadsheets in that data is often represented in rows and columns. The biggest difference is that database information is relational. Each table in a database is typically related to one or more other tables in the database. Relationships are usually one-to-many. Thus, for each client, there may be one or more invoices. For each invoice there may be one or more items. For each employee there may be one or more clients he is responsible for. Unlike a spreadsheet, the data entered is separate from the way it is gathered or presented, so the cells do not have display formatting associated with them. Forms and reports, however, can display the data in many different ways.

Databases are the primary sources for Enterprise Resource Planning (ERP) packages, in part because they are highly efficient at data management. On a local level, databases can also be created on an individual PC and shared on the local network using Microsoft Access. Microsoft Access provides users with all the tools for developing functional databases. Whether or not you have a network database or other ERP, learning how to develop using Access may fill the need for many tasks.

History

In the earlier times, data processing was separate from business functions of a company. Computer programmers developed programs that users accessed to generate reports. Most of those programs were database-based. Business users didn’t concern themselves with formatting and output. Their job was to enter data, and request suitable reports from that data. When the first business PC showed up, the first major spreadsheet (Visicalc) helped users to get many of their financials done without an IT department. Lotus 1-2-3 soon followed and then Excel.

As focus moved to spreadsheets, companies found a way to eliminate, downsize, or refocus their IT department. As a result IT-developed databases were replaced by desktop produced spreadsheets. Today, many of the larger companies have ERP systems which are replacing some of the spreadsheet applications. The software industry has also developed specialized database software to handle specific tasks. In accounting today, database software handles tax services, audit functions, and many bookkeeping and financial accounting functions. Progressive companies may do well to bring database-driven information back into other areas.

Database Advantages

Excel and spreadsheets have a useful purpose in an organization, but there are times when a database is more appropriate use of resources. When appropriate, the advantages of using an Access database are multi-faceted. The following categories illustrate the benefits of an Access database.

Data Management

  • Data efficiency. A single database can handle the tasks of many duplicate spreadsheets.
  • Data growth. Unlike a spreadsheet that becomes unwieldy as it grows, databases operations are generally not affected by growth in the size of the database.
  • Integration. A database can handle many different tables of different types of information and integrate all of them. General information about a client can be stored with financial transactions of the client. Everything is (should be) linked to everything else.
  • Pictures. Pictures, web links, and other objects can be stored in Access as part of a record. Links to separate supporting documents can also be added to a record.
  • Joint databases. A database can reference tables in a separate database. A user may or may not be able to add records in a supporting database. This is useful if some of the data is more confidential or security is important.
  • Multi-level access. It’s possible to limit access to some data in a database on a user-by-user basis. Limits could be inability to change, or inability to view confidential data by some users.
  • Consistent calculations. Calculations in Excel are cell by cell. If a cell is missing a formula in a column the totals will be incorrect. In a database, the total can be generated by multiplying two fields for each record, without having to assign a formula to each cell in the table.

Usability (ease of use)

  • Easy to use. Access databases are easy for users to enter relevant data, since the development is done prior to deployment.
  • Navigation. Access databases are typically designed to display forms for data entry with set navigation controls. This is better than trying to enter data in a long string of cells in a grid.
  • Data entry forms. Although it is possible to create a data entry form in Excel, forms are the primary tools in Access for accepting data from users. If source documents vary, separate forms can be used to enter data from source documents without having to change the database table structures.
  • Formula protection. Database calculations are separate from data entry, so there is no concern about writing over a cell formula with a constant value.

Structure

  • Access can represent multiple dimensions, while Excel is typically limited to two. In Excel, for each account you would have rows and columns. For additional accounts you would add another spreadsheet or section.
  • All cells in a column are the same. In Excel, cells in a column of table data (fields) are formatted individually. In an Access database, the formatting of a field is the same for all records.
  • Data types must be defined in Access, whereas Excel may (incorrectly) redefine data types, format, and precision based on data entered.
  • Validation. In addition to data type definitions, data entry can be validated in Access for consistency.
  • Primary keys. Every record in an Access database has a primary key that distinguishes that record from any other.
  • Normalization. The development process of normalization in an Access database insures that tables are properly related to others and that data is stored efficiently.
  • Referential integrity. Access can internally monitor the integrity of data in tables. If a value in a table references another table, the reference must exist.
  • Cascading additions and deletions. Referential integrity also controls stray, orphan data. When a table adds or deletes a value with references to a related table, the related tables are added or deleted. For example, you can avoid having invoices for customers that no longer exist if you choose.

Reporting

  • Data and presentation are distinct layers. In IT, the separation of data and how it is presented are considered separate layers.
  • Flexible reporting. Not only do separate data and presentation layers protect the data from accidental corruption (cell overwriting), reporting from an Access database is more flexible. When properly developed, reports can integrate data from many areas to provide more meaningful information. Using queries, you can get reports on select data or generate full reports of larger sets of data with multiple levels of subtotals, for example. Slice and dice.
  • Same data, separate reporting. Because reports are separate from the data in a database, you can create a new report without having to make a copy of the data. In Excel, a different reporting format often means making a copy of the first spreadsheet and modifying the copy. At that point, you have two separate copies of the data that would need to be modified if the data changes.
  • Reporting formats. You can export information from a database in a number of other formats, including Excel. Access does have the resources to prepare complex queries, but it doesn’t have all the statistical features of Excel, so you can store the data in Access and export to Excel to analyze if necessary.

Development and Maintenance

  • Longer development cycle. Often considered a disadvantage of databases, the longer development cycle reflects the nature of development. Spreadsheets are often not validated to insure they are accurate. Spreadsheet testing may be limited to formula syntax. Databases are generally tested to insure that they are functioning properly.
  • The calculations and the data entry are separate. Users cannot alter calculations created in a report using data entry.
  • Although a different type of expertise is required, Access databases can be more easily managed once developed. Similarly, automation through macros and VBA modules is available in Excel, but is more of a part of Access, with each form or report having its own code.
  • Access databases can be centralized, with users sharing a single data source accessed over the internal network.

How does a database work?

  • Each table represents an entity and has multiple records.
  • Each record must have a unique value (keys) to identify itself.
  • Tables are linked with other tables through those keys.
  • Forms and reports query the tables to get the information to be presented.
  • Multiple reports can be designed using the same data, without having to alter that data.

Information Presentation

One characteristic of a database is in its layered process. In a spreadsheet, data and presentation information are integrated into each cell. In a database project there are at least two layers; the data layer and the presentation layer. By segregating the tasks of entering information and creating reports users can focus on just one area at a time. Furthermore, once the data is in, the information can be presented in any number of ways, with selected levels of detail.

Database Limitations

Extensive use of Access databases is not for everyone.  Databases may be a new process for many cases, and staff will have to adjust to the operations and limits. Databases separate the two processes, development and use, so access to the database will depend on whether the person has development skills. Other limitations include the following.

  • Steeper learning curve. While use of a well-interfaced database can be a breeze, most any type of development or maintenance comes with a steep learning curve. Extensive training is required to teach someone to develop databases.
  • Different expertise. Expertise Database development requires a different level of expertise and developers are not readily available. Developers must understand database concepts, not just the steps to navigate the environment and create formulas.
  • Long development cycle. It is more time consuming to create a database than to create a spreadsheet. The upside is that changes are easier to make and the database may have many purposes.
  • Commitment required. Not only does a company need to develop the database application, they should be able to maintain it, add features when needed, adjust for changes in policy or law, and make corrections when needed. Some databases can exist for years after development without maintenance, but that may not be the norm.
  • Not error proof. Poorly designed databases, like poorly designed spreadsheets can appear to generate useful data. Test and retest.
  • Reporting. Access reports on all data the same way. Without some specific coding, it’s not possible to highlight or add special formatting for specific value(s).

Database Risks and Control

There are risks associated with database development, and executives will not have as much control. While a senior partner can review spreadsheet formulas, he will likely not have the knowledge to check a database table, formula, or report for errors, other than possibly verifying the information with spreadsheet calculations.

Errors in a database are not easy to detect by users other than double-checking the data entry. A comprehensive development process is needed to insure errors are detected and prevented prior to deployment of the database system. When centralized, loss of data can be catastrophic. Because a database contains large amounts of data, the potential loss is large. Incremental backups are crucial.

Database Development

Following the introduction of the PC in the mid-1980’s, with the limitation that not much software was available, one of the main reasons a company would hire a computer guy was to set up a database. Today, given the universal use of SQL and normalization, and the availability of affordable database software, the value and versatility of a database is greater than ever.  Most any size firm can begin the process if they have a resident database guru, either hired for that or trained in-house. While a company may not need a full-time IT department to begin using more Access databases, there must be someone available who has a good understanding of how to develop a database. A very simple database can be developed with minimal training but the real benefits of a database are based on a well-developed database program.

Database Development training is not unlike training requirements of an Excel. Although formal training is not required extensive training is a requirement. It will require more than a single introduction to Access. A possible misconception is that a person can take a single class in database development and be able to handle all of a company’s database needs. Just as Excel experts gain their expertise through progressive experience and often several classes in the subject, Access training requires more than a rudimentary understanding of Access basics. Given the time and opportunity, however, a staff member can grow into being a database guru.

You don’t have to invest in a dedicated IT department, but you do have to make an investment. Many managers may decide it’s not worth it, or that other projects are more in line with their business. You can begin a database team. Start with a few projects, evaluate, improve and refine them. Then integrate one or more of them. As you grow you can consider more complex applications, network databases, or web based (cloud) databases.

Identifying potential database projects

It’s not always apparent what project to consider for database development. One clue may be the level of frustration you have with a complex spreadsheet. The prevalence of task-oriented software is one indication of the power and flexibility of Access databases. A lot of such software is database driven and provides safeguards to many common risks. A good guide is to look at what things are done with separate software.

Tax Software

I wouldn’t recommend creating your own tax software in an Access database because it is so complex, but a look at what tax software includes may help you picture what the inside of an Access might look like. Tax database doesn’t just contain all the data for a single client. At end of tax-season, your tax software may contain 1000’s of completed forms with hundreds of fields for 100’s of clients in a single database. The fields of one form may be dependent upon the values other forms. Underlying the forms is the collection of related tables. Tax software selects only the information required from those tables for a particular client for a particular year and groups it into a single tax return.

Similarly, any requirement that has multiple layers of the same type of data may be better represented in a database. Spreadsheets that consists of multiple levels of spreadsheet data is a prime candidate for an Access database.

Sectional Spreadsheets

Excel files may contain several (many) sections in the same spreadsheet, with each section using the same formulas. In one case I looked at, a survey project, modifying the spreadsheet was time-consuming. Formatting was fixed and analytical tools did not fit the fragmented format of the spreadsheet. It was essential many spreadsheets on one spreadsheet.

Workbooks

Another Excel strategy was to actually create multiple spreadsheets in the same workbook, with one spreadsheet acting as a summary of the others. I’ve seen over 50 spreadsheets tabs in a workbook and a full sheet of cross-spreadsheet formulas that summarized the other worksheets. As such the data is fragmented and producing the spreadsheets for distribution was very time-consuming. Just the pure volume of information and number of formula cells made it challenging, if not impossible, to insure the calculations were accurate. Not all workbooks are like this, but when repeating spreadsheets are common then a database may be a better choice.

These types of worksheets are actually useable, although they require a very organized developer to use or manage it without introducing the potential for error and associated risks.

Management Tools

Access is not only an alternative to spreadsheets and other digital resources. In many cases, a database can be a new thing that improves management of a crucial element of important business processes. That has been the process that led me to develop databases for inventory control, order processing, and resource management, rather than use Excel as an intermediate step. As companies become more comfortable with databases and improve their internal development potential they will find new uses for this technology to save time and provide improved reporting for clients and within the company.

Conclusion

The first lesson to be learned from this is that companies need to establish a system of spreadsheet development, particularly when spreadsheets are distributed throughout the company. Occasional audits of spreadsheets will help to identify problems and risks associated with spreadsheets. Depending on the structure of the organization, consideration may need to be given to protecting some spreadsheets. In conjunction with implementing a quality control system for spreadsheet development, continuous training will help prevent users from getting stale on features of Excel that they do not use often.

Secondly, sometimes a spreadsheet is not the right tool. If all you have is a hammer, everything looks like a nail. Sometimes you really need a screwdriver. Sometimes you need to have a database. If your firm or department is large enough or has the staffing, consider investing the time to train a couple of people in database development.

This is a simplified coverage of Access databases. Without more exposure to databases you may not fully see the nature of development and daily use of databases. As time permits, more information may be posted to illustrate the effectiveness of internally developed Access database, the process of developing a database, and a program to train staff to develop and use databases.



[6] Panko, R. R., & Halverson, R. P., Jr. (1996, January). Spreadsheets on Trial: A Framework for Research on Spreadsheet Risks. Proceedings of the Twenty-Ninth Hawaii International Conference on System Sciences, Maui, Hawaii.

]]>
https://tylerhosting.com/b2e/dbell.php/excelling-with-access#comments https://tylerhosting.com/b2e/dbell.php?tempskin=_rss2&disp=comments&p=343
External Table Data in Access https://tylerhosting.com/b2e/dbell.php/external-table-data-in-access Fri, 03 Oct 2014 21:01:00 +0000 Dana Bell Information Technology Computing Professional Microsoft Office Access 334@https://tylerhosting.com/b2e/
Deprecated: Creation of dynamic property Item::$cache_has_content_parts is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 4911

Deprecated: Creation of dynamic property Item::$renderers_validated is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 11107

Deprecated: Creation of dynamic property Item::$pages is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 2412

Deprecated: Creation of dynamic property ItemSettings::$count_col_key_names is deprecated in /home4/tylering/public_html/b2e/inc/settings/model/_abstractsettings.class.php on line 121
<p>I'm constantly coming up with new ideas for projects, articles, and ways to do things. One way I tried to keep up with that was to create text files with those notes. Many years ago I used Microsoft's cardfile.exe but it no longer exists in current versions of Windows. Then, a few years ago I tried out the AZZ Cardfile program. Shortly after that I created my own card file database similar to AZZ Cardfile, named simply <strong><em>Simple Cards</em></strong>. The cards are listed by title on the left and the content of the selected card shows in the middle content area. The cards are categorized, sortable, and searchable. In order to make the application work I had to develop a number of routines to make sure the list and cards were synchronized, including when searching and filtering, when adding or deleting cards, and when resorting. Work still needs to be done to smooth the transitions between screens, being able to select and delete multiple cards, and exporting a category of cards to an external file. A programmer's work never ends.</p> <p>As in AZZ, you can also create external data files of cards in Simple Cards. The external files are actually separate databases. Ignoring the general development of Simple Cards for now, this article shows how to enable external table data in an Access program, first with an explanation and then some code samples.<br /><br />When Access displays a form, it relies on a source file for the data, either directly from a table, or from a query based on one or more tables. Typically, the only time Access uses an external table is if the database is split into front end/back end, but it is possible to access external data as a record source for a form or report, without technically splitting the database and creating links to external tables. The best way to do that is to use a query instead of a table as record source for a form.<br /><br />If the form is based on a query the tables are normally from within the database. However, if you open the query for editing and access the properties of the query, you can see that there is an option to specify the database to use under Source Database. If the query was created with internal tables, it will have "(current)" in gray. If you change that to refer to a different database, it will load data from that external file instead. After specifying an external database, you can view the SQL code for the query and notice that an additional qualifier, 'IN', is used in the query with the file path of the external file. <br /><br />In my project, however, I wanted the user to be able to optionally access one of several different databases for the form. So the user doesn't have to go to the query and enter a long file path to do this, Application.FileDialog is used to select the database. The problem is, outside of Microsoft, there is no direct way to change the Source Database of the query. I've searched, and asked, and searched, and I cannot find a way to access those properties. In order to change that, you have to change the SQL property of the query. To load an external file, you have to build the SQL statement; and to read the filespec of a loaded file, you have to search the SQL statement. <br /><br />In developing Simple Cards I also had to incorporate some user messages so the user would know what file is being used. In addition, I change the caption to show the file being used if it's an external file, both when loading external files, and when restarting with an external file. Finally, in order to make this work with less confusion for the user, instead of saving external databases with the normal extension, I used the "crd" extension. Access doesn't care, and the files won't be associated with Access.<br /><br />Following are the steps I took to enable this feature.</p> <p>First, I create the form to open the data using a query (qryCardFile) so I can change the query in order to change data source. On the main form is a button that enables the user to open an external file, with the following code to change the SQL statement.</p> <div style="font-family: courier new,courier; color: #000000; background-color: #ffffff; padding: 10px;">Set db = CurrentDb<br />Set qdf = db.QueryDefs("qryCardFile")<br />    <br />FileSelected = modFileDialog()<br />qdf.SQL = "SELECT tblCardfile.CardID, tblCardfile.Title, tblCardfile.Content, tblCardfile.CardDate, tblCardfile.CardCategory, tblCardfile.ChangeDate " &amp; _<br />    "FROM tblCardfile <strong>IN '" &amp; FileSelected &amp; "'</strong> " &amp; _<br />    "ORDER BY tblCardfile.CardID;"<br />        <br />DoCmd.OpenForm "frmReopen", , , , , , FileSelected</div> <p>Notice that I change the query by adding IN and the name of the file selected. You will also notice that I use an extra parameter (FileSelected) in the OpenForm command. The extra parameter is OpenArgs which enables you to pass the filespec to the form open subroutine. And, of course, you notice that I use this to open a different form. Using form frmReopen is a tidy way to close and reopen the main form without confusion for the user.</p> <p>Following is the relevant code in frmReopen</p> <div style="font-family: courier new,courier; color: #000000; background-color: #ffffff; padding: 10px;">DoCmd.Close acForm, "frmCardFile"<br />Me.Visible = False<br />'Use timer to give form time to load<br />... <br />DoCmd.OpenForm "frmCardFile", , , , , , Me.OpenArgs<br />DoCmd.Close acForm, "frmReopen"</div> <p>This simply closes the main form and then reopens it with the new external data table. The change in the query's SQL in the prior code sample is what enables the loading of the external data. The Me.OpenArgs parameter is only used to update the form caption.</p> <p>Incidentally, this technique could be used to view the content of any external table since the query can use the global qualifier, although additional work is required to get a list of tables in that external database.</p> <p>The following code is what actually changes the caption</p> <div style="font-family: courier new,courier; color: #000000; background-color: #ffffff; padding: 10px;">If IsNull(Me.OpenArgs) Then<br />    source = GetSourceDB<br />    If (source = "tblCardFile") Then<br />        Me.Form.Caption = "Simple Cards"<br />    Else<br />        Me.Form.Caption = "Simple Cards from " &amp; source<br />        MsgBox ("Using external data from " &amp; source)<br />    End If<br />Else<br />    Me.Form.Caption = "Simple Cards from " &amp; Me.OpenArgs<br />    MsgBox ("Using external data from " &amp; Me.OpenArgs)<br />End If</div> <p>The following code (GetSourceDB) is what gets caption information from the SQL statement of the query</p> <div style="font-family: courier new,courier; color: #000000; background-color: #ffffff; padding: 10px;">sqlstatement = CurrentDb.QueryDefs("qryCardFile").Properties("SQL")<br />ending = InStr(sqlstatement, "ORDER")<br />starting = InStr(sqlstatement, "IN '")<br />If (starting = 0) Then<br /> source = "tblCardFile"<br />Else<br /> source = Mid(sqlstatement, starting + 4, (ending - 3) - (starting + 4))<br />End If<br />GetSourceDB = source</div> <p>If this code cannot find 'IN' in the query, then the query must be using the internal table.</p> <p>Of course, the user will not want to manually create external databases, so an option to create one is made available from a button on the form. This simple code snippet shows how to create a database and copy the structure of the CardFile table to that database. Then it uses a modified version of the ChangeCards subroutine to load that blank external file.</p> <div style="font-family: courier new,courier; color: #000000; background-color: #ffffff; padding: 10px;">CardFileName = modSaveAsDialog<br />   <br />'Make sure there isn't already a file with the name of the new database<br />...<br /><br />Set db = ws.CreateDatabase(CardFileName, dbLangGeneral)<br />DoCmd.TransferDatabase acExport, "Microsoft Access", CardFileName, acTable, "tblCardfile", "tblCardfile", True<br />db.Close<br />Set db = Nothing<br /><br />ChangeCards (CardFileName)</div> <p>While the whole concept of using external files may be hard to grasp at first, with a logical study of the flow of data and order of instructions in the code it's possible to create routines to do things like this and make it look simple.</p> <p>Note that you may have to set the relevant Office references for your version of Access in order to use the FileDialog routines. Some word-wrapping may have occurred in code sections presented but marking and copying usually preserves underlying formatting.</p> <p>P. S.</p> <p>One issue with using external database files in an application is that Microsoft Access processes queries before processing forms. As a result, if the query fails to load, the form fails to load. This could be due to an invalid card file, or a missing card file. In order to avoid this unpleasant situation, you have to error check (not shown in the code clippings above) for the file when loading the form. You can’t error check in the main form itself. In this case, the check is done in the Splash screen, or Reopen form, when it opens the main form.</p><div class="item_footer"><p><small><a href="https://tylerhosting.com/b2e/dbell.php/external-table-data-in-access">Original post</a> blogged on <a href="http://www.tylerhosting.com/b2e/">Switched Keys</a>.</small></p></div>
I'm constantly coming up with new ideas for projects, articles, and ways to do things. One way I tried to keep up with that was to create text files with those notes. Many years ago I used Microsoft's cardfile.exe but it no longer exists in current versions of Windows. Then, a few years ago I tried out the AZZ Cardfile program. Shortly after that I created my own card file database similar to AZZ Cardfile, named simply Simple Cards. The cards are listed by title on the left and the content of the selected card shows in the middle content area. The cards are categorized, sortable, and searchable. In order to make the application work I had to develop a number of routines to make sure the list and cards were synchronized, including when searching and filtering, when adding or deleting cards, and when resorting. Work still needs to be done to smooth the transitions between screens, being able to select and delete multiple cards, and exporting a category of cards to an external file. A programmer's work never ends.

As in AZZ, you can also create external data files of cards in Simple Cards. The external files are actually separate databases. Ignoring the general development of Simple Cards for now, this article shows how to enable external table data in an Access program, first with an explanation and then some code samples.

When Access displays a form, it relies on a source file for the data, either directly from a table, or from a query based on one or more tables. Typically, the only time Access uses an external table is if the database is split into front end/back end, but it is possible to access external data as a record source for a form or report, without technically splitting the database and creating links to external tables. The best way to do that is to use a query instead of a table as record source for a form.

If the form is based on a query the tables are normally from within the database. However, if you open the query for editing and access the properties of the query, you can see that there is an option to specify the database to use under Source Database. If the query was created with internal tables, it will have "(current)" in gray. If you change that to refer to a different database, it will load data from that external file instead. After specifying an external database, you can view the SQL code for the query and notice that an additional qualifier, 'IN', is used in the query with the file path of the external file.

In my project, however, I wanted the user to be able to optionally access one of several different databases for the form. So the user doesn't have to go to the query and enter a long file path to do this, Application.FileDialog is used to select the database. The problem is, outside of Microsoft, there is no direct way to change the Source Database of the query. I've searched, and asked, and searched, and I cannot find a way to access those properties. In order to change that, you have to change the SQL property of the query. To load an external file, you have to build the SQL statement; and to read the filespec of a loaded file, you have to search the SQL statement.

In developing Simple Cards I also had to incorporate some user messages so the user would know what file is being used. In addition, I change the caption to show the file being used if it's an external file, both when loading external files, and when restarting with an external file. Finally, in order to make this work with less confusion for the user, instead of saving external databases with the normal extension, I used the "crd" extension. Access doesn't care, and the files won't be associated with Access.

Following are the steps I took to enable this feature.

First, I create the form to open the data using a query (qryCardFile) so I can change the query in order to change data source. On the main form is a button that enables the user to open an external file, with the following code to change the SQL statement.

Set db = CurrentDb
Set qdf = db.QueryDefs("qryCardFile")
    
FileSelected = modFileDialog()
qdf.SQL = "SELECT tblCardfile.CardID, tblCardfile.Title, tblCardfile.Content, tblCardfile.CardDate, tblCardfile.CardCategory, tblCardfile.ChangeDate " & _
    "FROM tblCardfile IN '" & FileSelected & "' " & _
    "ORDER BY tblCardfile.CardID;"
        
DoCmd.OpenForm "frmReopen", , , , , , FileSelected

Notice that I change the query by adding IN and the name of the file selected. You will also notice that I use an extra parameter (FileSelected) in the OpenForm command. The extra parameter is OpenArgs which enables you to pass the filespec to the form open subroutine. And, of course, you notice that I use this to open a different form. Using form frmReopen is a tidy way to close and reopen the main form without confusion for the user.

Following is the relevant code in frmReopen

DoCmd.Close acForm, "frmCardFile"
Me.Visible = False
'Use timer to give form time to load
...
DoCmd.OpenForm "frmCardFile", , , , , , Me.OpenArgs
DoCmd.Close acForm, "frmReopen"

This simply closes the main form and then reopens it with the new external data table. The change in the query's SQL in the prior code sample is what enables the loading of the external data. The Me.OpenArgs parameter is only used to update the form caption.

Incidentally, this technique could be used to view the content of any external table since the query can use the global qualifier, although additional work is required to get a list of tables in that external database.

The following code is what actually changes the caption

If IsNull(Me.OpenArgs) Then
    source = GetSourceDB
    If (source = "tblCardFile") Then
        Me.Form.Caption = "Simple Cards"
    Else
        Me.Form.Caption = "Simple Cards from " & source
        MsgBox ("Using external data from " & source)
    End If
Else
    Me.Form.Caption = "Simple Cards from " & Me.OpenArgs
    MsgBox ("Using external data from " & Me.OpenArgs)
End If

The following code (GetSourceDB) is what gets caption information from the SQL statement of the query

sqlstatement = CurrentDb.QueryDefs("qryCardFile").Properties("SQL")
ending = InStr(sqlstatement, "ORDER")
starting = InStr(sqlstatement, "IN '")
If (starting = 0) Then
source = "tblCardFile"
Else
source = Mid(sqlstatement, starting + 4, (ending - 3) - (starting + 4))
End If
GetSourceDB = source

If this code cannot find 'IN' in the query, then the query must be using the internal table.

Of course, the user will not want to manually create external databases, so an option to create one is made available from a button on the form. This simple code snippet shows how to create a database and copy the structure of the CardFile table to that database. Then it uses a modified version of the ChangeCards subroutine to load that blank external file.

CardFileName = modSaveAsDialog
   
'Make sure there isn't already a file with the name of the new database
...

Set db = ws.CreateDatabase(CardFileName, dbLangGeneral)
DoCmd.TransferDatabase acExport, "Microsoft Access", CardFileName, acTable, "tblCardfile", "tblCardfile", True
db.Close
Set db = Nothing

ChangeCards (CardFileName)

While the whole concept of using external files may be hard to grasp at first, with a logical study of the flow of data and order of instructions in the code it's possible to create routines to do things like this and make it look simple.

Note that you may have to set the relevant Office references for your version of Access in order to use the FileDialog routines. Some word-wrapping may have occurred in code sections presented but marking and copying usually preserves underlying formatting.

P. S.

One issue with using external database files in an application is that Microsoft Access processes queries before processing forms. As a result, if the query fails to load, the form fails to load. This could be due to an invalid card file, or a missing card file. In order to avoid this unpleasant situation, you have to error check (not shown in the code clippings above) for the file when loading the form. You can’t error check in the main form itself. In this case, the check is done in the Splash screen, or Reopen form, when it opens the main form.

]]>
https://tylerhosting.com/b2e/dbell.php/external-table-data-in-access#comments https://tylerhosting.com/b2e/dbell.php?tempskin=_rss2&disp=comments&p=334
A Microsoft Access Front-End Updater https://tylerhosting.com/b2e/dbell.php/a-microsoft-access-front-end Wed, 23 Jan 2013 03:53:00 +0000 Dana Bell Information Technology Microsoft Office Access 295@https://tylerhosting.com/b2e/
Deprecated: Creation of dynamic property Item::$cache_has_content_parts is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 4911

Deprecated: Creation of dynamic property Item::$renderers_validated is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 11107

Deprecated: Creation of dynamic property Item::$pages is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 2412

Deprecated: Creation of dynamic property ItemSettings::$count_col_key_names is deprecated in /home4/tylering/public_html/b2e/inc/settings/model/_abstractsettings.class.php on line 121
<p>A question came up on one of the discussion forums is how to automatically update the front end (FE) of a split database. Instead of taking up space on the board, I'm posting it here. This is the method I used on the only situation where I provided that feature.</p> <h2>Overview</h2> <p>I use the opening screens of the front end (FE) and a separate database that does the updating. The intermediate database is used because it is small while the updated version may be large, slow, etc., and enables access to make a backup of the existing FE. This is a summary, with the relevant code following.</p> <ol> <li>After opening the splash screen of the FE, I open a version check form that compares the versions. The recordsource of the form gets version Ids from FE and back end (BE), using equality because versions may have alphabetical suffixes. This is used in the form_open code. <p>SELECT tblBEVersion.VersionID, tblBEVersion.BEVersionNumber, tblFEVersion.VersionID, tblFEVersion.FEVersionNumber <br /> FROM tblBEVersion INNER JOIN tblFEVersion ON tblBEVersion.[VersionID] = tblFEVersion.[VersionID];</p> </li> <li>If there is a difference, the versioncheck form asks if the user wants to update. Some users may have customized their version of the FE, and not all users may have the same FE. If the update is critical, you could add another field to the BE table and use that instead of the prompt if needed.</li> <li>If approved, the FE copies an updater database to the user system, shells to open it and then closes itself.</li> <li>When the Updater opens, it presents another warning and asks if user is sure they want to update. If not, shell to the existing FE.</li> <li>If approved by the Updater, the Updater backs up the current FE and copies the new version to the user system, and shells to open it. This is a major case when it may  be necessary to reconnect to BE tables.</li> </ol> <p>When developing the updater, you need to set the opening form for the updater. You will want to remember that if you set it up so that the user can not edit the database, you can't get back in to edit it either. You can, however, create a new database and import the objects.</p> <h2>Source Code</h2> <p>This is the relevant code for the Auto Updating system  I use. This was based on some code provided by one or more other developers so it may be familiar. I only used it in one case and as I post this I notice that there is much room for improvement...when I next need to use it. It's also old (possibly 2003) so some functions (FileCopy, Instr) may already exist in current version of Access.</p> <h3>Front End Database</h3> <h4>frmSplash (automatically opens)</h4> <blockquote> <p>Private Sub CloseSplash_Click()<br />On Error GoTo Err_CloseSplash_Click<br /><br />    Dim stDocName As String<br />    stDocName = "frmVersionCheck"<br />    <br />    DoCmd.OpenForm stDocName<br />    DoCmd.Close acForm, "frmSplash"<br /><br />End Sub</p> </blockquote> <h4>frmVersionCheck</h4> <blockquote> <p>RecordSource<br /><br />SELECT tblBEVersion.VersionID, tblBEVersion.BEVersionNumber, tblFEVersion.VersionID, tblFEVersion.FEVersionNumber<br />FROM tblBEVersion INNER JOIN tblFEVersion ON tblBEVersion.[VersionID] = tblFEVersion.[VersionID];<br /><br />Private Sub Form_Open(Cancel As Integer)<br /><br />Dim strUpdaterPath As String<br />Dim strCurrentPath As String<br />Dim strDataFile As String<br />Me.Visible = False<br /><br />If FEVersionNumber &lt; BEVersionNumber Then<br />    <br />    strMsg = "You do not have the correct version." &amp; vbCrLf &amp; vbCrLf &amp; _<br />             "Would you like to download the latest client?"<br />    If MsgBox(strMsg, vbExclamation + vbOKCancel, "Update Client") = vbOK Then<br />        ' If Updater doesn't exist on Client, then copy it there<br />        strCurrentPath = Left(CurrentDb.Name, LastInStr(CurrentDb.Name, "\")) &amp; "ExpAuth Updater.mdb"<br />        <br />        ' Always copy down newest updater and assume Updater is in the same path as the Data files<br />        strDataFile = DLookup("Database", "msysobjects", "Name='tblPeople'")<br />        strUpdaterPath = Left(strDataFile, LastInStr(strDataFile, "\")) &amp; "ExpAuth Updater.mdb"<br />        FileCopy strUpdaterPath, strCurrentPath<br />        <br />        ' Shell to execute/open the updater database<br />     strUpdateTool = "MSAccess.exe " &amp; """" &amp; strCurrentPath &amp; """"<br />        Shell strUpdateTool, vbNormalFocus<br />        DoCmd.Quit<br />    End If<br />    <br />End If<br /><br />DoCmd.OpenForm "Switchboard"<br />DoCmd.Close acForm, "frmVersionCheck"<br /><br />End Sub</p> </blockquote> <h3>Updater Database</h3> <h4>frmUpdate (automatically opens)</h4> <blockquote> <p>' In Properties, Timer = 3000<br /><br />Option Compare Database<br />Option Explicit<br /><br />' Set up globals<br />Dim strPath As String<br />Dim strDest As String<br />Dim strBkup As String<br />Dim strMyDB As String<br />Dim strVer As String<br />    <br />Private Sub Form_Open(Cancel As Integer)<br /><br />Dim Answer As Byte<br />On Error Resume Next<br /><br />Answer = MsgBox("Continue with Menu Update . . .", vbInformation + vbOKCancel, "Menu Update")<br />If Answer = vbCancel Then DoCmd.Quit<br />'Form shows Updating text<br /><br />        DoCmd.Hourglass True<br />        DoEvents<br />        <br />        ' Load variables with correct file name-path values.<br />        strMyDB = CurrentDb.Name<br />        strPath = Left(strMyDB, LastInStr(strMyDB, "\"))<br />        <br />        ' File specs based on current menu database<br />        strDest = strPath &amp; "MyExpAuth Menu.mdb"<br />        strBkup = strPath &amp; "Backups\" &amp; "MyExpAuthMenuUpd_bkup.mdb"<br />        <br />        ' Update status form to identify version being copied.<br />        strVer = DLookup("[BEVersionNumber]", "tblBEVersion")<br />        Me.txtVer.Caption = "Installing version number ... " &amp; strVer<br />        <br />        ' Create a backup (replacing existing backup if necessary) and remove the target file.<br />        If Dir(strBkup) &lt;&gt; "" Then Kill strBkup<br />        FileCopy strDest, strBkup<br /><br />    ' Let Form_Timer take over<br />       <br />End Sub<br /><br />Private Sub Form_Timer()<br /><br />On Error Resume Next<br />    Dim strPathOnly As String<br />    Dim strSource As String<br />    Dim strHelpFile As String<br />    Dim strMsg As String<br />    Dim strOpenClient As String<br />    Const q As String = """"<br /><br />    DoCmd.Hourglass True<br />    DoEvents<br />    <br />    Err.Clear<br />    <br />    ' We make the assumption that the new client is in the<br />    ' same folder as this utility.<br />    <br />    ' Get path to Data from tblBEVersion<br />    strPathOnly = DLookup("Database", "msysobjects", "Name='tblBEVersion'")<br />    strSource = Left(strPathOnly, LastInStr(strPathOnly, "\")) &amp; "MyExpAuth Menu.mdb"<br />    If (strDest = strSource) Then<br />        MsgBox "Updater must be run on client machine.  Exiting.", vbCritical, "Error"<br />        GoTo Cleanup<br />    End If<br />    FileCopy strSource, strDest<br />    <br />    ' Update help file also<br />    strHelpFile = Left(strPathOnly, LastInStr(strPathOnly, "\")) &amp; "EAHelp.chm"<br />    FileCopy strHelpFile, strPath &amp; "EAHelp.chm"<br />    <br />    DoEvents <br />    <br />    ' Now that the new client file has been copied open new database and close this one.<br />    strOpenClient = "MSAccess.exe " &amp; q &amp; strDest &amp; q<br />    Shell strOpenClient, vbNormalFocus<br /><br />Cleanup:<br />    ' Cleanup the mouse pointer and exit from this application.<br />    DoCmd.Hourglass False<br />    DoCmd.Quit<br /><br />End Sub<br /><br /></p> </blockquote> <p> </p><div class="item_footer"><p><small><a href="https://tylerhosting.com/b2e/dbell.php/a-microsoft-access-front-end">Original post</a> blogged on <a href="http://www.tylerhosting.com/b2e/">Switched Keys</a>.</small></p></div>
A question came up on one of the discussion forums is how to automatically update the front end (FE) of a split database. Instead of taking up space on the board, I'm posting it here. This is the method I used on the only situation where I provided that feature.

Overview

I use the opening screens of the front end (FE) and a separate database that does the updating. The intermediate database is used because it is small while the updated version may be large, slow, etc., and enables access to make a backup of the existing FE. This is a summary, with the relevant code following.

  1. After opening the splash screen of the FE, I open a version check form that compares the versions. The recordsource of the form gets version Ids from FE and back end (BE), using equality because versions may have alphabetical suffixes. This is used in the form_open code.

    SELECT tblBEVersion.VersionID, tblBEVersion.BEVersionNumber, tblFEVersion.VersionID, tblFEVersion.FEVersionNumber
    FROM tblBEVersion INNER JOIN tblFEVersion ON tblBEVersion.[VersionID] = tblFEVersion.[VersionID];

  2. If there is a difference, the versioncheck form asks if the user wants to update. Some users may have customized their version of the FE, and not all users may have the same FE. If the update is critical, you could add another field to the BE table and use that instead of the prompt if needed.
  3. If approved, the FE copies an updater database to the user system, shells to open it and then closes itself.
  4. When the Updater opens, it presents another warning and asks if user is sure they want to update. If not, shell to the existing FE.
  5. If approved by the Updater, the Updater backs up the current FE and copies the new version to the user system, and shells to open it. This is a major case when it may  be necessary to reconnect to BE tables.

When developing the updater, you need to set the opening form for the updater. You will want to remember that if you set it up so that the user can not edit the database, you can't get back in to edit it either. You can, however, create a new database and import the objects.

Source Code

This is the relevant code for the Auto Updating system  I use. This was based on some code provided by one or more other developers so it may be familiar. I only used it in one case and as I post this I notice that there is much room for improvement...when I next need to use it. It's also old (possibly 2003) so some functions (FileCopy, Instr) may already exist in current version of Access.

Front End Database

frmSplash (automatically opens)

Private Sub CloseSplash_Click()
On Error GoTo Err_CloseSplash_Click

    Dim stDocName As String
    stDocName = "frmVersionCheck"
    
    DoCmd.OpenForm stDocName
    DoCmd.Close acForm, "frmSplash"

End Sub

frmVersionCheck

RecordSource

SELECT tblBEVersion.VersionID, tblBEVersion.BEVersionNumber, tblFEVersion.VersionID, tblFEVersion.FEVersionNumber
FROM tblBEVersion INNER JOIN tblFEVersion ON tblBEVersion.[VersionID] = tblFEVersion.[VersionID];

Private Sub Form_Open(Cancel As Integer)

Dim strUpdaterPath As String
Dim strCurrentPath As String
Dim strDataFile As String
Me.Visible = False

If FEVersionNumber < BEVersionNumber Then
    
    strMsg = "You do not have the correct version." & vbCrLf & vbCrLf & _
             "Would you like to download the latest client?"
    If MsgBox(strMsg, vbExclamation + vbOKCancel, "Update Client") = vbOK Then
        ' If Updater doesn't exist on Client, then copy it there
        strCurrentPath = Left(CurrentDb.Name, LastInStr(CurrentDb.Name, "\")) & "ExpAuth Updater.mdb"
        
        ' Always copy down newest updater and assume Updater is in the same path as the Data files
        strDataFile = DLookup("Database", "msysobjects", "Name='tblPeople'")
        strUpdaterPath = Left(strDataFile, LastInStr(strDataFile, "\")) & "ExpAuth Updater.mdb"
        FileCopy strUpdaterPath, strCurrentPath
        
        ' Shell to execute/open the updater database
     strUpdateTool = "MSAccess.exe " & """" & strCurrentPath & """"
        Shell strUpdateTool, vbNormalFocus
        DoCmd.Quit
    End If
    
End If

DoCmd.OpenForm "Switchboard"
DoCmd.Close acForm, "frmVersionCheck"

End Sub

Updater Database

frmUpdate (automatically opens)

' In Properties, Timer = 3000

Option Compare Database
Option Explicit

' Set up globals
Dim strPath As String
Dim strDest As String
Dim strBkup As String
Dim strMyDB As String
Dim strVer As String
    
Private Sub Form_Open(Cancel As Integer)

Dim Answer As Byte
On Error Resume Next

Answer = MsgBox("Continue with Menu Update . . .", vbInformation + vbOKCancel, "Menu Update")
If Answer = vbCancel Then DoCmd.Quit
'Form shows Updating text

        DoCmd.Hourglass True
        DoEvents
        
        ' Load variables with correct file name-path values.
        strMyDB = CurrentDb.Name
        strPath = Left(strMyDB, LastInStr(strMyDB, "\"))
        
        ' File specs based on current menu database
        strDest = strPath & "MyExpAuth Menu.mdb"
        strBkup = strPath & "Backups\" & "MyExpAuthMenuUpd_bkup.mdb"
        
        ' Update status form to identify version being copied.
        strVer = DLookup("[BEVersionNumber]", "tblBEVersion")
        Me.txtVer.Caption = "Installing version number ... " & strVer
        
        ' Create a backup (replacing existing backup if necessary) and remove the target file.
        If Dir(strBkup) <> "" Then Kill strBkup
        FileCopy strDest, strBkup

    ' Let Form_Timer take over
       
End Sub

Private Sub Form_Timer()

On Error Resume Next
    Dim strPathOnly As String
    Dim strSource As String
    Dim strHelpFile As String
    Dim strMsg As String
    Dim strOpenClient As String
    Const q As String = """"

    DoCmd.Hourglass True
    DoEvents
    
    Err.Clear
    
    ' We make the assumption that the new client is in the
    ' same folder as this utility.
    
    ' Get path to Data from tblBEVersion
    strPathOnly = DLookup("Database", "msysobjects", "Name='tblBEVersion'")
    strSource = Left(strPathOnly, LastInStr(strPathOnly, "\")) & "MyExpAuth Menu.mdb"
    If (strDest = strSource) Then
        MsgBox "Updater must be run on client machine.  Exiting.", vbCritical, "Error"
        GoTo Cleanup
    End If
    FileCopy strSource, strDest
    
    ' Update help file also
    strHelpFile = Left(strPathOnly, LastInStr(strPathOnly, "\")) & "EAHelp.chm"
    FileCopy strHelpFile, strPath & "EAHelp.chm"
    
    DoEvents
    
    ' Now that the new client file has been copied open new database and close this one.
    strOpenClient = "MSAccess.exe " & q & strDest & q
    Shell strOpenClient, vbNormalFocus

Cleanup:
    ' Cleanup the mouse pointer and exit from this application.
    DoCmd.Hourglass False
    DoCmd.Quit

End Sub

 

]]>
https://tylerhosting.com/b2e/dbell.php/a-microsoft-access-front-end#comments https://tylerhosting.com/b2e/dbell.php?tempskin=_rss2&disp=comments&p=295
Menu Manager as the New Switchboard Manager https://tylerhosting.com/b2e/dbell.php/a-new-switchboard-manager Sun, 02 Dec 2012 00:16:00 +0000 Dana Bell Information Technology Professional Microsoft Office Access 290@https://tylerhosting.com/b2e/
Deprecated: Creation of dynamic property Item::$cache_has_content_parts is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 4911

Deprecated: Creation of dynamic property Item::$renderers_validated is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 11107

Deprecated: Creation of dynamic property Item::$pages is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 2412

Deprecated: Creation of dynamic property ItemSettings::$count_col_key_names is deprecated in /home4/tylering/public_html/b2e/inc/settings/model/_abstractsettings.class.php on line 121
<p><em>This series of posts has been talking about using Switchboards in Microsoft Access. Prior entries looked at ways to customize the Switchboard and how it functions, how to edit the Switchboard Items table, and the limitations of the internal Switchboard Manager. This entry is about building a new Switchboard Manager to support features added to the Switchboard system.</em></p> <p>In this entry I introduce the Menu Manager. The Menu Manager is my version of the Switchboard Manager. Instead of the form Switchboard, I use frmMenu. Instead of the Switchboard Items table, I use tblMenuItems, and for the Switchboard Manager I use frmMenuEditor and related subforms. However, the Menu Editor is just a glorified Switchboard Manager so you could use it with your improved Switchboard and the default Switchboard files.</p> <p>While the menu only requires the menu form frmMenu and the menu item table, tblMenuItems, the Menu Editor requires three tables, a query, and three form objects. Before mapping the steps I took, here's a description of the objects being used in the Menu Editor. The Menu Editor (frmMenuEditor) uses a subform (subfrmEditMenu) and reads the menu item table (tblMenuItems) to show options selected for the menu. When you click to edit one of the items a popup form (subfrmEditMenuItem) is displayed so that you can select commands and available arguments for each command. The information for this form is gathered from tblMenuCommands, tblMenuCommandTypes, and a query, qryObjectTypes that finds relevant objects in the system's MSysObjects table.</p> <h3>Reading the Menu Items Table</h3> <p>Now, let's look at the fields in the Menu Items table. As previously mentioned, the table that runs the menu is tblMenuItems and the combination of SwitchboardID and ItemNumber fields make an entry unique. The ItemText is what is displayed. The Command is a number that refers to the command in the code behind the menu form. The Argument can be either the object being opened, or a number that refers to another menu. When the menu and the menu editor opens a new menu, it restricts the items read to those with the designated MenuID. Those items are then displayed in item number order.</p> <h3>Editing Basics</h3> <p>There are three levels of editing, and a form for each level. First, the Menu Editor main form (frmMenuEditor) only reads in each menu title. This is done with a query of tblMenuItems where Item Number is 0. That imports all of the menu titles. The current setup allows you to select the menu you want to edit from a combo box at the top of the form. On top of the drop down is a textbox that is used to enter the title for new menus. <br /><br />Next, the Menu Editor subform (subfrmEditMenu) reads in the items from the same table, except that only items &lt;&gt; 0 are included. Of course, there is a link between the parent and child forms so that only items for the active menuID are shown.</p> <p> </p> <div class="image_block"><a href="/b2e/media/blogs/dbell/Menu Editor.png?mtime=1354381294"><img style="display: block; margin-left: auto; margin-right: auto;" src="/b2e/media/blogs/dbell/Menu%20Editor.png?mtime=1354381294" alt="" width="450" /></a></div> <p>Finally, each menu item is edited in a popup form that must get the menu text, the command, and possibly an argument. Editing the menu items is where the most effort is concentrated.</p> <div class="image_block"><a href="/b2e/media/blogs/dbell/Edit Menu Item.png?mtime=1354381445"><img style="display: block; margin-left: auto; margin-right: auto;" src="/b2e/media/blogs/dbell/Edit%20Menu%20Item.png?mtime=1354381445" alt="" width="400" /></a></div> <h3>Editing the items</h3> <p>When adding or editing new commands there are three fields that need to be supplied in the item editor. The first is the text to be displayed in the menu. The second is the command. The third is the argument. The text that is supplied is as simple as typing it in.<br /><br />The command requires a little more prompting, so a combo box is provided. Although the command refers to a number, we need a list of commands that we can select from. tblMenuCommands is the table that is used to display the prompt for each of the commands. The menu title item has no command or arguments so no editing is required. The other menu items, however, must be edited to provide command and in most cases, argument. <br /><br />The argument requires a little more explanation. If a command requires an argument, which is usually the case, we have to show an appropriate list of arguments. If the command involves opening a form, we don't want to also list tables or reports. Again, a combo box is shown for the user to select a valid argument. To get the list of items for the particular command that we are building, we use code to set the RowSource for the Argument field to match the object type.</p> <p>The query, qryObjectTypes is the source we use to get the list of objects that we <em>can</em> work with. The first step is to filter the MSysObjects table to show only valid user arguments. Extracting the related arguments involves searching the MSysObjects table. MSysObjects has other objects that we normally don't access. To limit the list to items that we can use, the criteria for the list limits items with a Flag of 0.</p> <p>The next step is to match the list of objects with the object type that the command uses. Instead of hard-coding the types for each, I created a table with values to match for each object type. It uses tblMenuCommandTypes as a reference table to look in MSysObjects for the objects of that type. If the value of the system Type matches the command Type in the tblMenuCommandTypes, then the object qualifies as an object. For example, if the type is -32768, then the object is a form.</p> <p>This is the table I use to match objects with the type I'm using.</p> <div class="image_block"><a href="/b2e/media/blogs/dbell/Menu Command Types.png?mtime=1354381569"><img style="display: block; margin-left: auto; margin-right: auto;" src="/b2e/media/blogs/dbell/Menu Command Types.png?mtime=1354381569" alt="" width="348" height="244" /></a></div> <p>This is the query that extracts from MSysObjects based on the the table of  menu commands.</p> <div class="image_block"><a href="/b2e/media/blogs/dbell/qryObjectTypes.png?mtime=1354381624"><img style="display: block; margin-left: auto; margin-right: auto;" src="/b2e/media/blogs/dbell/qryObjectTypes.png?mtime=1354381624" alt="" width="450" /></a></div> <p>.. And the results from the query</p> <div class="image_block"><a href="/b2e/media/blogs/dbell/qryObjectTypes Result.png?mtime=1354381600"><img style="display: block; margin-left: auto; margin-right: auto;" src="/b2e/media/blogs/dbell/qryObjectTypes%20Result.png?mtime=1354381600" alt="" width="450" /></a></div> <p>Finally, the query is used to select the appropriate list of objects. This is done using code behind the edit menu item popup. In order to simplify execution and limit the amount of coding, I used the same constant-naming convention as was defined in the menu and I re-used the Select statement for setting the Row Source for each item. Following is a snippet from the code behind subfrmEditMenuItem, the popup that is used to set up an item in the menu.<br /><br />    Const conCmdRunCode = 8<br />    Const conCmdOpenTable = 9<br />    Const conCmdRunQuery = 10<br /><br />    Select Case Me.Command<br />        <br />        Case conCmdGotoMenu<br />            Me.Argument.RowSource = "SELECT MenuID, ItemText, ItemNumber FROM tblMenuItems WHERE (((ItemNumber)=0));"<br />        <br />        ...    <br />        Case conCmdRunCode<br />            Me.Argument.RowSource = "SELECT Name, Name FROM qryObjectTypes WHERE (((MSysObjects.Type)=-32761));"<br />        <br />        Case conCmdOpenTable<br />            Me.Argument.RowSource = "SELECT Name, Name FROM qryObjectTypes WHERE (((MSysObjects.Type)=1));"<br />        <br />        Case conCmdRunQuery<br />            Me.Argument.RowSource = "SELECT Name, Name FROM qryObjectTypes WHERE (((MSysObjects.Type)=5));"</p> <p>Notice that the display is a bit tricky. The combo box displays the Menu name (ItemText) for a Goto Menu command, and stores the MenuID as the argument. For the other arguments, what is displayed and stored is the same.</p> <h3>Coding for Renumbering, Deletions, etc.</h3> <p>While discovering how to set appropriate arguments is an accomplishment, there is still much to be done to get the Menu Editor fully functional. For one, we need to increment the item number when we add new items. We also have to be able to renumber items in the menu so there are no blank spaces, or to handle items that are deleted. And, we have to support some method of rearranging items in the menu. <br /><br />Getting the next item number for the menu is through the OnClick event of an "Add" button. The number is determined by the following code line.<br /><br />    ItemNumber = DMax("ItemNumber", "tblMenuItems", "MenuID = " &amp; [MenuID]) + 1<br /><br />In order for the entries to be displayed together the items should be in order and start from the number 1. The section of code for this renumbering selects and updates all items in the menu to insure this happens.<br /><br />    strSQL = "SELECT MenuID, ItemNumber, ItemText, Command, Argument FROM tblMenuItems " &amp; _<br />        "WHERE (((ItemNumber)&lt;&gt;0)) and ([MenuID] = " &amp; Me![MenuID] &amp; ") " &amp; _<br />        "ORDER BY ItemNumber"<br />    Set db = CurrentDb<br />    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)<br />    rst.MoveFirst<br />    i = 1<br />    Do<br />        rst.Edit<br />        rst!ItemNumber = i<br />        rst.Update<br />        rst.MoveNext<br />        i = i + 1<br />    Loop Until rst.EOF<br /><br />Renumbering is also important when deleting entries or arranging items, so the function is executed from several points in the code. For example, the event that monitors the deletion of records is the After Del Confirm and the only thing it does is renumber the menu items. <br /><br />Though I don't discuss that here, we will also need to have a user-friendly way to delete menus, navigate menus, and prompt if the user attempts to add too many items or use invalid commands. Another area that required some original coding was moving items up and down in the menu. Download the Sample Database from the link below and see the code behind the editmenu form for the algorithms used for that.</p> <p><a title="Menu Editor download" href="/dbell/downloads/SwitchSampleDB.accdb">Menu Editor download</a></p> <p>This database also has code that allows you to accept parameters at the bottom of the form. That's what the Expanded field in the table is all about. I just haven't had time to write about it yet. This is a first edition, so there are many features that could still be added, and styling that can be done to make the system look more professional. One could even emulate the Access Switchboard Manager's method of using three levels of popup forms and buttons for the items. If you improve it and make it public domain, let me know. I might add an additional link on this page to point to it.</p><div class="item_footer"><p><small><a href="https://tylerhosting.com/b2e/dbell.php/a-new-switchboard-manager">Original post</a> blogged on <a href="http://www.tylerhosting.com/b2e/">Switched Keys</a>.</small></p></div>
This series of posts has been talking about using Switchboards in Microsoft Access. Prior entries looked at ways to customize the Switchboard and how it functions, how to edit the Switchboard Items table, and the limitations of the internal Switchboard Manager. This entry is about building a new Switchboard Manager to support features added to the Switchboard system.

In this entry I introduce the Menu Manager. The Menu Manager is my version of the Switchboard Manager. Instead of the form Switchboard, I use frmMenu. Instead of the Switchboard Items table, I use tblMenuItems, and for the Switchboard Manager I use frmMenuEditor and related subforms. However, the Menu Editor is just a glorified Switchboard Manager so you could use it with your improved Switchboard and the default Switchboard files.

While the menu only requires the menu form frmMenu and the menu item table, tblMenuItems, the Menu Editor requires three tables, a query, and three form objects. Before mapping the steps I took, here's a description of the objects being used in the Menu Editor. The Menu Editor (frmMenuEditor) uses a subform (subfrmEditMenu) and reads the menu item table (tblMenuItems) to show options selected for the menu. When you click to edit one of the items a popup form (subfrmEditMenuItem) is displayed so that you can select commands and available arguments for each command. The information for this form is gathered from tblMenuCommands, tblMenuCommandTypes, and a query, qryObjectTypes that finds relevant objects in the system's MSysObjects table.

Reading the Menu Items Table

Now, let's look at the fields in the Menu Items table. As previously mentioned, the table that runs the menu is tblMenuItems and the combination of SwitchboardID and ItemNumber fields make an entry unique. The ItemText is what is displayed. The Command is a number that refers to the command in the code behind the menu form. The Argument can be either the object being opened, or a number that refers to another menu. When the menu and the menu editor opens a new menu, it restricts the items read to those with the designated MenuID. Those items are then displayed in item number order.

Editing Basics

There are three levels of editing, and a form for each level. First, the Menu Editor main form (frmMenuEditor) only reads in each menu title. This is done with a query of tblMenuItems where Item Number is 0. That imports all of the menu titles. The current setup allows you to select the menu you want to edit from a combo box at the top of the form. On top of the drop down is a textbox that is used to enter the title for new menus.

Next, the Menu Editor subform (subfrmEditMenu) reads in the items from the same table, except that only items <> 0 are included. Of course, there is a link between the parent and child forms so that only items for the active menuID are shown.

 

Finally, each menu item is edited in a popup form that must get the menu text, the command, and possibly an argument. Editing the menu items is where the most effort is concentrated.

Editing the items

When adding or editing new commands there are three fields that need to be supplied in the item editor. The first is the text to be displayed in the menu. The second is the command. The third is the argument. The text that is supplied is as simple as typing it in.

The command requires a little more prompting, so a combo box is provided. Although the command refers to a number, we need a list of commands that we can select from. tblMenuCommands is the table that is used to display the prompt for each of the commands. The menu title item has no command or arguments so no editing is required. The other menu items, however, must be edited to provide command and in most cases, argument.

The argument requires a little more explanation. If a command requires an argument, which is usually the case, we have to show an appropriate list of arguments. If the command involves opening a form, we don't want to also list tables or reports. Again, a combo box is shown for the user to select a valid argument. To get the list of items for the particular command that we are building, we use code to set the RowSource for the Argument field to match the object type.

The query, qryObjectTypes is the source we use to get the list of objects that we can work with. The first step is to filter the MSysObjects table to show only valid user arguments. Extracting the related arguments involves searching the MSysObjects table. MSysObjects has other objects that we normally don't access. To limit the list to items that we can use, the criteria for the list limits items with a Flag of 0.

The next step is to match the list of objects with the object type that the command uses. Instead of hard-coding the types for each, I created a table with values to match for each object type. It uses tblMenuCommandTypes as a reference table to look in MSysObjects for the objects of that type. If the value of the system Type matches the command Type in the tblMenuCommandTypes, then the object qualifies as an object. For example, if the type is -32768, then the object is a form.

This is the table I use to match objects with the type I'm using.

This is the query that extracts from MSysObjects based on the the table of  menu commands.

.. And the results from the query

Finally, the query is used to select the appropriate list of objects. This is done using code behind the edit menu item popup. In order to simplify execution and limit the amount of coding, I used the same constant-naming convention as was defined in the menu and I re-used the Select statement for setting the Row Source for each item. Following is a snippet from the code behind subfrmEditMenuItem, the popup that is used to set up an item in the menu.

    Const conCmdRunCode = 8
    Const conCmdOpenTable = 9
    Const conCmdRunQuery = 10

    Select Case Me.Command
        
        Case conCmdGotoMenu
            Me.Argument.RowSource = "SELECT MenuID, ItemText, ItemNumber FROM tblMenuItems WHERE (((ItemNumber)=0));"
        
        ...    
        Case conCmdRunCode
            Me.Argument.RowSource = "SELECT Name, Name FROM qryObjectTypes WHERE (((MSysObjects.Type)=-32761));"
        
        Case conCmdOpenTable
            Me.Argument.RowSource = "SELECT Name, Name FROM qryObjectTypes WHERE (((MSysObjects.Type)=1));"
        
        Case conCmdRunQuery
            Me.Argument.RowSource = "SELECT Name, Name FROM qryObjectTypes WHERE (((MSysObjects.Type)=5));"

Notice that the display is a bit tricky. The combo box displays the Menu name (ItemText) for a Goto Menu command, and stores the MenuID as the argument. For the other arguments, what is displayed and stored is the same.

Coding for Renumbering, Deletions, etc.

While discovering how to set appropriate arguments is an accomplishment, there is still much to be done to get the Menu Editor fully functional. For one, we need to increment the item number when we add new items. We also have to be able to renumber items in the menu so there are no blank spaces, or to handle items that are deleted. And, we have to support some method of rearranging items in the menu.

Getting the next item number for the menu is through the OnClick event of an "Add" button. The number is determined by the following code line.

    ItemNumber = DMax("ItemNumber", "tblMenuItems", "MenuID = " & [MenuID]) + 1

In order for the entries to be displayed together the items should be in order and start from the number 1. The section of code for this renumbering selects and updates all items in the menu to insure this happens.

    strSQL = "SELECT MenuID, ItemNumber, ItemText, Command, Argument FROM tblMenuItems " & _
        "WHERE (((ItemNumber)<>0)) and ([MenuID] = " & Me![MenuID] & ") " & _
        "ORDER BY ItemNumber"
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    rst.MoveFirst
    i = 1
    Do
        rst.Edit
        rst!ItemNumber = i
        rst.Update
        rst.MoveNext
        i = i + 1
    Loop Until rst.EOF

Renumbering is also important when deleting entries or arranging items, so the function is executed from several points in the code. For example, the event that monitors the deletion of records is the After Del Confirm and the only thing it does is renumber the menu items.

Though I don't discuss that here, we will also need to have a user-friendly way to delete menus, navigate menus, and prompt if the user attempts to add too many items or use invalid commands. Another area that required some original coding was moving items up and down in the menu. Download the Sample Database from the link below and see the code behind the editmenu form for the algorithms used for that.

Menu Editor download

This database also has code that allows you to accept parameters at the bottom of the form. That's what the Expanded field in the table is all about. I just haven't had time to write about it yet. This is a first edition, so there are many features that could still be added, and styling that can be done to make the system look more professional. One could even emulate the Access Switchboard Manager's method of using three levels of popup forms and buttons for the items. If you improve it and make it public domain, let me know. I might add an additional link on this page to point to it.

]]>
https://tylerhosting.com/b2e/dbell.php/a-new-switchboard-manager#comments https://tylerhosting.com/b2e/dbell.php?tempskin=_rss2&disp=comments&p=290
Overcoming Access Switchboard Limitations https://tylerhosting.com/b2e/dbell.php/overcoming-access-switchboard-limitations Sat, 01 Dec 2012 04:12:00 +0000 Dana Bell Information Technology Professional Microsoft Office Access 289@https://tylerhosting.com/b2e/
Deprecated: Creation of dynamic property Item::$cache_has_content_parts is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 4911

Deprecated: Creation of dynamic property Item::$renderers_validated is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 11107

Deprecated: Creation of dynamic property Item::$pages is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 2412

Deprecated: Creation of dynamic property ItemSettings::$count_col_key_names is deprecated in /home4/tylering/public_html/b2e/inc/settings/model/_abstractsettings.class.php on line 121
<p><em>This a second article on using the Microsoft Access Switchboard. I use this feature all of the time, although I don't see many other Access programs that do. Part of that may be the limitations of the feature. One limitation is that you can't have more that 8 items per menu. A second limitation is that only a very limited number of actions are available to the Switchboard.</em></p> <h3>Eight Item Limitation</h3> <p>The Switchboard provided by Microsoft Access limits you to eight lines per switchboard page. That's a reasonable number, if there has to be a limit. Increasing the limit to 9 or 10 is easy to do, though. We can change how the Switchboard form operates but we can't change how the internal Switchboard Manager handles that. The Switchboard Manager is the internal tool used to edit the Switchboards, or menus.<br /><br />Open the Switchboard form in design mode and add 2 more option sets (button plus text label)below the existing ones. You can do that easily by selecting the last two option sets, copy (don't deselect) and then paste. They will appear just below the existing ones and closely aligned. Next, open the properties for the first new control, make sure that the first new button is named Option9, the text is OptionLabel9, and that the On Click refers to =HandleButtonClick(9). Do the same for the other new button using the number 10.<br /><br />Now go to code behind the form and edit the FillOptions function. It is called be the OnCurrent event of the form. One way to do that is when the Switchboard form is opened in Design View. In the Design tab, click on the icon in the very top right to view code. The code will have a line with<br /><br />    Const conNumButtons = 8<br /><br />Change that to<br /><br />    Const conNumButtons = 10<br /><br />You still can't add more that 8 items using Switchboard Manager, but you can add the extra two items to the menu by editing the Switchboard Items directly. See the section below explaining the structure of the Switchboard Items table.</p> <h3>Function Limitations</h3> <p>A second limitation is that the provided Switchboard only supports eight actual functions, and you can only add these actions to a menu from the Switchboard Manager. The eight functions that you can use are:</p> <ol> <li>Go to another switchboard</li> <li>Open form in Add</li> <li>Open form in Edit</li> <li>Open report in print preview</li> <li>Design Application (Run switchboard manager)</li> <li>Exit</li> <li>Run macro</li> <li>Run code</li> </ol> <p>It is true that with Run macro and Run code you can do just about anything if you create the macro first, but that is time consuming and shouldn't be a necessity. There is a fix for this but it is only semi-automatic. You can alter the code behind the form to do other commands but you can't use the Switchboard manager to add those commands. Here's how to accomodate additional types in a Switchboard menu. For each additional option, add a new command and then the instructions to process that command.</p> <h4>Function Constants</h4> <p>In the HandleButtonClick function in the code behind the form, there is a section of constants.<br /><br />Just below<br /><br />    Const conCmdRunCode = 8<br /><br />add two new constants<br /><br />    Const conCmdOpenTable = 9<br />    Const conCmdRunQuery = 10</p> <h4>Function Code</h4> <p>Next, find the select statement further down in the function that associates the command with the option. <br /><br />Just below<br /><br />        ' Run code.<br />        Case conCmdRunCode<br />            Application.Run rs![Argument]<br /><br />Add the two new functions associated with the new constants.<br /><br />        ' Open a Data Table<br />        Case conCmdOpenTable<br />            DoCmd.OpenTable rs![Argument]<br /><br />        ' Run query.<br />        Case conCmdRunQuery<br />            DoCmd.OpenQuery rs![Argument]<br />            <br />Unfortunately again, you can't use the Switchboard manager to allow you to add them to a menu your are editing. That requires manually altering the Switchboard Items table, which is where all of the options are stored.</p> <h3>Manually Editing Switchboard Items</h3> <p>Adding the extra items mentioned in the first part, or using one of the new functions in a menu requires a little explanation of the Switchboard Items table. The table contains the fields SwitchboardID, Item Number, Item Text, Command, and Argument.</p> <p>SwitchboardID is the key to each set of commands that makes up a menu screen. All lines with the same ID are items on the menu. Item Number indicates the order of the items in the displayed menu. Item Number 0 is the name of the menu. Itemtext is what is displayed for each item. Command is the command that will be executed. These are the numbers that the constants above refer to. Finally, the argument is either the ID of the switchboard, or the name of the object to be opened. If the Command is 1, then the argument will be a number. <br /><br /></p> <div class="image_block"><a href="/b2e/media/blogs/dbell/Switchboard Items.png?mtime=1354309295"><img style="display: block; margin-left: auto; margin-right: auto;" src="/b2e/media/blogs/dbell/Switchboard%20Items.png?mtime=1354309295" alt="" width="500" /></a></div> <p>Now to modify the table. Sort the table by SwitchboardID and find the menu where you want to add the new functions. If there is only Item numbers 1,2,3,4 for SwitchboardID 3, you would want to add the option in SwitchboardID number 3 with the next available Item number, 5. Here's what you would enter in each field of the table.</p> <ul> <li>Create the new record with 3 as the SwitchboardID</li> <li>Enter the next Item Number</li> <li>Enter the text that will be displayed in the Switchboard</li> <li>Enter the number for the command (9 or 10 for one of the new commands)</li> <li>Enter the object (form, table, etc.) being executed in the argument field.</li> </ul> <p>Close the table, open the Switchboard, and navigate to the new options.</p> <h3>Renaming and Copies</h3> <p>Incidentally, you can change the name of the table used in the code, or use a different menu if you want. The Switchboard doesn't have to be named Switchboard, and the table doesn't have to be named Switchboard Items. You can copy/rename either or both. You could even have multiple Switchboards for multiple users. You will have to modify the code behind the new forms to point to the correct table, but that's as easy as using find/replace. Keep in mind that the Acces Switchboard Manager only works on the Switchboard Items table.</p> <p>The only way to handle the limitations of the Switchboard Manager is to create a new one that supports more than eight items, and more than the eight functions available. That's what I did in <a href="/b2e/dbell.php/a-new-switchboard-manager" target="_self">Menu Manager as the New Switchboard Manager</a>.</p><div class="item_footer"><p><small><a href="https://tylerhosting.com/b2e/dbell.php/overcoming-access-switchboard-limitations">Original post</a> blogged on <a href="http://www.tylerhosting.com/b2e/">Switched Keys</a>.</small></p></div>
This a second article on using the Microsoft Access Switchboard. I use this feature all of the time, although I don't see many other Access programs that do. Part of that may be the limitations of the feature. One limitation is that you can't have more that 8 items per menu. A second limitation is that only a very limited number of actions are available to the Switchboard.

Eight Item Limitation

The Switchboard provided by Microsoft Access limits you to eight lines per switchboard page. That's a reasonable number, if there has to be a limit. Increasing the limit to 9 or 10 is easy to do, though. We can change how the Switchboard form operates but we can't change how the internal Switchboard Manager handles that. The Switchboard Manager is the internal tool used to edit the Switchboards, or menus.

Open the Switchboard form in design mode and add 2 more option sets (button plus text label)below the existing ones. You can do that easily by selecting the last two option sets, copy (don't deselect) and then paste. They will appear just below the existing ones and closely aligned. Next, open the properties for the first new control, make sure that the first new button is named Option9, the text is OptionLabel9, and that the On Click refers to =HandleButtonClick(9). Do the same for the other new button using the number 10.

Now go to code behind the form and edit the FillOptions function. It is called be the OnCurrent event of the form. One way to do that is when the Switchboard form is opened in Design View. In the Design tab, click on the icon in the very top right to view code. The code will have a line with

    Const conNumButtons = 8

Change that to

    Const conNumButtons = 10

You still can't add more that 8 items using Switchboard Manager, but you can add the extra two items to the menu by editing the Switchboard Items directly. See the section below explaining the structure of the Switchboard Items table.

Function Limitations

A second limitation is that the provided Switchboard only supports eight actual functions, and you can only add these actions to a menu from the Switchboard Manager. The eight functions that you can use are:

  1. Go to another switchboard
  2. Open form in Add
  3. Open form in Edit
  4. Open report in print preview
  5. Design Application (Run switchboard manager)
  6. Exit
  7. Run macro
  8. Run code

It is true that with Run macro and Run code you can do just about anything if you create the macro first, but that is time consuming and shouldn't be a necessity. There is a fix for this but it is only semi-automatic. You can alter the code behind the form to do other commands but you can't use the Switchboard manager to add those commands. Here's how to accomodate additional types in a Switchboard menu. For each additional option, add a new command and then the instructions to process that command.

Function Constants

In the HandleButtonClick function in the code behind the form, there is a section of constants.

Just below

    Const conCmdRunCode = 8

add two new constants

    Const conCmdOpenTable = 9
    Const conCmdRunQuery = 10

Function Code

Next, find the select statement further down in the function that associates the command with the option.

Just below

        ' Run code.
        Case conCmdRunCode
            Application.Run rs![Argument]

Add the two new functions associated with the new constants.

        ' Open a Data Table
        Case conCmdOpenTable
            DoCmd.OpenTable rs![Argument]

        ' Run query.
        Case conCmdRunQuery
            DoCmd.OpenQuery rs![Argument]
            
Unfortunately again, you can't use the Switchboard manager to allow you to add them to a menu your are editing. That requires manually altering the Switchboard Items table, which is where all of the options are stored.

Manually Editing Switchboard Items

Adding the extra items mentioned in the first part, or using one of the new functions in a menu requires a little explanation of the Switchboard Items table. The table contains the fields SwitchboardID, Item Number, Item Text, Command, and Argument.

SwitchboardID is the key to each set of commands that makes up a menu screen. All lines with the same ID are items on the menu. Item Number indicates the order of the items in the displayed menu. Item Number 0 is the name of the menu. Itemtext is what is displayed for each item. Command is the command that will be executed. These are the numbers that the constants above refer to. Finally, the argument is either the ID of the switchboard, or the name of the object to be opened. If the Command is 1, then the argument will be a number.

Now to modify the table. Sort the table by SwitchboardID and find the menu where you want to add the new functions. If there is only Item numbers 1,2,3,4 for SwitchboardID 3, you would want to add the option in SwitchboardID number 3 with the next available Item number, 5. Here's what you would enter in each field of the table.

  • Create the new record with 3 as the SwitchboardID
  • Enter the next Item Number
  • Enter the text that will be displayed in the Switchboard
  • Enter the number for the command (9 or 10 for one of the new commands)
  • Enter the object (form, table, etc.) being executed in the argument field.

Close the table, open the Switchboard, and navigate to the new options.

Renaming and Copies

Incidentally, you can change the name of the table used in the code, or use a different menu if you want. The Switchboard doesn't have to be named Switchboard, and the table doesn't have to be named Switchboard Items. You can copy/rename either or both. You could even have multiple Switchboards for multiple users. You will have to modify the code behind the new forms to point to the correct table, but that's as easy as using find/replace. Keep in mind that the Acces Switchboard Manager only works on the Switchboard Items table.

The only way to handle the limitations of the Switchboard Manager is to create a new one that supports more than eight items, and more than the eight functions available. That's what I did in Menu Manager as the New Switchboard Manager.

]]>
https://tylerhosting.com/b2e/dbell.php/overcoming-access-switchboard-limitations#comments https://tylerhosting.com/b2e/dbell.php?tempskin=_rss2&disp=comments&p=289
Customizing the Access Switchboard https://tylerhosting.com/b2e/dbell.php/customizing-the-access-switchboard Thu, 29 Nov 2012 10:21:00 +0000 Dana Bell Information Technology Professional Microsoft Office Access 288@https://tylerhosting.com/b2e/
Deprecated: Creation of dynamic property Item::$cache_has_content_parts is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 4911

Deprecated: Creation of dynamic property Item::$renderers_validated is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 11107

Deprecated: Creation of dynamic property Item::$pages is deprecated in /home4/tylering/public_html/b2e/inc/items/model/_item.class.php on line 2412

Deprecated: Creation of dynamic property ItemSettings::$count_col_key_names is deprecated in /home4/tylering/public_html/b2e/inc/settings/model/_abstractsettings.class.php on line 121
<p><em>This is the first of possibly multiple posts on the Microsoft Access Switchboard. In the next few entries I will look at the design of the menu, editing of the code for more functionality, and possibly discuss my development of an alternate menu editor. I begin with the design.</em></p> <p>Microsoft Access comes with a feature that allows developers to  create menus, or Switchboards, for the functions in the database. I use this feature all of the time, although I don't see many other Access programs that do. Learning how to use this, or a variation of it can save some development time and add to functionality of an Access database. The standard Switchboard, however, needs a few enhancements to make it look and work better.</p> <h3>Preliminaries</h3> <p>The Switchboard in 2007 and later is somewhat less flexible. First, it is designed as a continuous form, which is not as well suited for design. That also makes it difficult to design as a standalone element. Additionally, this one uses VBA code instead of the newer macros. In order to get the older VBA switchboard I create a Switchboard in 2003 blank database format and import it into the current database.</p> <ul> <li>Create a mdb database - Location, select *.mdb (or just import from a previously created mdb).</li> <li>Select Database Tools tab, and then Switchboard Manager.</li> <li>Access will prompt if you want to create a new switchboard. Say Yes.</li> <li>Save/Close the database</li> <li>Open the database you want to use the Switchboard in</li> <li>Import the Switchboard form and the Switchboard Items table.</li> </ul> <p>I love tabs in browsers but in Access I like to use windows instead of tabs, especially if there is little to show. If your version defaults to tabs you can change it by going to Access Options and select Overlapping Windows. While you are there, you can set the form that automatically opens when you start the database to Switchboard.</p> <p>Try the Switchboard by opening the form Switchboard. Add items to it from the tab Database Tools &gt; Switchboard Manager. The following is one I created with some dummy entries for demonstration purposes.</p> <div class="image_block"><a href="/b2e/media/blogs/dbell/Default Switchboard.png?mtime=1354158115"><img style="display: block; margin-left: auto; margin-right: auto;" src="/b2e/media/blogs/dbell/Default%20Switchboard.png?mtime=1354158115" alt="" width="268" /></a></div> <h3>Designing the Switchboard Menu</h3> <p>The Switchboard is a form just like any other, with some code behind it. To change the design, colors, spacing and fonts simply go to design mode from Home &gt; View &gt; Design View. One of the things I always do when starting a project and its Switchboard is to make the option selections much bigger. Follow these steps to resize the options and buttons.</p> <ul> <li>Select all of the options and buttons</li> <li>Increase vertical spacing</li> <li>Resize by dragging the bottom resizing handle</li> <li>Deselect and select all of the buttons</li> <li>Resize by dragging the resize handle to the left until they appear square</li> <li>Deselect and select only the options on the right</li> <li>Change the size to 12 point</li> </ul> <div class="image_block"><a href="/b2e/media/blogs/dbell/Default Switchboard Design Button Selection.png?mtime=1354158114"><img style="display: block; margin-left: auto; margin-right: auto;" src="/b2e/media/blogs/dbell/Default%20Switchboard%20Design%20Button%20Selection.png?mtime=1354158114" alt="" width="296" /></a></div> <p>Next, the color background images need to be adjusted to fit the enlarged options.</p> <ul> <li>Select the left rectangular 'border'</li> <li>Resize by dragging down to just below the bottom option</li> <li>Drag the bottom canvas resizer up to meet the left border</li> <li>Do the same for the top right border.</li> </ul> <div class="image_block"><a href="/b2e/media/blogs/dbell/Default%20Switchboard%20Design%20Border%20Selection.png?mtime=1354158113"><img style="display: block; margin-left: auto; margin-right: auto;" src="/b2e/media/blogs/dbell/Default%20Switchboard%20Design%20Border%20Selection.png?mtime=1354158113" alt="" width="296" /></a></div> <p>Except for the option buttons that should be in order, arrange elements like you want them. I haven't done it here but you will very likely want to use colors that match the rest of the forms you are using. The left side might be a good place for a company logo, or it could be eliminated altogether if you wish. Squeeze in the bottom and side and view the form in Form View. Click on Size to Fit Form. Compare and smile.</p> <div class="image_block"><a href="/b2e/media/blogs/dbell/Finished%20Switchboard%20Form%20Mode.png?mtime=1354158116"><img style="display: block; margin-left: auto; margin-right: auto;" src="/b2e/media/blogs/dbell/Finished%20Switchboard%20Form%20Mode.png?mtime=1354158116" alt="" width="274" /></a></div> <h3>Adding a Home Page Click</h3> <p>Web pages very frequently use the logo at the top of their web page to allow users to click and return to their home page. You can do that here as well. There are several ways, but the one that I use does not have some of the side effects of using a clickable label in the header.<br /><br />First right click on the Detail bar and select Form Header/Footer. Then expand the Form Header and select the Heading (Sample Database), its shadow, and its underline and move them up into the Header. Use Shift Click to select/deselect multiple items. Change the back color to match the lower border elements. Move the Detail elements up to fill the space.</p> <div class="image_block"><a href="/b2e/media/blogs/dbell/Finished Switchboard Header Design.png?mtime=1354158117"><img style="display: block; margin-left: auto; margin-right: auto;" src="/b2e/media/blogs/dbell/Finished%20Switchboard%20Header%20Design.png?mtime=1354158117" alt="" width="335" /></a></div> <p>Add a large label to the Header section with any text, and then set the fore color to the same color as the background. Back Style defaults to Transparent which is what we want here. Then make sure to arrange it so that the label is on top. Close the Form Footer area.</p> <div class="image_block"><a href="/b2e/media/blogs/dbell/Switchboard Header Design Label.png?mtime=1354158120"><img style="display: block; margin-left: auto; margin-right: auto;" src="/b2e/media/blogs/dbell/Switchboard%20Header%20Design%20Label.png?mtime=1354158120" alt="" width="335" /></a></div> <div class="image_block">After creating the label and naming it appropriately (I name it Logo), create an OnClick event with the following line.</div> <p>    Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=1"</p> <p>Now when the user clicks on the menu's header, he gets redirected to the Main Switchboard. Of course, you could also use buttons or add any other navigation feature.</p> <h3>Form Title</h3> <p>The form for the Switchboard out of the box has a static label in the header, that has something like the Company name, or in this example, Sample Database, that doesn't change from switchboard to switchboard. If you change the label to a textbox and set the Source to Itemtext, it will show the title of the menu in the header, like Main Switchboard, instead of just in the top border of the form. Of course, you will need to style it so it looks good, disable tabbing into it, and make sure it is behind the Logo.</p> <div class="image_block"><a href="/b2e/media/blogs/dbell/Switchboard Header Itemtext Form Mode.png?mtime=1354158120"><img style="display: block; margin-left: auto; margin-right: auto;" src="/b2e/media/blogs/dbell/Switchboard%20Header%20Itemtext%20Form%20Mode.png?mtime=1354158120" alt="" width="286" /></a></div> <p>One of the reasons that more developers do not use the Switchboard may be the seeming limitations of the feature. There are limited actions that you can execute with it and there is a limit of 8 items per Switchboard. With a little coding, some of these  barriers can be overcome. Read about it in <a href="/b2e/dbell.php/overcoming-access-switchboard-limitations" target="_self">Overcoming Access Switchboard Limitations</a>.</p><div class="item_footer"><p><small><a href="https://tylerhosting.com/b2e/dbell.php/customizing-the-access-switchboard">Original post</a> blogged on <a href="http://www.tylerhosting.com/b2e/">Switched Keys</a>.</small></p></div>
This is the first of possibly multiple posts on the Microsoft Access Switchboard. In the next few entries I will look at the design of the menu, editing of the code for more functionality, and possibly discuss my development of an alternate menu editor. I begin with the design.

Microsoft Access comes with a feature that allows developers to  create menus, or Switchboards, for the functions in the database. I use this feature all of the time, although I don't see many other Access programs that do. Learning how to use this, or a variation of it can save some development time and add to functionality of an Access database. The standard Switchboard, however, needs a few enhancements to make it look and work better.

Preliminaries

The Switchboard in 2007 and later is somewhat less flexible. First, it is designed as a continuous form, which is not as well suited for design. That also makes it difficult to design as a standalone element. Additionally, this one uses VBA code instead of the newer macros. In order to get the older VBA switchboard I create a Switchboard in 2003 blank database format and import it into the current database.

  • Create a mdb database - Location, select *.mdb (or just import from a previously created mdb).
  • Select Database Tools tab, and then Switchboard Manager.
  • Access will prompt if you want to create a new switchboard. Say Yes.
  • Save/Close the database
  • Open the database you want to use the Switchboard in
  • Import the Switchboard form and the Switchboard Items table.

I love tabs in browsers but in Access I like to use windows instead of tabs, especially if there is little to show. If your version defaults to tabs you can change it by going to Access Options and select Overlapping Windows. While you are there, you can set the form that automatically opens when you start the database to Switchboard.

Try the Switchboard by opening the form Switchboard. Add items to it from the tab Database Tools > Switchboard Manager. The following is one I created with some dummy entries for demonstration purposes.

Designing the Switchboard Menu

The Switchboard is a form just like any other, with some code behind it. To change the design, colors, spacing and fonts simply go to design mode from Home > View > Design View. One of the things I always do when starting a project and its Switchboard is to make the option selections much bigger. Follow these steps to resize the options and buttons.

  • Select all of the options and buttons
  • Increase vertical spacing
  • Resize by dragging the bottom resizing handle
  • Deselect and select all of the buttons
  • Resize by dragging the resize handle to the left until they appear square
  • Deselect and select only the options on the right
  • Change the size to 12 point

Next, the color background images need to be adjusted to fit the enlarged options.

  • Select the left rectangular 'border'
  • Resize by dragging down to just below the bottom option
  • Drag the bottom canvas resizer up to meet the left border
  • Do the same for the top right border.

Except for the option buttons that should be in order, arrange elements like you want them. I haven't done it here but you will very likely want to use colors that match the rest of the forms you are using. The left side might be a good place for a company logo, or it could be eliminated altogether if you wish. Squeeze in the bottom and side and view the form in Form View. Click on Size to Fit Form. Compare and smile.

Adding a Home Page Click

Web pages very frequently use the logo at the top of their web page to allow users to click and return to their home page. You can do that here as well. There are several ways, but the one that I use does not have some of the side effects of using a clickable label in the header.

First right click on the Detail bar and select Form Header/Footer. Then expand the Form Header and select the Heading (Sample Database), its shadow, and its underline and move them up into the Header. Use Shift Click to select/deselect multiple items. Change the back color to match the lower border elements. Move the Detail elements up to fill the space.

Add a large label to the Header section with any text, and then set the fore color to the same color as the background. Back Style defaults to Transparent which is what we want here. Then make sure to arrange it so that the label is on top. Close the Form Footer area.

After creating the label and naming it appropriately (I name it Logo), create an OnClick event with the following line.

    Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=1"

Now when the user clicks on the menu's header, he gets redirected to the Main Switchboard. Of course, you could also use buttons or add any other navigation feature.

Form Title

The form for the Switchboard out of the box has a static label in the header, that has something like the Company name, or in this example, Sample Database, that doesn't change from switchboard to switchboard. If you change the label to a textbox and set the Source to Itemtext, it will show the title of the menu in the header, like Main Switchboard, instead of just in the top border of the form. Of course, you will need to style it so it looks good, disable tabbing into it, and make sure it is behind the Logo.

One of the reasons that more developers do not use the Switchboard may be the seeming limitations of the feature. There are limited actions that you can execute with it and there is a limit of 8 items per Switchboard. With a little coding, some of these  barriers can be overcome. Read about it in Overcoming Access Switchboard Limitations.

]]>
https://tylerhosting.com/b2e/dbell.php/customizing-the-access-switchboard#comments https://tylerhosting.com/b2e/dbell.php?tempskin=_rss2&disp=comments&p=288

Deprecated: substr(): Passing null to parameter #1 ($string) of type string is deprecated in /home4/tylering/public_html/b2e/inc/sessions/model/_hit.class.php on line 932

Deprecated: substr(): Passing null to parameter #1 ($string) of type string is deprecated in /home4/tylering/public_html/b2e/inc/sessions/model/_hit.class.php on line 933

Deprecated: Creation of dynamic property Hit::$is_lynx is deprecated in /home4/tylering/public_html/b2e/inc/sessions/model/_hit.class.php on line 571

Deprecated: Creation of dynamic property Hit::$is_firefox is deprecated in /home4/tylering/public_html/b2e/inc/sessions/model/_hit.class.php on line 572

Deprecated: Creation of dynamic property Hit::$is_gecko is deprecated in /home4/tylering/public_html/b2e/inc/sessions/model/_hit.class.php on line 573

Deprecated: Creation of dynamic property Hit::$is_IE is deprecated in /home4/tylering/public_html/b2e/inc/sessions/model/_hit.class.php on line 574

Deprecated: Creation of dynamic property Hit::$is_winIE is deprecated in /home4/tylering/public_html/b2e/inc/sessions/model/_hit.class.php on line 575

Deprecated: Creation of dynamic property Hit::$is_macIE is deprecated in /home4/tylering/public_html/b2e/inc/sessions/model/_hit.class.php on line 576

Deprecated: Creation of dynamic property Hit::$is_chrome is deprecated in /home4/tylering/public_html/b2e/inc/sessions/model/_hit.class.php on line 577

Deprecated: Creation of dynamic property Hit::$is_safari is deprecated in /home4/tylering/public_html/b2e/inc/sessions/model/_hit.class.php on line 578

Deprecated: Creation of dynamic property Hit::$is_opera is deprecated in /home4/tylering/public_html/b2e/inc/sessions/model/_hit.class.php on line 579

Deprecated: Creation of dynamic property Hit::$is_NS4 is deprecated in /home4/tylering/public_html/b2e/inc/sessions/model/_hit.class.php on line 580