Working with MS Access 2013 Web Apps - The Good

Back in 2009 I joined a group of smart Access users, developers, and technical guide writers in Redmond to preview Access 2010.  The software was supposedly in Alpha phase, but from the look of it, and the way the program managers described most features as locked, I'd say it was closer to a GA preview.  The software felt like a first model year car, not quite the full vision the maker had, and rough around the edges.

I've spent perhaps 20-25 hours all told developing in Access 2010 for the web.  Mostly for internal use, but some for a client.  It does the basic job, but the web forms lack flexibility and finish.  Access 2013 has made some large improvements, the Access team really took a fresh approach.  That is saying something when Access 2010 wasn't even really on the market for more than 2 years.  Overall, the product is much improved for web applications.  In some areas they went backward in terms of functionality, but by improving several key areas, the loss of functionality isn't felt as strongly.

So what are some features of Access 2013 web apps that you'll appreciate

1. Better support for master-detail type forms.  While a database is meant to efficiently store information, that shouldn't come at the sacrifice of usability.  Access 2010 web apps were hard to easily show related information.  It was possible with effort, but Access 2013 makes it effortless.  In fact, it automatically shows related information in areas much like a subform.  And it supports subforms as well just like 2010.
2. More control over security.  You can configure, straight from the Access client, whether to allow read/write connections to the tables, and whether it is limited to just one location or anywhere on the internet (security credentials are still required of course).
3. Modern styling and user interaction.  Access 2010 relied mostly on dropdowns, and styling was mundane.  You had a lot of control over things like font size/color/style, and coloring and conditional formatting of cells and areas on screen.  Access 2013 drops most of that (more on that later).  But while you lose control of the minutae, I expect it will return in the future, and Microsoft incorporated things like auto-complete text areas, elegant form styling, and a limited but passable icon library.
4. Publishing without anger.  Access 2010 publishing was somewhat like trying to start a manual car uphill as a new driver.  Sometimes it would work, sometimes not.  It takes a lot of effort, you don't always know if you're going to do it right, and when it failed, you didn't know what exactly to do to make things right before sliding backward.  Access 2013 did a few things right, despite dropping flexibilty.  (A) There is no more blurred line between desktop and web tables and forms, so you can't get into trouble as you did in 2010, (B) Hitting save = publishing immediately to the web.  No more hitting "synchronize" and crossing your fingers.  It just works.  (C) No more compatibility checker with confusing, misleading messages.  Say that one 20 times over.

There are some things that will bring frustration and cursing, I'll cover those in the next part of this series.

Transitioning from Access to....?

Let me start with two things:  a) This post is mostly for the Access-only "developer" that doesn't do much with other programming, and b) I started my career working with Cobol developers.  In the late 90's.  By then Cobol was, as it still is, a reliable, solid, trustworthy dinosaur.  Days could be spent debating the merits of any programming language or platform, but let's suspend that discussion and look at the parallel with Access.


Access has been around for nearly 20 years now, and like Cobol, evolution has slowed to a crawl.  You might say that Access 2010/2013 is a sign of evolution, but let's be honest.  2010/2013 contains two apps - one for client development, and one for SharePoint form/list design.  They share the same skin, but fundamentally diverge in terms of design decisions and capability.



Microsoft has done a great job with Access web services for SharePoint users, provided people try not to compare the Access web capability with the client capability, and you don't look at Access web services as a solution for enterprise-grade web capabilities.  But what about the "legacy" side of Access, the one hundreds of thousands of databases use?

My answer is, for better or worse, it's time for us to start transitioning.  In the same way that a lot of really bright COBOL programmers I know have done, we have to realize that the opportunity space for MS Access is shrinking.  The need for rapidly developed, business-user friendly database applications will never go away.  But the number of places Access will be used will continue to decline, and what I'd be curious to hear from readers is where you think people will go to solve the problem?

PHP Developers - Charts for PHP without Coding

Let's face it, Microsoft Access is in a bit of decline.  The market is moving increasingly online, and Microsoft Access 2013 is more tailored to light-weight web entry via Share Point than the old days of MS Access.  We'll still be supporting and enhancing our world-class Access products, there's life left yet in Access.

