Where does an Access Database Application fit into my Organization

Many people don't understand Microsoft Access and have a hard time seeing how it can help them.  They hear the word "database" and think "large system", "programming", "servers", and get overwhelmed.  When they have data to store, they revert back to Excel spreadsheets and workbooks or just putting it in Word.  True, its safe and easy and I can see it there right away.  Incorporating an Access database into your organization doesn't have to conjur up negative feelings.  Hopefully, this post will help you answer the question, "Where does an Access Database fit into my Organization?"

Many data challenges that companies face are not large enough or important enough to merit a solution that cost $100,000 or more. Yet just because the challenge isn't large enough to merit this investment, it doesn't mean that it doesn't exist. Access can meet this in between range of data issues and challenges.

Storing data in Excel
As I mentioned earlier, and in other blog posts (see Beyond the Spreadsheet post), many people default to storing data in Excel.  Its is easy to get started with and usually requires no investment.  One downside to getting started quickly is that users rarely take the time to think about the data that is being stored, the amount, or how the data will be used.  One thing to make clear is that Excel is not a database.  It is not relational and thus does not have all of the advantages that a database provides.

Another thing to consider is that storing data in Excel is not secure and drives IT guys crazy.  The average cost of creating an Excel application is $500. Excel is used because it is simple. People know it and can use it very quickly.

Using an Access Database Application
Access is the most popular database in the world.   It can solve a range of problems (see Top 10 Reasons to use Access post) and can still be deployed fairly quickly.  True that it takes some knowledge of Access.  Access provides a great Return on Investment.  It serves as a great tool for prototyping.  The average cost of an Access solution is:

Access Individual user system - $3,000
Access Multi-User system - $10,000
Access Department Level system - $25,000

Using an Server  Database Application

Larger than Access is a server side database.  Examples of server side databases are SQL Server and Oracle.  These are major systems with major investments.  They are the databases that, mentioned earlier, are not useful for many small problems.  They require hardware, software, database adminstrators, security concerns, conectivity concerns, and the list goes on and on.  The avgerage costs for a server side database are:

SQL Server - $500,000
Oracle - $2,000,000

There are instance where an Access database can evolve into a server side application.  This is another advantage of Access as it allowed you to protype a system, determine the details, and work out the solution at a smaller investment, thus making the upgrade to SQL Server must more efficient and ecconomical.

Most small businesses are never going to have the funds for ability to create true server side databases unless they outsource and rent server space somewhere.


So in short, Access should be used when you are ready to graduate from using Excel and really commit to building an Application, but aren't ready to make a giant investment into the solution. 
Robby

Things to do before you get building

I recently started doing some work with Filemaker Pro as a database development tool. For those of us who are "born and bred" Access developers, this is like turning to the dark side. However, software prejudices aside, I was struck by how the stuff you do before you get into the actual building of a database are very much the same. The logic of a well designed relational database goes deeper than the software used to develop it. So, what are some of these steps?


1. Think about the data: What things will the database be about? A good way to start is simply to write a list in good old English (or any other language you happen to be fluent in).

• Watch for the nouns you use: Clients, employees, invoices, appointments. These are the data entities your database will incorporate.


• Ask yourself what you want to know about these things. Sometimes these bits of information will be the attributes (aka fields) describing the data entity (e.g. name of client, date of invoice) but sometimes they may give rise to a new entity. A good example of this would be the payment of an invoice. If you simply want to indicate whether an invoice has been paid (a verb), you can simply attach an attribute to the entity "invoice" (which will probably become a "yes/no" field in your invoice table). But if there are things you want to know about THE PAYMENT (a noun), such as the amount, date, and method of payment, then the payment itself becomes a new data entity that will have, as one of its attributes, a reference to the invoice entity.


• Create a simple Entity diagram: As you turn your narrative into a graphich representation, you will clarify the entitites involved and their relationships to each other. There are many free tools available for download to assist in this process.

2. What does the system need to do or produce?

• Initially, try to limit this to the core functions: those things that are the main reasons for the database.


• Look for any gaps in your data entity analysis. When thinking about a function that needs to take place, or a report that needs to be produced, we often discover that we have not properly included the necessary data in our design.


• Start to envision what forms will need to be developed. Create a list of forms with key functions for each (Record retrieval, data addition/edit/deletion)

3. Consider the installation environment and constraints:

• If this is to be an Access databasse, does the intended user have Access installed? If so, what version? If not, how many user licenses are needed? (Developers can also consider packaging a solution as a runtime version to avoid the need to purchase Access. However, this introduces other issues.)


