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++.
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 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.
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.
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).
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
And that's just off the top. It was/is an educational experience, and I enjoy education.
Download the installer or the help file that describes the features from www.tylerhosting.com/hoot/.
]]>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.
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.
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
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.
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.
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.
In addition to the limitations, and often because of the limitations, Excel contains potential risks.
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.
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.
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.
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 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 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 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]
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.
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.
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.
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]
Detecting errors can be as simple as regularly checking formulas being used in a spreadsheet, but there are other methods for detecting errors.
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.
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.
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.
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]:
Secondly, each individual should develop good skills and practices when creating spreadsheets. Consider these few, more specific, guidelines for developing spreadsheets.
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.
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.
There are many ways to evaluate spreadsheet development. This is a simplified series of questions you might ask about new spreadsheets.
Create spreadsheets to get new information instead of just transferring your existing information to Excel.
Know what information you have to work with, and if it contains what you need to get useful information.
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.
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.
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 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
[2] http://blogs.technet.com/b/hub/archive/2010/01/15/where-should-i-put-my-data-excel-or-access.aspx
[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.
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.
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
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
The following code (GetSourceDB) is what gets caption information from the SQL statement of the query
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.
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.
]]>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.
SELECT tblBEVersion.VersionID, tblBEVersion.BEVersionNumber, tblFEVersion.VersionID, tblFEVersion.FEVersionNumber
FROM tblBEVersion INNER JOIN tblFEVersion ON tblBEVersion.[VersionID] = tblFEVersion.[VersionID];
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.
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.
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
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
' 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
]]>
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.
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.
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.
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.
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.
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.
]]>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.
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:
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.
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
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.
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.
Close the table, open the Switchboard, and navigate to the new options.
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.
]]>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.
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.
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.
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.
Next, the color background images need to be adjusted to fit the enlarged options.
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.
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.
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.
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.
]]>