Getting Ready for Microsoft Access 2010

Several pages with overview information about Microsoft Access 2010, including an overview of the new Access Services and Access Services System Requirements

How to Be a Self Taught Access Database Programmer

So you don't plan on making programming a career, but for some reason (need, fun) you want to make your database really sizzle. Maybe your database is your pet project, a guilty pleasure during downtime. Or you hope it will increase your value to your company (and often it can). But the most you've ever done that closely relates to programming is hitting the "Record" button in Excel to record a macro. Let's talk about ways you can learn to do some basic Access programming without taking a hammer to your PC after a few hours.

1) Very Small Bites

Programming can be as challenging as learning a foreign language, and then some. Set some very, very simple goals. Like, "I'm going to make a message box popup that says 'Hello World'." There is good reason why most books you pick up about programming make this the first step. The proverbial "Hello World" is a small, achievable milestone that you can check off as a success. You'll want to have lots of these to keep you going, to offset the frustration when you just can't understand why something doesn't work.

2) Learning by Example

Microsoft Access has been around for over 15 years now. The wealth of examples, samples, and Access templates available on the internet is astonishing. Familiarize yourself with what is out there, and then pick apart a template or example you like. Find out how it ticks, and adopt the useful material for yourself. Unlike writing a college paper, code samples publicly available on the Internet are generally there to be copied. Respect any copyright notices that you may need to include in your code, but otherwise, take it easy on yourself. Don't recreate the wheel when there are so many samples and tips available. Click for a collection of Access tips and resources.

3) Engage the Experts

After you've spent time learning the basics, and you've hit a wall, you'll find an amazing group of people willing to help. The UtterAccess.com message boards are full of experts always willing to help out. A few tips to have a good experience:

a) Always do a search on Google, and search the posts, for any solutions to your problem. The experts are patient, but I've noticed they get (understandably) touchy when a well-known solution to a common problem is brought up by a new user. They prefer to help those that have tried to help themselves (as opposed to posting questions without doing any work to find a solution first).

b) Explain the problem thoroughly. Something like "I'm getting an error in my code when the user clicks a button" is about as helpful as when someone in elementary math would raise their hand to say "I ended up with 9.5 on this problem." The teacher hasn't seen the work, and so has no idea where the problem may have occurred. Similarly with programming, give as much information as possible. For example: "I'm encountering error 999 'Object Does Not Exist' when I tried to open a recordset."

4) Celebrate Successes

An extension of #1 above, make sure you allow yourself little "wins." If you've got a complex set of steps you're trying to accomplish, break them into little pieces that you can check off. If you get very frustrated, sleep on it.

5) It doesn't have to be pretty

If you are learning to program, your first many attempts won't be pretty. And unless you plan to sell your solution, or you are really looking forward to a career in software programming, don't worry about how pretty the code looks. It needs to work, of course. But it needs to work for your purpose. You'll find a lot of purists that may chuckle if you don't use (or know about) class modules instead of normal modules. You may know that your monolithic, 500 line subroutine isn't the optimal way to handle something, but what matters is that it works reliably. If you have time later, you can rework it to remove redundant code, move some parts to separate functions, etc.

6) Enjoy!

Learning to program is like anything else that comes with satisfaction, it is earned through a good deal of challenge and frustration. But in the end, you have tangible (your application) and intangible (the new knowledge you possess) accomplishments you can admire.

Microsoft Access 2010: Publish Access Data/Forms to the Web

Microsoft announces a major new feature in MS Access 2010: the ability to publish Access data and specialized forms to SharePoint. See the video here:

http://ecn.channel9.msdn.com/o9/ch9/3/0/1/0/0/5/TheAccessShow2010Demo_ch9.wmv

Microsoft calls it "Access Services." We've been working with the feature since November 2008 when we visited Redmond for a few days for an alpha-preview. It is a vast improvement over Access 2000/2003 Data Access Pages in terms of usability. The new feature is limited to SharePoint, something that will be a downside for anyone hoping to finally publish Access forms to the web. We've also been participating in the SharePoint Live tech preview, and publishing to SharePoint Live is not supported. But for any local intranet application that requires the scalability of SharePoint, and some nice web forms, Access 2010 is a fantastic leap forward.

There will be an Office Beta 2 in November, which will be publicly available. A great opportunity to try it out. Note that you'll need SharePoint 2010 (now called Office Server). And a word of warning: don't publish any production data in the beta and expect it to migrate it to the full version. We found quite a few issues with publishing data and databases that Microsoft will surely fix when Access 2010 goes public.

