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

Using D-Lookup function

Access 2007,2003,2002,2000,97

One of our readers recently asked a question. He was recording meter readings on specific dates in an Access table. In a report, he needed to run a calculation that multiplied the meter reading by the rate in effect for that particular day. He contacted us questioning the "correct" way to build this into his database.

The simplest option was to store the rate in the same table as the readings. Instead of having just "Date, Meter Reading" in the table, he would have "Date, Meter Reading, Rate". Then in his query or report, he could just multiply the two values.

This approach does have some drawbacks. One, it requires the user to know the current rate and it requires unneeded data entry. It is also leaves your data open to errors and inconsistencies, as it requires each user to enter the rates correctly. Another drawback, perhaps not so likely in this case, is that you can't easily change the rate value. If you found out that you had entered the wrong rate on a particular date, you'd have to go change it for ALL meter readings on that date.

The second approach is to store the rate in a separate table. Being a database programmer, this is the approach I would use. It requires building a separate table, lets say "T_Rate" which would include a few fields "Date_Active, Date_InActive, Rate". When running your reports, you would have to go retrieve the rate for the time period that includes your meter reading date.

There are a number of advantages in this approach. One, it requires less data entry by the user and cuts back on the possibility that the user enters the wrong rate. It also ensures that all users are using the same rate for a particular day. If you realize you got the rate wrong, you only have to change it in one place.

This approach does pose one major problem...how do I get my query to retrieve the correct rate. The problem is that you can't just join your meter readings table to the rate table because there is no key field to link on (unless you enter a new reading for every day of the year even if it didn't change). This is a great use for the D-Lookup function.

In your query, you can build in a field that uses the D-lookup function to retrieve your correct rate for a specific day. In simple terms, the function will take your meter reading date, go to the T_Rate table, find the correct rate, and return it to your query.

The syntax for the D-Lookup is:

DLookup("[Field Name]", "Table Name", "Criteria" )

In our example, it would look like

