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.

Create Microsoft Access Forms

We've just released our newest version of Designer for Microsoft Access. Version 3.0 features automatic MS Access forms creation.

After the success of our first product, UI Builder, we found that most Access users never intend to become database architects, and would prefer to just get on with using their database. Thus, we created Designer to make it far easier to build Access database tables and relationships using a plain-English Q&A style wizard. Over and over, Designer customers would tell us, "you're product is great, but how do I create data entry forms that will show all the information in one place." So we went back to work on Designer to solve that problem, too.

The new Designer creates tables and relationships automatically, and then creates forms with a "header" section that displays many records as a list, and then a bottom section that contains related records from other tables. For example, if you have a "Patients" form that displays a list of all patients in your database, the bottom section of the form Designer creates can display related appointments, emergency contacts, prescriptions, visits, and notes.

What Microsoft Access problems are you trying to solve? We love a challenge, especially if many Access users will benefit!

Access Triggers are Here!

Anyone following the Microsoft Access 2010 teasers or participating in the Tech Preview knows by know that Access 2010 will have a new feature called Data Macros. They are very similar to their more robust cousin-database platforms' Table Triggers concept. Read more here:

Access Team Blog: Access 2010 Data Macros