Microsoft Access and Reports

While the individual parts of Microsoft Access are all important to a MS Access Database Developer or Consultant, one of the more important aspects to the average user or business person is reports. Reports are an excellent way of generating a aesthetic looking overview of a specific set of data. Ranging from a list of customers or an invoice, they are perhaps one of the more useful tools available to a Database Developer or Consultant.

Although not integral for the database to actually work, a user friendly and well thought out report is what a client or user will use along with forms, since this is the aspect to most be seen by a user it is important that the report is intuitive and not confusing. Thankfully, Microsoft has included adequate tools for Database Developers or Consultants to use to create reports, allowing you to use conditional formatting, varying colour schemes and the ability to insert pictures, and more.

Best Browser for Web Development

Unlike the early 2000’s and late 90’s our choice of best browser has increased a lot, from Firefox to Chrome, from Internet Explorer to Safari and the lesser known Opera and so on. This amount of choice is good for consumers as they get to pick the exact best browser that matches their preference, unfortunately this adds another layer of complexity to a Web Developers job as they must compensate and ensure that their website displays correctly in all the major browsers. Thankfully, the W3C has put specific standards on HTML, CSS and more to help Web Designers. This however can be completely negated when a popular enough browser simply ignores these standards and does its own thing, forcing developers to put specific rules to deal with these browsers. The worst offender of course was old versions of Internet Explorer, although thankfully the developers have adopted the latest standards and Internet Explorer has become a far better browser unfortunately its reputation may be forever tarnished.

All the major browsers offer some sort of developer tools, and all support a variety of extensions to increase this functionality if the default isn’t enough. Since the early 2000’s the web browser competition has increased vastly and all the main browsers compete to offer the latest features and standards to gain an advantage over the competition, this is good for both consumers AND web developers as it the latest web features are excellent. In the end, the developer tools are quite adequate on most browsers, and it comes down to personal preference on what you end up using. Some Web Designers prefer Firefox, some Chrome and some even like Internet Explorer, and diversity of choice is always a good thing.

A little bit of Microsoft Access History

We’ve been talking a lot about Microsoft Access in these blog posts; however we’ve never really delved into some of the interesting history of Microsoft Access. Now, this isn’t required knowledge by Access Database Developers and Consultants to actually use the software, but history is always important and interesting. This is especially true when compared and contrasted to the present.

The very first version of MS Access was released in November of 1992, which is just over 20 years ago this indicative of exactly how useful databases are, 20 years on and they are still an amazing tool. One of the most amazing aspects I believe is that, version 2.0 of MS Access has the minimum specified requirements of “Microsoft Windows v3.1 with 4 MB of RAM required, 6 MB RAM recommended; 8 MB of available hard disk space required, 14 MB hard disk space recommended. The product shipped on seven 1.44 MB diskettes.” This is highly contrasted with the soon to be released MS Office 2013 suite which has minimum system specs requiring at least a one gigahertz or faster CPU, 1GB of RAM and Graphics hardware acceleration and I’m sure it only comes on one DVD.

If this doesn’t show how far we’ve computing has evolved throughout the years, I’m not sure what will. A few other little factoids, I find slightly entertaining are the fact, that the version number of MS Access skipped from 2.0 to 7.0 due to being bundled with the MS Word which was at version 7.0 already. On this note, the developers also skipped the version 13.0 due to the superstition associated with the number.

In the end, the history of MS Access is inconsequential in the grand scheme of things for MS Access Database Developers and Consultants however history is excellent at teaching lessons and shouldn’t be ignored. Of course for a far more comprehensive look at the history of MS Access, the Wikipedia page is always good http://en.wikipedia.org/wiki/Microsoft_Access#History.

Web Safe Fonts and Colours

