Wednesday, April 27, 2016

Office 365 Plans For Access Web Apps

If you want to create an Access Web App, or AWA, you need two components:
1)      The MS Access Application, version 2013 or 2016
2)      An Office 365 Account which includes Access Services
Of course, if you work in a large organization which has deployed SharePoint 2013 or 2016 internally, you can use the Access Services provisioned on it (assuming that has been done). Many of us don't have that option. We can, however, get a cheap O365 account for our purposes. And that’s what I am going to talk about today.
Obviously, to create an ACCESS database or web app, you need MS Access. I think most people get that point without a lot of effort. Further, it’s pretty easy to understand that you need either the 2013 or 2016 version to support AWA’s. We’re used to versioning in software applications.  
It's the second component that gets murky, which Office 365 Account do you need and why?

Business Plans

Here’s the page for  Office 365 Business Plans. Microsoft’s website listing the basic plan options for Business. It used to be called “Small Business Plans”, it’s now just “Business Plans”, as opposed to "Enterprise Plans" or "Personal Plans".

Enterprise Plans

There’s also a page for Office 365 Enterprise Plans. I won’t go there today, but if you need the services and products offered in any of those plans, the higher costs for them are worthwhile.

Detailed Comparison of Plans

 Here's a page with detailed comparisons of the plans.

What Do You Get in an Office 365 Plan?

So, the first thing we see for Office 365 Business Plans is that two of the three plans include the “Standard” Office applications, which means Word, Excel, PowerPoint, Outlook and OneNote. None of them include the Access application itself. This will be the 2016 version of those applications; I believe you can opt for the 2013 version, but I also don’t understand why anyone would do that.
The absence of MS Access means, however, means you have to obtain a license for MS Access, the application, elsewhere. Regardless of which Business Plan you choose, you don’t get the Access Application.


Getting Access Services

The other component we’re looking for, Access Services, on the other hand, IS included in all three of these plans.
Good News! If you have a licensed copy of MS Access 2013 or MS Access 2016, all you need is the lowest cost plan Office 365 Business Essentials at $6.00 per month, or $5.00 per month if you buy an annual license.
This is important information for anyone looking to move their Access databases “into the cloud”. For $60.00 a year (annually) or $72.00 a year (monthly), you can have any number of Access Web Apps on the web. In my opinion, that is a very good deal.
Pass it on.

Wednesday, April 20, 2016

Where Do YOU Shop for Furniture?

The other day I was reading posts on www.UtterAccess and thinking about what kinds of questions people ask. It occurred to me that some people look for database design assistance as if they are Ikea shoppers, some like Ethan Allen shoppers, and some like Home Depot shoppers.

The Ikea shoppers always want a database template. They’re willing to do a minor amount of assembly, but they crave pre-packaged solutions that someone else put all the hard work into creating, packaging and delivering.

The Ethan Allen shoppers want a high-end, finished database that they can have delivered to their home or office. They’re not interested in learning much about it, only that it is delivered with a minimum of hassle on their part. They’re willing to pay the cost which goes with that product.

The Home Depot shoppers are looking for an associate in the warehouse store who can help them pick out materials, along with few tools, and maybe conduct a training session on Saturday morning, but they expect to do the hard work themselves. They know the final product is going to be a bit rough around the edges, but that’s a good trade-off for saving a lot of money.

One problem, of course, is when shoppers expect to pay Ikea or Home Depot prices for Ethan Allen products. That’s a bit frustrating, as a matter of fact. I can’t say that I really blame them, I suppose. If you could get a $3,000 sofa for $198, wouldn’t you take it?

Another problem is when shoppers want to pick up an Ikea bookcase that exactly fits that odd-shaped corner of their living room. Well, chances are high that anything you get from Ikea is going to have standard dimensions, aimed at 99% of their customers, and not at your odd-ball space. If you put your mind to it, one of those out-of-the-box bookshelves can be modified to work for you. You just need to be willing to put in the work to do it.

And the third problem is that Home Depot shoppers tend to run into frequent difficulties that keep them returning to the store for more parts, more tools and more tutorials. There’s a lot more hand-holding involved.

Actually, now that I think about it, none of these are really problems, with a capital P. They’re just different ways to think about what it means to be a support person in the wonderful world of online forums.



Friday, April 8, 2016

What Are People Looking For?

