Techno Granny Show, Custom Designed Software with Microsoft Access

This show was originally broadcast on July 6, 2009 and archived version is available at: http://www.talkshoe.com/talkshoe/web/tscmd/tc/30986 or on TechnoGrannyShow's unique radio channel at http://www.positivelypittsburghlivemagazine.com


Dana Del Bianco is president of DCDB Inc, a custom database development company. She is a graduate of Carnegie Mellon University, and has been designing databases for 12 years.

DCDB Inc designs databases for small- to medium sized businesses. We primarily use Microsoft Access as the development platform. Access is an incredibly powerful tool. It allows for sophisticated programming in Visual Basic. But—most importantly—it is very cost-effective for the client. Since Access is part of Microsoft Office, most companies already own it. There is no additional software licensing costs. Its very easy to maintain, so companies without full-time IT support can still take advantage of the benefits of having a custom software program.

Why custom software? Customized software works the way you do. Off the shelf software will make you adapt your business practices to the way it works.

Many people ask “What type of information can I track in Access?” The short answer is—anything! If you have data, you can track it. In many cases, people will have numerous linked Excel spreadsheets tracking their data. The problem with this approach is that Excel does not have data validity checking. It will let you type anything into a field. Someone can accidentally type text in a number or date field, and Excel will let it. Access allows you to control this type of data entry and more. It protects the quality and validity of your data.

Also, report generation is very difficult out of a spreadsheet. Reporting from a spreadsheet can be extremely time-consuming. You have to hide columns, sort columns and more. With Access, reports can be designed to run at the click of a button. They easily prompt for dates or other criteria, making it very simple to customize your data to answer the question at hand.

Access is a relational database. That means that data is stored in different tables, and the tables are related to each other using a key field. The beauty of the relational database is that you never have to type the same information more than once. For example, if you are entering customer contacts into a database, you would have the company name in one table, and the individual employees’ names in a different table. You would then just relate the employees to the company where they work. You would not have to type the company name for every employee. In addition to saving lots of time with data entry, this design also eliminates the chance of having a company’s address typed in many different ways.

You may have opened Access on your computer and been very intimidated by what you saw. You quickly closed it and just went back to entering information into the more familiar Excel. What you didn’t realize is that what you were looking at is the nuts and bolts of the database that developers use to create a database. The finished product is very different. When you open up a completed database, it will be menu driven, just like any other software. You navigate screens by clicking on the appropriate buttons. You easily tab through fields entering data. A well-written database is extremely user-friendly. Don’t be intimidated! You honestly don’t need to know a thing about Access development to use an Access database!

If, however, you would like to develop your own Access databases, here are a few basics for you.

Access is made up of different types of objects that work together to create a database.

Tables—this is where you store your data. You create different fields that belong to a single record. All of the fields in a record are always associated with each other—you can’t accidentally sort one column and lose the connection to the rest.

Queries—queries allow you to ask questions of your data. You can use multiple joined tables in a single query. You can also place criteria on a query to limit the data you see. For example, you can only show records that are within a certain date range. You can even use formulas in queries, much like you would use them in Excel.

Forms—forms display the data from tables and queries in a more user-friendly manner. They make data entry very easy. You can also place data integrity controls on forms. For example, you can put a drop-down menu on a field to limit the options that can be entered into that field. Forms are also used to create menus to help navigate your database. Virtually all of your database interactions is through forms.

Reports—reports allow you to take data from a table or query and format it in a more useful manner. You can use different sorts and different groupings with subtotals to make your data more meaningful.

Macros/Modules—macros and modules are where you can use more sophisticated programming to automate database tasks. For example, you can write a module in visual basic that looks up information in a table when a particular option is selected on a form. This is truly where Access is most powerful. You can use visual basic to write extremely sophisticated code to run in your database.

Dana Del Bianco
DCDB, Inc.
412-244-5370
dana@dcdbinc.com
http://www.dcdbinc.com