But today we announce ChartBuilder for PHP.  A new application that is easy to deploy, and helps create charts for MySQL databases without any coding!  Just embed the chart.php file in your page(s), and define how you want each chart to look.  Simple as that!

Here's a sample of what ChartBuilder can do in about 30 minutes from deployment to completion.

Access Consulting -

After many years focusing solely on building Access tools, we've decided to extend our offering to Access consulting.  We are taking a limited number of engagements, but please check out the list of services:
OpenGate Access Consulting

Whether you have a an aging database that needs renovation, or a new project of any size, give us a call or drop us an email to see if we can help!

Getting Started with VBA in Access

If you're new to programming, but want to make a start of it, Visual Basic in Access can be a great introduction.  In some ways VB can be easier to grasp and use than even Access macros, with enough time. No doubt there is a bit of a learning curve, but not only can you become more productive, but it can open the door to even more complicated programming (if you want to pursue that path).

Getting Started
The easiest way to get started is by example.  Here's how:

1. Add a new button to your Access form.
2. In the wizard, choose an option to have the button do something like close your form.
3. Once the wizard is complete, double-click the button.
4. In the properties window, select the "Events" tab.
5. Notice the On Click line has "[Event Procedure]" in the field.
6. Click the ellipsis "..." next to the box.
7. The Visual Basic editor will open and you can see the code that Access created for you.

What Does it All Mean?

There is a lot to learn about Visual Basic, but with some simple background you'll know what matters and what doesn't.  In the VB editor, you'll see that your code is surrounded by:

Private Sub CommandButton1_OnClick()

  

End Sub

The "Private Sub" just means the code only applies to the current form, and can't be "seen" outside of the form.  The "CommandButton1" part just refers to the name of the button clicks.  And "_OnClick()" is the event that fires when you click the button.  You'll see that there are lots of events you can take advantage of, like when a user clicks, hovers over, presses a button.  There are tons of events you can fire in Access.  Finally, the "End Sub" just means it's the end of the subroutine (section of code that applies to the "OnClick" event.

So in between the "Sub" and "End Sub" you'll be able to tell Access what you want to do.

Docmd Is the Workhorse of Access VBA
There is one VB command every Access user should learn: Docmd.  Learning just that will take you very far.  For example:

Docmd.OpenForm "YourFormName"

Docmd.OpenForm will open a form you specify.  You can use the Intellisense to understand what Access needs to actually perform the command. And in many cases, will also give you dropdowns with options to choose from.  In the picture, you can see that the View options will let you open the form in Design view, as a datasheet, Normal, etc.

There's much more to learn, but this should get you started.  Google or Bing searches are a real treasure.  If you need to do something in Access, there's a 99.99% chance someone has had the same need, and someone has an answer out there.

New Google Adwords Tools available

We're expanding our product line!  If you, like millions of businesses worldwide, rely on Google Adwords to reach your audience, AdWords Doctor is for you!  Save time and significantly increase your return on advertising by using AdWords Doctor to analyze your AdWords campaigns, adgroups, creatives, and keywords.

We're opening up a free beta for a limited time where you will receive premier support to get AdWords Doctor up and running.  After the beta, we'll be giving beta participants large discounts on the full product.

To learn more about Adwords Doctor and the free beta, click here: New Adwords Tools

Myths about Visualization

Since much of people's work in Access and Excel ultimately leads to trying to visualize your data in some way to understand your business or research better, this post is really insightful:

http://flowingdata.com/2011/09/23/5-misconceptions-about-visualization/

Google Docs - The start of a database offering?

Who knows these days what Google is serious about, versus a passing fancy.  But Google Fusion Tables may just be a new competitor to Zoho and Access 2010 SharePoint Services if they keep adding features to it.

Excel Resources

Of course this is a blog mostly about Excel, but frankly, Excel and Access are like close cousins that have their own special strengths.  Here's a resource that can help with how to choose between using Excel or Word tables, and what is Excel used for.

Making Excel Easier

After years of working with Excel, you start to just "live" with some of it's shortcomings related to usability.  How often have you had to toggle between sheets to see what the value of an important cell is after making a change? How often have you wished you could go straight to a chart without wading through your sheet tabs to find where it is?