My website,, has been active for years. I offer a lot of free, fully functional, sample Access databases and code examples. Recently, I was reviewing statistics on what people look for when finding me, and what they download when they get there.
As you'd guess, "Free Access Database" is a popular search term, usually accompanied with a specific version or category. Students seem to be popular. Lots of school staff and teachers out there looking for a way to track their classes or schools.
However, the most popular download, by a narrow margin, is "contacts". The version I have available is mostly aimed at tracking simple, client/contact related information, along with meetings and phone calls with those contacts.
Currently, there are two versions. The newest one is an accdb designed with Access 2013. It ought to run in Access 2010 and even 2007, although I haven't checked the latter recently. The other is the venerable mdb originally designed with Access 2003. I debated pulling the mdb out of the download, but after some recent conversations on, I decided to let it ride. Lots of people seem to be sticking with the older versions still, even Access 97 despite it's being two decades old.
In second place is an older version of my project and work tracking tool. I actually built it for my own use and have made many modifications to it over the years. The version I use every day is now modified to run under Access 2013/2016, with a custom Ribbon. It connects to SQL Azure tables now. That allows me to carry a copy of the Front End on my laptop and update work hours from any location without having to worry about resynching Access BEs.
Actually, there are three versions of Working Tracking, one for Access 2007, one for 2010 and newer, and one for 2003 (the mdb format). Interestingly enough, the 2007 version has proven slightly more popular over the years and still is in the most recent reporting period.
I'm mulling over the implications of this history of contacts and work tracking.
What do you think?

Wednesday, March 30, 2016

One Size Fits All, Or All For One. NOT!

One of the most valuable aspects of Access Database development is that it is almost entirely custom.
That means nearly every new Access application is different in some non-trivial way from every one that preceded it--ever.

Need a database to track family addresses and phone numbers? Great. You'll find templates all over for that. But no two will be exactly alike and that's a good thing, IMO.

However, for a lot of new Access developers, that turns out to be a stumbling block to getting off the ground. If all you want to do is copy out a template and go to work, it's almost never possible to do so. Naming conventions, at the very least, are those chosen by the template designer. They may or may not be the same as those used in your organization.

And that’s just the trivial part.

You want to track one or more phone numbers for each contact. No problem, that’s a job for a related table of Phone Numbers. But what if the template maker decided every contact can have three Phone Numbers (Home, Work and Mobile)? That’s a limitation you’ll have to accept as a compromise. Or, you can modify the template.

At some point, such trade-offs and compromises become more of a hindrance to getting on with the task than simply starting out from scratch with your own design. And the more complex the business process, the more likely it will be that any template you find won’t stretch to fit it.

All of that can be summed up in the saying which is the title of this post. One Size Does Not Fit All.

You’ve been warned.

Wednesday, March 16, 2016

It's all about the data.

That's so obvious it seems silly to even comment on it in a page that's all about database design, doesn't it? But, every day, while addressing Access questions on my favorite Access forum, Utter Access, I run across questions can only be answered by looking at the specific data involved, not at the code used to manipulate it.

A recent example might help explain what I’m talking about. I’ve rephrased the question so as to avoid making it too easy to identify the source.

“My query raises a division by zero error. The query includes two calculated fields. The data is from three subqueries. The SQL for the final is shown below. How do I avoid the division by zero issue?”
Not picking on anyone, but the basic mathematician in me says “this ain’t a database question, it’s a math question. You avoid division by zero by not including zeros in the divisor.” But to the questioner, that simply hadn’t occurred, I guess. He or she was looking for “an Access solution” to the math problem.

There are, of course, two answers to this.

First, if records in the underlying tables have either Nulls or zeroes in one of the fields going into the calculation, then those values have to be resolved before you even START writing queries against that data. Exclude those records from the selection before you try to do any math on the remaining records.

There is, also in that sense, “an Access solution” to the question, "How do you handle Nulls and Zeroes in SQL so that they don’t blow up calculations?" The answers, of course, involve functions to convert Nulls and zeroes, as needed, when they appear. But that’s not the point of today’s comments.

Second, there is an even earlier, more fundamental, question to be addressed. If you are doing math (and a division by zero  error definitely signals that math is being attempted), then you have to decide whether Null is valid for the data at hand, and whether Zero is valid for the data at hand. If so, then why do you then want to do math on either of those values, knowing that your calculation is not valid under any circumstances?

For example, if you want to calculate the average number of days between the date an order is placed and the date it is shipped, you have to decide, right at the beginning, if you want to try to include orders without a ship date. Those orders are still being processed and not yet shipped. I submit the answer to that one is obvious. You must exclude them because it’s only meaningful to ask about average processing days for orders that were actually processed and shipped. So, by the time you write that SQL with a division in it, there are no nulls to fret over.

Another example would have to do with calculating error rates in a manufacturing process. Let’s say you want to calculate a percentage of errors detected in Quality Control versus errors reported by customers after products are delivered. Unfortunately, if the calculation is QA Errors divided by Customer Reported Errors, it’s entirely possible (and one would have to hope, likely) to have one or more errors found in QA and Zero reported by customers for a particular product. See what’s going to happen there? Yup, a divide by Zero error.

The answer to that one is equally obvious, I think. You don’t do the arithmetic that way in the first place. You come up with a more appropriate, mathematically valid, way to calculate this metric. And how you do that is as much a business rule as it is a math problem. Maybe the next approach might be, well, okay, lets SUM the QA and Customer Reported errors first and then divide the QA errors by that total to get a ratio of QA errors. Uh uh! If there are no errors of either sort, then you’re still dividing by Zero.

At this point, I will step in and acknowledge that there is an Access way to handle it, but you can only get there by understanding the math—and the business rule—behind it.

It's all about understanding your data first. After that, it's all just code.