DLookup("[Rate]", "T_Rate", "#" & [MeterDate] & “# Between [Date_Active] and [Date_InActive]")

The hardest part about using DLookup is getting the criteria code correct but once you do, it works very well in many situations.

Robby

Spice up Your Access Reports

Access 2007,2003,2002,2000,97

One nice feature of Access Reports is the availability of separate format properties for the Page Header/Footer, Report Header/Footer and the Detail section. If you add Groupings to the report, Group Header/Footers also become available. Each level has it’s own properties and you can format the sections to be different from the others. But what about individual records within the detail section?

As you are probably aware, the detail section repeats itself for every record in the report using whatever property settings you have specified in the report design view. However, there may be times when you wish to vary the format on a record-by-record basis based on the specific values in that record. This can be done with just a little bit of code, either as a macro instruction or a VBA expression.

For example, imagine a report that shows all upcoming campus events, their maximum capacity, and their current number of reservations. You would like to highlight the events that have reached their maximum capacity by having the event name print in red instead of black. You can accomplish this through OnFormat property of the detail section.

“A Format event occurs for each section in a report, but before Microsoft Access formats the section for previewing or printing.”

Using our example above, I’ve created a Report named Report1. This report has three fields in the detail section. These fields are txtMaximum, txtReservation, and txtEvent.

1.Open Report1 in design view.
2.Right Click on the Detail section and select Properties.
3.In the Report Detail properties box, select the Events tab.
4.In the "On Format" event of the events tab, click on the ellipsis (...) button to the right and choose Macro Builder. Then click OK. When prompted, name the macro, FormatMacro.
5.Make sure you have a column named “Condition”. If you do not see this, click on the View Menu and then click on Conditions.
6.In the first row, enter a Condition “[txtReservation]>=[txtMaximum]” and an Action of SetValue. SetValue has two Arguments; Item and Expression. Set the Item Argument to [Reports]![Report1]![txtEvent].[ForeColor] and set the Expression Argument to 125. This will change the font color to Red if the condition is true. Unfortunately you do need to know the number of the color are you are looking to use. On tip for figuring out the number is to set the color from the report design view using the color pallet on the toolbar. Then check the properties of the control to see what number it was set too. After a while you will begin to remember the colors.


7.In the second row enter an ellipsis (…) in the condition line and enter StopMacro in the Action column. This tells the macro to exit the macro if, in fact, the first condition is true.
8.In the third row leave the condition blank and enter SetValue in the action column. Set the Item Argument to [Reports]![Report1]![txtEvent].[ForeColor] and set the Expression Argument to 0. This will change the font color to black if the first condition is not met.
9.Save your Macro and run your report. The Event name should be red if the reservations numbers are equal to or greater than the maximum.
This can also be done using a Visual Basic expression. I prefer using the VBA code because it allows greater control and more options. If you choose to use an expression instead of a macro, you will need write an IF statement. The IF statement is a very powerful tool and if you are just getting started, using it will really make you feel like a programmer.The IF statement syntax is IF (something) THEN (do something) ELSE (do something else) END IF.

Follow steps 1-4 the same as above, but in step five click on Code Builder instead of Macro Builder. Once the code window opens type the following IF statement.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [txtReservation]>=[txtMaximum] then
Me.txtEvent.ForeColor = vbRed
Else
Me.txtEvent.ForeColor= vbBlack
End if
End Sub

There are a few advantages to using the VBA code instead of macros. One is that you can use vbRed and vbBlack instead of having to figure out the color number. As you get more comfortable with the IF statement and VBA, you will be able to branch off and use ELSE IF and/or Select CASE to utilize multiple conditions and colors.

Good Luck!

What can a customized database solution do for you?

A database provides a “home” for all your business’ information. If you are like many busy people, you have lots of important information stored in a variety of ways: multiple spreadsheets, documents, handwritten lists and sticky notes. Finding what you need, when you need it, can be a problem. Often the same information is recorded in more than one place. How can you be sure you are looking at the most current and accurate data? A well designed database relies on the old adage “a place for everything and everything in its place”. (Think of how your mother thought your room should look!) Not only does this allow you to quickly and easily retrieve any information you need but, by linking the different “data entities” (e.g. customers, job orders, invoices, payments etc.), you get exactly what you need for the job at hand.

However, a database system is more than just a repository for data. It is a tool for efficiently viewing and managing information and, since business run on information, for effectively managing your business processes. By providing a structured set of user-friendly windows, a database system:

• standardizes and speeds data entry through the use of list boxes and drop down menus that can be dynamically filtered,
• assures that data entry is complete and accurate according to your specifications,
• maintains consistent practices and procedures,
• and allows multi-tasking and easy navigation through the system via customized toolbars and linkages.

Do you ever have to stop what you are doing to look something up? Perhaps a product code or a tax rate for a specific location? Built-in directories can put needed information at your fingertips. Frequently used reference lists can appear as pop-up windows, or drop-down menus throughout the system, allowing you to select items with a mouse click. Large directories can often be easily linked to your database from a source file.

Of course, recording data is only half of the process. Getting information out, when and how you need it, is the other part of the equation. Routine reports and operating documents (e.g. work orders, daily schedules etc.) can be generated on-demand with the click of a button. In addition, ad-hoc reports can be generated based on a variety of selection criteria and output formats. And for those of you who want to use your data in other applications (e.g. a mail merge or spreadsheet), you can select the data you need and export it Word, Excel or other programs.

Finally, a database system helps protect your information. While defense against “hackers” and other outside threats is primarily the domain of firewalls and anti-virus software, a database adds an additional layer of security, specific to the management of your data. Access to the database and to specific system functions can be password restricted and a user’s ability to view, change, add and/or delete information can be specified for each function in the database. In addition, the database can track user activity to document, among other things, when data was changed and by which user.

In summary, your business can “do more, more easily.” A well designed database facilitates your existing functions, allows you to go beyond what is possible with a manual system and grows with your company. Let InfoCore be your partner in making this a reality for your business!

Joe Buonocore

Keep your data "your" data

InfoCore's mission is to help you manage your data, keeping it well organized and easily accessible. But what about keeping your data safe and making sure that your data stays YOUR data and isn't either lost or even worse, stolen. The loss of data could have a huge impact on your company. Here are some quick tips that even the least techy person could do to make their data safe. 1. Make sure you back up your data.

There are two things to keep in mind when backing up your data. One is that you want to do it at a regular schedule. The second is that you would like your backups stored at a different location. Once such company, DataDepositBox makes instant online backups whenever your files change. Instead of scheduling your backups once at night, this service actively monitor the directories that you set and makes backups of the files that change as they change. Simply set it and forget it. You'll never have to worry about your backup again. The best part is, that you can retrieve these files from any computer with internet access and the files are stored office site at a secure location.

2. Install a firewall

Although this does sound scary, its really very easy to do these days. Most routers come with a firewall built in. Make sure that your cable or DSL modem is connected to an access point or a wireless router and not directly to your computer.

3. Update your anti-virus software

Make sure you pay for updates and select your virus software to automatically check for updates. Remember, those creating virus, ad-ware, mal-ware, and the such aren't taking breaks and neither can you. Installing your anti-virus software once and then not updating it just wont do these days. Make sure your anti-virus software comes with some kind of spy-ware/ad-ware scanning as well. These spy-ware program can be even more detrimentally than a virus as it most likely will leak your data instead of just destroying it.

4. Don't let your data just get up and walk away.

One of the most common types of data theft is from stolen laptops. Laptops are becoming more and more common and its scary how much data you probably keep on your laptop. After all, you need that data everywhere you go. If your laptop is stolen, that data is in the hands of someone else. One good feature is making sure your laptop is set up with a user login. It won't stop a good thief, but it will make your data more secure from just anyone happening upon your computer and taking a look at your data. When setting up a login and password, make sure your password is secure enough. Use a combination of letters and numbers and don't use your birth date. The use of the word "password" as your password also isn't as tricky as you might think!

Keeping your data securely "your" data is a constant task. By following these basic steps you will have a good start at protecting yourself against system crashes, corrupted files, or computer crimes.