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.

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

Microsoft Access 2010 Preview: Ribbon Customization

One of the key criticisms of Access 2007 was the new ribbon. Gone was the familiar command bar, and with it, many thousands of hours work by the Access developers that came to rely on the command bar as the main user menu.

Microsoft has invested in making life a bit easier for the everyday user that wants to customize the ribbon without custom code or the nice (free no less) utility Claton Hendricks created.

As you can see in the screenshot from the Office 2010 Tech Preview, you can create custom ribbon tabs and groups. In the preview, it doesn't appear that you'll be able to change any of the packaged ribbon tabs and groups, though.







Stay tuned for more on Access 2010 Web Form design and other enhancements...

What Access 2010 Web Deployment Will Mean

The world's most popular desktop database is going to the web. That's what is fairly clear now from the Access Team Blog and several other public sources. The actual deployment details are still a fairly close held secret (including yours truly), but what is certain:

1) Microsoft is renewing their commitment to MS Access. You could argue that Access 2007 sets the stage for Access 2010, but most agree Access 2007 wasn't a huge leap forward.

2) MS Access is back in the competitive game. Zoho, OpenOffice, TrackVia, Alpha5 and others have been nibbling at Access' proverbial toes for a some time now. Not with any significant success...yet.

3) Access development will change significantly, for the better. Access 2010 will most assuredly not be a repeat of the Data Access Pages debacle. Even taking web functionality out of the picture, the ability to set data macros like SQL Server triggers will be a huge step forward for any Access solutions that need higher levels of data integrity and validation.

There are more interesting points to make, but Microsoft isn't releasing the details yet, and we don't want to risk violating non-disclosure!

New Microsoft Access 2010 Video

Found an online video with more previews of MS Access 2010 on YouTube.



Note the new "Blank Web Database" in the new database "Available Templates" dialog, and the "Client Forms" in the Create Ribbon Tab. Microsoft Access on the Web?

Quick Microsoft Access Overview

What Is MS Access Used For overview for any of you new to Access databases

Is Microsoft Access Still Relevant?

With a new wave of online "databases" (which are arguably not fully functional databases, but spreadsheets with fantastic Ajax entry screens), I wondered today whether Access remains as relevant as other office productivity tools.

You might argue that Access has always been an under-utilized asset in most companies, with Excel spreadsheets being stretched to the limits to become Access-like with extensive vlookup and hlookup references. Imagine my surprise to learn that Access actually receives more searches on Google generally (click here for the trend graph source)

Note that the blue line is Access, red is Excel, and orange is (not surprisingly) Word.

Access is probably more relevant than ever, if not a bit outdated in it's delivery method (desktop only). The personal micro-databases that are quick to create, inexpensive, and don't require a computer science degree is vital to many businesses in a difficult economy where hiring consultants isn't an option, and efficiency can help you stay in business. From what I've seen so far, Access 2010 will go a long way in making Access even more relevant as a micro-database for the masses.


z69cuhx7f4

Creating Dependent Dropdowns

Here is a great tip and example from the MS Access team on how to create dropdown boxes that drive what a user can select from additional drop downs. It is also sometimes referred to as cascading combo-boxes (since the first choice 'cascades' to the next list of things a user can choose).

Get the template here

Free Access Sample - Web 2.0 Effects in Access

Add cool looking Web 2.0 visual effects into your Microsoft Access database with a few simple steps. You don't have to know AJAX or Java to impress your users with advanced visual effects. Follow the link below for the free download:

OpenGate Library Sample - MS Access Web 2.0 Effects

The first shows you how to dynamically expand and contract a subform to save screen real estate and avoid overwhelming users with information they might not need.

The second example uses a single line of Visual Basic to fade one or more controls in and out with the click of a button or label.

You Learn Something New Every Day...

Need to convert your Macros to Visual Basic? I never imagined Microsoft had a tool like this, but come to find out, it's been in Access since 1997.

http://www22.brinkster.com/accessory/macros/004.shtml