• Where will the database reside: on a file server or a computer?


• Will it be shared? If so, by how many people and how will they connect to the host location?


• Are all users the same? Will you need to restrict certain data or functions for different users?


• How secure is the environment?

4. Develop a naming convention: This may seem superficial but having a logical way of naming your database objects will make it easier to follow the flow of the system and to make modifications later on. Although Access has gotten better at updating references to tables and queries if you change the name later on, problems still come up when you do this. Experienced users/developers probably have a system that works well for them. We have used some of the following conventions:

• The names of tables that relate to data entities start with "T_" and names for those that are used to look-up values used in data entry begin with "LU_"


• Don't leave spaces in the names of objects. For readability, use underscores or hyphens. This is more important if you intend to do programming in VBA.


• Try to group related objects by using with similiar names; employee_bio, employee_addresses, employee_names.

As with building a house, working out the design concepts is the first step in assuring a successful outcome.

Joe

How secure is Microsoft Access? Part 2

In our last post, we started to discuss the how secure Microsoft Access is. I provided some quick and easy ways to keep out preying eyes and that can be implemented right away. Please look back at "How secure is Microsoft Access? Part 1" to see these.

In this post, I'm going to go into a few more complex methods for securing your Access database.

I. Splitting your tables from the client database. A common practice when devleoping an Access database is to create a "front-end" and a "back-end". The front-end is the "program". It is the database that will store all your forms, reports, queries, etc. It will NOT contain the tables. The tables will be stored in a second database or "back-end". The front-end will access these tables as linked tables. This setup is used for a number of beneficial reasons, but you can utilize this to really secure your data.

1. After you create your back-end database and the tables, set a database password on the file. Then when you go into the front-end and link to these tables, you will be prompted for a password. The password will be stored and now users who don't know the password will only be able to access the tables by going into the front-end.

2. With a little VBA code, you can set up your database to link to the tables every time you open the database. If the user authenticates correctly, then the tables will be linked. If the user does not, the tables will not be relinked. Thus they can not be accessed at all.

3. You don't have to have just one set of tables. You can have mutliple Access files each storing certain related tables. Then, the authentication procedure that you built, can determine which tables need to be linked and which do not. Thus each user will ONLY have access to those tables that they have been granted access to.

II. Turn your front-end into an MDE file - Once you have finished developing your database and before you distribute it users, it is good practice to turn the front-end file into an MDE file (ACCDE file in Access 2007 and later). By creating an MDE file, you restricted the user from going into design view of Forms and Reports, and from viewing VBA code. This not only will protect your code, but it will hide sensitive information like the path to the data files and passwords needed to access them.

On a side note, an MDE file will also perform better than an MDB file.

III. Piggy back on existing security - If your database is going to reside on a network file server, you probably already have network security in place to access those shared drives. You can set Access up to piggy back on top of this existing security. In order for me to gain access to the shared drives at InfoCore, I have to sign into our network with a user login and password (lets say user name rbuonocore). This user name and password is stored in the network management system and not in Access. When I open up Access, you can write code to "pull" the network user name of the person signed in. In this case, when I open up my database, it will determine that the person signed into the network is rbuonocore. I can then build my security in Access around permissions granted to rbuonocore. I don't have to worry about authenticating or storing passwords because my network management system has already done that for me. This same concept can be used with normal Windows logins as well.

So...How secure is Microsoft Access? Although not as secure as a full blown database management system, a very secure system can be created by using all these listed methods in combination; one that protects access to the system, access to the data, and access to functional areas inside the database.

-Robby

How secure is Microsoft Access? Part 1

One complaint I hear from many IT professionals who don't like Microsoft Access is that it really isn't very secure. Although Access is not as secure as some larger Database Management Systems, there are a number of things you can do, that when used in combination, can really help to secure your database. In this post, I'll present a few simple tasks that you can do that will keep out preying eyes. These methods are quick and easy and can be implemented right away.

I. Encrypting your database. Encrypting a database compacts the database file and helps protect it from being read by a word processor. Encrypting is particularly useful when you transmit a database electronically (FTP or Email), or when your store it on a flash drive or compact disc. Remember though that most databases will grow very fast and thus transporting them becomes very difficult anyway. Also, many email programs (Outlook included) don't like to receive emails with Access files attached. They basically view them as threats and it's difficult to receive. One quick way around this is to zip the file first and then send it.

WARNING: Encrypting will slow down the performance of your database.