Suffer no more!  Give this Microsoft Excel tool a test drive and you'll be hooked....

Publishing Access Dashboards to the Web

Very exciting news!  Have you always felt like your Access data was trapped on the desktop?  We've just completed and released our newest addition to the Dashboard Builder family - Web Edition.  The Web Edition lets you create rich, interactive HTML charts and graphs on the Internet from your Access database!

Imagine seeing your Access data anywhere, any time, on any web-enabled device!  Without installing any special apps on the device.

You can download an evaluation from our site here:
MS Access Dashboards on the Web

Access Query Tips

New article available on Access Query design and use to summarize and analyze your Access data.

What are those gray "MSys..." tables!?!?

Over the years I've talked to users that panic because they see grayed out tables starting with "MSys" in their database. Virus?  Trojan?  Spyware?  An add-in that hasn't uninstalled?

Fear not, it's a normal part of Microsoft Access.  At the very least you can hide them.  And they can even make your work easier in some cases.  Learn more about Microsoft Access System Tables here.

How to Package and Deliver Your Access Database

Many people using Access need to deploy their databases in such a way that they can upgrade their forms/reports/queries/code/macros without disrupting users or losing data.  This article can help:  Delivering Your Access Database - Best Practices

Access Forms - 10 Ways to Improve Your Forms

New article available on 10 ways to create more dynamic user experiences with Access Forms

Moving from Excel to Access

New resource available for anyone that is new to Access and looking for a high-level process to move from Excel to Access.

Microsoft Access reports just got a whole lot easier

We've just launched Report Builder for Microsoft Access, a way to create an Access report in far less time, and make it look much more polished than doing it all by hand!   Thanks to all the dedicated beta testers who gave such excellent feedback!

Here's the direct link:
http://www.opengatesw.net/products/Access-Report-Builder/Report-Builder-for-Access.htm

New Access 2010 Resources from Microsoft

http://technet.microsoft.com/en-us/office/gg406142.aspx

Mainstream Access 2010 Hosting

Microsoft is making Access 2010 web hosting more mainstream:
http://blogs.msdn.com/b/access/archive/2010/10/19/office-365-and-access-web-databases.aspx

While another Access 2010 hosting solution exists already, clearly people look to Microsoft first to find the end-to-end solution.  Whether the pricing will be comparable, that's going to be interesting.

Transitioning your Database

Some time ago I posted about the risk you face in creating a database that becomes essential for your group's daily operations.  By no means should you think of NOT creating or pursuing the database.  It is a double-edged sword: creating an innovative and useful database will increase your value and the success of your group, while it can also add to your workload and cause your management a bit of fear when they think of promoting you or your departure.

Step 1: Set Expectations
Set clear expectations with your management about what your Access database can do functionally, and what it can do with and without you.  It may continue to work perfectly even if you are gone for weeks.  Or it might need daily care and feeding.  Either way, make sure you've communicated what it takes for your database to keep working, and what skill level is needed if you are gone.  Ideally your manager will be an ally, and make certain to facilitate finding a prospective replacement.  It also helps them to understand how much hard work you've put in to saving your group time and money.

Step 2: Find a Replacement
Look for someone in your group to begin sharing the work with.  If your application has been visible to others in your group, you may have no trouble finding someone willing to start taking some of the "hero" spotlight. And you don't need to tell them you're hoping for a promotion, just that you'd like someone to help cover questions and issues if you're out of town, on vacation, or sick.

It's likely that no-one will have your level of expertise in Access.  If so, you can try to convince your manager to fund an online Access class, or a book or two to help cross-train your colleague.

Step 3: The Transition
I've been fortunate to move to new positions several times in my career.  At least three included a departmental Access database that needed someone to take care of when I moved on.  Try setting up a series of short meetings to transition your responsibility.  Breaking your transition into several sessions gives some breathing time for you to remember details you might want to pass on, and the new person can assemble questions and absorb the information for the next session.    You may be tempted to apologize for poor code quality, imperfect architecture, and ineffecient steps.  Rest assured the person you are transitioning to will likely not recognize these facts, and will have an opportunity to improve upon them in due course (if they have the interest!).