On the surface fonts must seem quite trivial in terms of web development, but the font type used is far more important than it appears on the surface. The first issue with fonts is ensuring that the user actually has the font that the web developer has selected. In most cases, a web developer will use one of the web-safe fonts which are fonts that are most likely to be on the users system. Initiatives from Microsoft to include the “Arial, Courier New, Times New Roman, Comic Sans, Impact, Georgia, Trebuchet and Verdana” fonts on most computers ensure that pretty much every computer will be able to display them. Although these fonts were selected because they filled certain criteria such as legibility on a screen, however it could be the case that a web designer wants to use a highly stylistic font or one with a certain aesthetic which works better with the site.

In this case, the user might not have that particular font type on their computer, meaning the web designer would include a “font-family” where they refer to several different fonts with a similar aesthetic to the one that they want in the hopes that the user has one of those other fonts. A particular solution to ensure a user could see the text would be to use images to display the font, but then a user cannot select the text and it becomes a lot messier.

Another issue is web colours, back when the web was young, displays were limited to 256 colours meaning that a web developer would have to carefully select which colours to use so as to ensure the maximum amount of users could enjoy the site at its maximum potential as possible. Although not a problem for most users these days, some users still browse as if they were still in the 90’s so to make web pages as accessible to everyone as possible, sometimes it is necessary to sacrifice some design elements for usability.

Microsoft Access Database Backups

As MS Access Database Developers and Consultants would know, one of the most oft-mentioned pieces of advice is database backups, preferably to multiple types of media and multiple locations. Backups are still the best method of retrieving lost data, and for reverting back to old copies of data, although it is inconvenient at times luckily most systems have automated methods of backing up or they are made as simple one or two click operations.

Although most people would never say that backups are useless or aren’t required, a lot of people don’t bother with it. Usually it only takes one or two lost databases, assignments or another piece of work to convert most people to using backups. As good as backing up every now and then is, unless it is done regularly then it can be as useless as no backups, with regular backups you know you won’t lose too much work when something happens.

The most obvious means of losing data is power failure when the database is corrupted, although this is probably the most common way of losing data, there are still plenty of others. Physical damage to the location of the database can cause data to be lost, whether it is fire, theft, broken equipment, floods and a myriad of other reasons why it could break.

Luckily for Microsoft Access Database Developers and Consultants, backing up in MS Access 2010 is a simple task, one only needs to click “File”, then select “Save & Publish”, where the option to create a backup is listed. With backing up as easy as this, Developers and Consultants have no reason to not backup their databases regularly.

Website Security and SQL Injections

One of the most devious ways of infiltrating a website is through SQL Injections, which in general is where un-sanitised user input allows a malicious user to ‘inject’ their own SQL statements into your SQL queries. Of course this is incredibly dangerous as depending on the level of security, a user could potentially delete entire databases, give themselves administrator permissions, and steal data. It can also cause problems due to apostrophes within some people’s names. This makes SQL Injection a very serious concern for any competent Web Developer or Consultant. The site http://www.unixwiz.net/techtips/sql-injection.html has an incredibly interesting walk-through of using SQL Injection to break into a client’s site in a security review of their site.

As the article discusses, SQL Injection won’t work every time and does involve a little bit of guess work when guessing database field names, although important field names generally look similar, the password field will typically be called ‘password’ and the email field will be called something like ‘email’ or ‘mail’. However once all this is figured out, using SQL Injection will lead to be quick results.

Finally, the article discusses the virtues and methods of giving your website security /SQL Database from SQL Injections, advocating techniques like sanitisation of input where you remove all special characters that can be used to perform SQL Injections like apostrophes and so on. It goes on to mention using database permissions and configuring error reporting. Error reporting is especially important as if a malicious attacker is able to see the SQL errors that occur from their SQL Injection attempts it can allow them to easily tailor their later injections to avoid errors. In the end, you could luck out and have no user attempt to use SQL Injection, but this blog has reported that “the observed Web applications suffered on average 71 SQLi attempts an hour.” so in the end it is worth the time of a Web Developer or Consultant to provide website security against SQL Injections.

Microsoft Access Database Security