II. Hide your objects and database window. This is a very simple method of securing objects (tables, forms, reports) from the casual user. Through the Start up properties, you can set your database to not show the database window at startup. You can also select settings that will stop the user from un-hiding the database window. Then if a user gets into the database window they won't see the tables, etc. that would get them into the information.

WARNING: This is not a very secure method. It only stops the casual person from accidently opening these objects. Anyone who know a little about Access will know how to unhide the objects, but it is still good practice to do.

III. Create a database password. As with most Office applications, Access provides the ability to set a database password in order to open it. This again is a simple means of protecting your data from others and limits access to the system to a select few that know the password. In this case, there is only one password. All authorized users must know the password in order to access the system. Although this is a good means of keep unauthorized users out of the system, it doesn't help much when certain authorized users only need some of the data.

WARNING: There are programs out on the market that can break Access passwords very easily. I have a number of them that I use to help people when they forget their own passwords that they set.


IV. Create user sign-on. Access has its own built in user level security. You can create users, provide individual passwords, have the user authenticate when they open the database, and restrict certain users to certain objects. I have always found that this is easy to get started with but not robust enough and have always opted for developing my own security for my databases. In doing so, you can not only restrict certain users to certain objects, but you can also limit the data they see. If Joe is the salesmen for the NY area, then I can limit Joe's view of the customer table to only NY customers.

These are some very simple methods that can help to secure your database. In our next blog post, I'll discuss a few more technical methods that can really lock things down.

-Robby

Top 10 Reasons to use MS Access

Microsoft Access is the most popular database program in the world for a number of reasons. One of the biggest is that non-IT professionals can use it to develop solutions that are cost-effective and that address a wide range of data management issues. Access essentially brings Visual Basic, SQL Server, and Crystal reports all down into one nice package that almost anyone can start using.

Here are the Top 10 reasons why I like MS Access....

10. Great for projects with little or no budget


Solutions can be built quickly and, if done in house, for almost no dollar cost. Many small businesses already own a version of Microsoft Access and just aren't using it. Either in house our through a consultant, applications can be built quickly and at low cost that solve many issues and have an immediate effect on your business.


9. Allows for Rapid Application Development

Building in Access is a great Agile development tool. A non-IT professional can quickly start building an application and will quickly see how Access can address their data challenges. Access is also a great environment for prototyping.

Many databases built in Access solve a specific problem and may be short term solutions. They last for a while and then disappear as the issue is resolved.

Other databases start out to address one issue but evolve to address many other needs. These may eventually outgrow Access but, when its time to upscale from your Access database to a SQL server database, you are well prepared because you have your Access database to serve as a prototype.


8. Easily Integrates with Microsoft Office

One of the best features of working with Microsoft Access is its ability to integrate with other MS Office products. You can get the most out of Office when you let each product do what it was intended to do; MS Word was created to do Word Processing, Excel, for number crunching and graphs, and Powerpoint for presentations. Data can be easily moved between Excel and Access, letting you use the best features of both. Access queries can easily provide a data source for a Word Mail Merge document. The integration of all the Microsoft Office components creates an environment that exceeds the sum of the parts.


7. Great for Data Entry

Through the use of Forms, Access allows for very customized and easy to use forms that are great for data entry. The combination of these allow for the creation of a very user friendly interface to interact with your database. Business rules can easily be built in and through the use of combo boxes and list boxes, data entry can be simplified and uniform.

6. Interface with other Database formats

Access should be described as a tool more than just a program. You can build a solution in Access and yet link to data in other applications such as dBase, Paradox, FoxPro, as well as SQL Server, Oracle, and DB2 through ODBC connections.

You can even link to other Access databases. This ability basically solves any space limitations you may worry about. Tables can be stored in as many databases as you need; each allowing for up to 2 gig of data. Then the tables are linked back together in your front end application. Basically, the only limitation to the size of your database will be the size of your hard drive or file server.


5. Query Designer

The query design tool in Access is excellent. Dragging and dropping fields into your query couldn't be easier. It is laid out nicely and allows even the most novice query writer to begin building useful queries. As you get more experienced, you can easily switch to the SQL view to write more complex queries.

Even experienced SQL writers will prefer to use the query designer just for the ease of it...I know I do.


4. Report Generator

Now that you were able to get your data organized and in one place, you'll want reports. Bigger database systems like Oracle and SQL require a separate system to run reports. Access brings a reporting tool right into the same package. Reports can be built directly against tables or on top of pre-existing queries. Experienced and inexperienced users, through the help of wizards, can build very fancy reports quickly and easily. There are even some preformatted reports to help with labels.


3. No need for a server or extra hardware