Estimating the Complexity of Your MS Access Database

We've just published a new white paper to help Access beginners to help estimate the complexity of a new Microsoft Access database project. The paper offers a scoring system to help decide how complex your database project may be, and the level of experience that is likely required.

Download the paper here, along with other Microsoft Access tips.

Upgrading Windows XP to Windows 7

A little bit off the typical topic, but since we've gone through it several times, thought this might be of service to some. If you don't want to replace your XP machine because it's fairly new and has the horsepower to run Windows 7, here's how to "upgrade" it without reformatting your hard drive:

Upgrading Windows XP to Windows 7

Five Things to Love about Access 2007

With Microsoft Access 2007 hovering at around $89 for a copy of the upgrade version, the price is now one of the smallest considerations when deciding to upgrade. Access 2010 is in the next calendar year, and so you may want to wait to upgrade if you haven't already. But working in Access 2007 quite a bit lately to create a new Microsoft Access Template for employee recruiting, I didn't want some of the less publicized, but incredibly useful features, to go unnoticed. In no particular order:

1. Alternating section backgrounds in forms and reports. Getting that "greenbar" effect where every other row in a form or report had a different background was once the thing of clever coding or Conditional Formatting. Access 2007 has a native "Alternate Back Color" property in the Detail Section properties. You can set it to be dynamic to the user's system color scheme, or specify a color to use.
2. Date picker. Similar to the greenbar effect, you once had to trouble with ActiveX controls to offer users a nice date pick control when they were in a date field. Updating to the current century, the Access team wisely made it an embedded control that automatically pops up when the user enters a date field.
3. Layout view. This enhancement has gotten more press than the prior two, but it can't get enough. I'll admit I staunchly stuck with the "Design view" when Access 2007 came out, shunning the "Layout view" as a silly tool. How wrong I was. The Layout View in Access 2007 will make you so much more efficient creating reports and forms. Neither mode is perfect, but use the Layout view to touch up your reports and forms by seeing what records will actually look like to the user. You'll notice text boxes that need to be wider, mis-aligned controls, and whether you're using space and screen real-estate effectively for your users.
4. Report design. Overall, Microsoft's improvements to the Report Design mode are fantastic and far more intuitive. It's fair to say that 50-70% of the people that use Access would consider themselves novices, and the old report design interface did few favors to help. The Group & Sort, Totals, and Layout view simplify some of the most confusing elements of report design in prior versions of Access.
5. Ribbon. I'll caveat this by saying anyone who has worked with any MS Office product for years is going to hand you a few reams of complaints about the Office Ribbon, and the Access Ribbon in particular. The Ribbon in Access is problematic for designing an application for end users, as it takes away quite a bit of programmatic control developers were accustomed to. But for a beginner, the Ribbon is genius. If you are not a database designer or developer, you need to know what possible tasks and options exist for what you are looking at on screen. And the Ribbon does just that.

A few myths and truths about MS Access Security

It seems at least once a month, I see some trade magazine or blogger snipe that Microsoft Access is the least secure place to store your data, or that Access is a hacker's dream. An article in InformationWeek this month offers some empirical data to combat the myth that MS Access databases are a wide open vault door:



















(source: InformationWeek "Database Servers: Candy For Hackers")

There is no denying that the "Workstation" and "Laptop" categories would largely pertain to Microsoft Access and FileMaker. Nevertheless, database servers clearly hold the largest share of breaches and records stolen.

Truth: Microsoft Access database files can be hacked
Microsoft designed Access to be secure when the appropriate measures are taken. But even taking those measures, there are publicly available utilities to unlock MS Access passwords, and read the raw database files.

Myth: Access databases are easy to hack
This myth is only a myth if you take the right steps to secure your database in the first place, and I won't go into them here. You'll find a wealth of them available online, including this excellent article on MSDN. But if you take the appropriate measures, you'll be safe from everyone except the interesting characters that sit in front of dark terminals search for vulnerabilities and go by monikers like 'fuN1B0yyy'.

Final Thoughts
If you choose to store confidential and sensitive information in an Access database, take the appropriate measures to limit access to the database. That means securing the database file(s), but also locking down the directory, and machine, where the sensitive information exists. But above all, when you hear a well-meaning colleague scoff "you're storing that in Access...you might as well just print it out and pass out copies on the street!!!" you can respond with empirical facts (database servers are more commonly breached) and the steps you've taken to appropriately secure your sensitive data.