There are many means to securing your Microsoft Access Database, unless you are leaving that to your Access Database Developer or Consultant who should also be well aware of these methods. There are the immediately obvious means, such as password protecting the database in general. Although that comes with several downsides, including that once the password is provided every element of the database can then be manipulated. So this solution will not do if you want people to use your database, but restrict them from meddling with or seeing sensitive material. However it is a good catch-all method of preventing anyone that you don’t want to use the database from using it.

Inconveniently, security against outside malicious attacks isn’t enough; sometimes you’ll need to protect the database from internal users, whether malicious or benign. A malicious user might use their ability to access the database for stealing data or other nefarious means (They might even delete or modify code), most MS Access Database Developers and Consultants know they have to defend against benign users who may accidently delete data, code or other objects which would ruin most peoples days or even weeks depending on the damage.

The most useful way of securing your database for most businesses would be setting user-level security permissions for various user groups. For example you’d have a group of users that can only view the databases, while another group would be able to edit the data within, and finally an administrator group that could create more objects in the database and make those sorts of changes.

Although there is not enough time to discuss every single security measure available to Developers or Consultants it feels apt to mention one of the most overlooked aspects of computer security in general. Most people seem to think defending against hackers is the most important aspect, but there are equally large security breaches in the physical world, the physical servers must also be protected from attack, theft, accidental damage and natural disasters such as floods, and fires. Some of these methods are obvious, such as securing the servers behind locked doors, within temperature controlled rooms and possibly even offsite. This would also protect against accidental damage, there are a lot of clumsy people in the world and nobody wants to lose their hard work because someone spilt coffee on the server.

Hopefully this shouldn’t be news to a decent Database Developer or Consultant; however it can serve as a good reminder to ensure that database security is a real deal and not something to skip out on.

Microsoft Access and Conditional Formatting

A useful tool in Microsoft Access is conditional formatting, where certain formatting is used when certain conditions are met, like a field containing a particular value or set of values, or when the field has focus. This helps MS Access Developers and Consultants tailor specific elements in reports or forms to a client’s needs, for example you can create a report which uses standard accounting practices like using black text to denote a profit or red text to show a loss.

Although not the only use for conditional formatting, it can also be used to highlight specific fields to bring attention to certain values or fields when required. Overall, conditional formatting is yet another tool provided by Microsoft Access to aid Developers and Consultants in delivering powerful and valuable databases to their clients.

Buttons and Microsoft Access

On this blog, we’ve been talking a lot about the powerful tools available to MS Access Database Developers and Consultants, so far we haven’t talked about buttons yet. The versatility of buttons in Access is amazing and contributes significantly to a developer’s ability to create functional and engaging menus, forms and reports. At their simplest buttons can be used to navigate through all the records in a table when displayed through a form, or they can run macros filled with a several commands. Most users are familiar with buttons, so adding them into menus makes it a lot easier to create functional UI’s.

The button command wizard is great for creating buttons that perform actions, such as opening reports, printing reports and a variety of other tasks. Although advanced database developers and consultants can configure the effects of button through the properties tab and this allows them to do even more with them. Without a functional UI most databases would be clunky and difficult to use for new users and even some intermediate users, and one of the most important aspects of a functional UI is appropriate use of buttons and not something any good MS Access Database Developer or Consultant should forget.

Importing Excel Spread-sheets to Access

As some people may have noticed, comparisons between single tables in Access and Excel spread-sheets have been made several times, both on the Internet and in this blog. This might have even convinced a few people to recreate some of their spread-sheets in Access, luckily for them and Database Developers and Consultants, Microsoft has helpfully included the ability to import a variety of file formats into MS Access with only minimal configuring on the part of the user. This can save both regular people and developer’s lots of time as they or their clients’ needs may change to require a database rather than a spread sheet or similar file.

It’s really simple, just select the “External Data” tab in Microsoft Access, then select the “Excel” button and follow the prompts to import and configure your spread sheets into tables.