Unlike, Oracle or SQL, MS Access does not require the use of a server. The MS Access program and Jet Database Engine run on each machine. This means, instead of a server doing the work, each machine using the database will handle the processing.

Very often, we will build our application to utilize an existing file server. The data will be stored in an Access Database on the file server and each user will have their own version of the program (the Forms, Queries, Reports, etc) on their own desktops. If the company doesn't have an existing file server, we often just dedicate one of the workstations as the server and let the datatables reside there. Another possible solution is investing in a Network Attached Storage device. These devices are small and cheep and can take the place of a file server nicely.


2. Multiple users

Although Access has advantages for the single user, sharing the the database with multiple people all at the same time is where you will really see the biggest impact. MS Access will handle all the record locking and worrying about who made what changes to which records. Unlike working with Excel, you'll never get that annoying message that someone already has your spreadsheet open. Access was designed to be used by many users (by default 255 can use it at one time). Through the use of forms and some coding, security can be set up so that certain users can only view certain data. Of course you can get very complex with security as well and get into all sorts of levels with Read/View only or Read/Write levels.


1. Programming with Macro's and VBA

One of my favorite aspects of Access is that it allows you to program using Macro's or VBA code. When I first started using Access (back in Access 2.0), Macro's gave me my first taste of programming. While building Forms, I discovered how to make buttons work through the use of macro's. I could program buttons to open new Forms, run reports, run queries, etc. They are easy to use and give users a good taste of how the database can be transformed into an application.

As if Macro's weren't enough, Access also gives you the option to program in VBA. Once you become more experienced you will realize how powerful VBA can be and how many more options it gives you. VBA is a version of Visual Basic. It is the code that controls the application and allows all the pieces of your database to work together as one, seamless, application.



These are just a few of the reasons I like using Access. While looking back at these, I can’t help but realize a recurring theme in most of them. Access provides all the tools necessary for a very inexperienced user to create a new database and start building while at the same time providing experienced users a great environment to build more complex systems.

How great is a program that really allows you to grow your skills all within the same application?

Robby

Beyond the Spreadsheet

Lists! Some people love them; others hate them. No matter which category you’re in, lists are a fact of life since we all have stuff we need to keep track of: customers, invoices, things that have happened, things to do. For those of us of a certain age, our first impulse might be a card file (Remember when every desk had a Rolodex?). In today’s computer world, many people may use a word processing program for simple lists but for more complicated records, a spreadsheet such as Microsoft Excel is often the program of choice.

Spreadsheets are great for many purposes, especially those that used to be accomplished on accounting paper and involve lots of mathematical calculations and they have evolved to include many data management features. In addition, the program is often included in the basic software found on many computers (Excel is included in the most basic versions of Microsoft Office). However, the basic structure of spreadsheets make them difficult to manage for more than moderately complex information. This is because a spreadsheet relates data spatially, based on their physical location, not logically, based on the information being stored. Many people have become frustrated as their once managable spreadsheet became more and more complex and they find themselves “impaled” on the grid of columns and rows.

Database programs, such as Microsoft Access, offer a solution to this problem since they are, at their core, designed to store and relate sets of data. Because data are related logically, rather than spatially, a “relational database” can be a very powerful tool for handling very complex information systems. The key to success with a database is to decide at the beginning, what “sets” of information need to be stored.

Let’s say we want to store information on customers and their invoices, using a mental image of multiple card files. We would establish a file with a card for every customer. On that card, we would record the basic information about the customer. We could also record information about each invoice (date, amount, whether it was paid etc.). There would, however, be a physical limit to how many invoices we could record, even if we wrote really, really small and used the back of the card. Instead, we create a separate file with a card for each invoice. In addition to the basic information, we would also record which customer was being invoiced. In this system, there would be no limit to the number of invoices we could record for a customer.

But a database is more than just a place to store information. It is a system for controlling how information is entered (and by whom) that improves accuracy, efficiency and security. It is also a “magic window” into your data. Using our card file example, we could easily retrieve a customer (based on business name, contact name, phone number etc.) and automatically pull information on all related invoices by telling it which customer ID to look for. (We don’t need to tell the system that the invoices are the 13th to 27th cards in the file!)

Although they don’t present the user with a familiar looking, ready made system of columns and rows to store information, a database does provide a more robust system for storing, retrieving and reporting information. Programs such as Microsoft Access include many simple to use wizards that help you get started and basic instruction is available from a variety of sources. The expense of purchasing an additional piece of software and the up-front time spent in setting up the data structure, are more than recouped by gains in efficiency and flexibility for any information processing that is an ongoing part of your business operation.

Joe