Saturday, November 08, 2014

Office 365 Legal Framework

I recently listed to a talk on Office 365 security and privacy and it added to my belief that Microsoft has data management goals that are business focused from day 1.  Here is a link to the Office 365 Trust information, you be the judge.

Sunday, October 19, 2014

Win a copy of Professional Access 2013 Programming by Teresa, George, Ben and Dagi

Contact me here to be in the running to win 6 copies of Professional Access 2013 Programming in eBook form.

Correct entries will be drawn by using the rand() function in Excel and those with highest random value will win and I will announce the winners here and by email by the middle of November.


Winners names are


Garry Robinson
Access MVP since 2006

Saturday, October 18, 2014

Two Things I Learnt Making My Second Access Web App

Web apps are being closed down in  2018

When making my first web app, I charged at the App like a bull in a china shop and didn’t really pay much attention to the whole App building experience.
With my second App I noticed two important things
Tip 1: Importing Auto-number Related Tables Make Life So Much Easier
Access Web databases love Autonumber fields so much that they will always add one for you. So if you want to save yourself a lot of time, make sure the Primary Key in the database you are converting is Autonumber and the Key in the Related table is Long Integer.  This is really important because the Web Forms Access creates for you relies on these features to set up the Automatic forms. So even if you don’t understand this concept now, be prepared to rewrite the parts of your online database to suit this model before you import. And believe me you will import your database a few times before you get it right.
Tip 2: A Desktop Database with Read-Write Linking Is Essential
The second thing I found I just had to have was a read-write connection to the Office365 tables to change and add data. This is explained in this video
To explain why this is important, consider this simple example. In the online Authors tab at, I was showing Authors that had written for Smart Access but there was no actual article online. This meant viewing that person drew a blank in the related articles list. So to solve this I am going to add Yes/No field called ArticleOnline in the Office365 database as in the picture below.

Now I need to populate that field and because I am an older mouse, I like to follow old paths to solve problems the same way as I am used to. So I open my Read-Write connected database and build a normal update query. In the picture below you can see the query on the right and on the left you see the World Icons in the linked fields. These are ODBC links to the Office 365 database. So as the simple linking of the tables fulfils my Yes/No test, I can run the query.

Now I can make a small change to my Access Web Form so that it filters on the Yes/No field as in the following picture
 If you don't think connecting using linked tables is important, try programming XML packets to transfer data, its 100 times more difficult.
Garry Robinson
Editor of

A Public Site Access Web App You Can Try

NOTE: Public facing website are being phased out

I was looking around my downloads the other day and I found the database that Peter Vogel wrote to manage Smart Access. Looking into it, I thought it could be a good way to further experiment with Access 2013 Web Apps. So I imported the data into a New Web App.
24 hours elapsed time including maybe 3 hours of programming and 6 hours of fiddling with data and here it is.
You do not need any permission to run the app, it is readonly. The purpose of it is to allow you to navigate to articles in the website in different ways than Good Old Search.
Here are some samples starting with the Articles View.
Then there is the Keyword search form which I quite like. This uses a datasheet sub view.
The important thing to remember when using this Web App for the first time is that you can quickly filter the list by entering a search term in the “Filter the List” box.
Enjoy this App and come back later to use it again to search

Saturday, October 11, 2014

Demonstration of the Slider Keyboard on Windows Mobile 8.1

Hello Readers.  With all the hype for iPhone 6, Windows Mobile 8.1 was released without dramas onto my Nokia 1020 phone.  Here is a demonstration of a feature that I have missed since I left my Google phone in the drawer, a slider keyboard entry system.  See the keyboard demonstration on YouTube here

Thursday, October 02, 2014

Windows 10 - the first demo

Windows 7 menus meet Windows 8 live tiles. That's Windows 10 and here is a good 40 minute video.  For ultra geeks, 27 minutes into the talk is a demo of new features in the cmd window (dos window).

Wednesday, September 24, 2014

Improve Your Buttons

Round, curved, shadows, these are the things you can do with Access button in Access 2010.  See samples on this page.

New Office Plans A-Coming

Office Blogs
In October the Office 365 plans will change names and structure and allow a lot more users. If you want to use Access web Apps, you need a plan with Sites - Team Collaboration/ SharePoint.  This means that you will need as a minimum Business Essentials as per my previous advice. Here is a Link to what is included in SharePoint in these plans

Note:  This advice is for programmers who are sitting on the fence wondering if / how they will get into Access Web Apps.

Tuesday, September 09, 2014

Access Web Apps - Cough up 50$ and get on with it

Ok, I know I have been banging on about Access Web Apps and I really haven't told you how to get started. In Office 365 there is currently the offer of a 10 users free trial of Small Business Premium (Soon to be called Business Premium). Here is my tip, don't bother with the free trial, go with a one user licence of Small Business (Soon to be called Business Essentials) and cough up the 5-6$ a month for a year.


  1. Its going to take more than a month before you really get a hang of it unless you are a 24/7 sort of person.
  2. If you get the Small Business Premium 10 user licence, you probably will need to contact a technician to turn it back into a Small Business licence.
  3. There is a chance when switching between the Small Business products that you will lose the SharePoint sub-domain name that you started with and the data that went with it. I nearly did because of my procrastination :(
  1. I did quite a bit of work with adding domain names and setting up Public Facing SharePoint sites. As some of this was hard to get right, I had to call both the technicians at Telstra T-Suite Australia and Microsoft. Both organisations provided fantastic support.
  2. I found it really useful to use a second domain name whilst setting up the website. SharePoint allows you to switch between domains whilst you are working on your public website.
Garry Robinson

Wednesday, September 03, 2014

Win More Copies Of Professional Access 2013 Programming in eBook form

Back in July I gave away a couple of free copies of the really good book called Professional Access 2013 Programming in eBook form. Well I have more copies to win and your challenge is to connect from your place to 5 articles that you like at the new

To be in the running, in Twitter or Facebook or Linked-In or Blogger or a good old fashioned website, add your 5 favourite articles at and add a little description on those articles.  Then send me details on where those links are or ping me with Facebook or Twitter.  Upto 3 people will win, those that don't will be in the draw with those that didn't win in July for yet more copies of Teresa, Ben, George and Dagi's book. No correspondence entered into. Don't enter unless you intend keeping the links.

Examples of links to articles at

The Trouble with Normal by Peter Vogel
Queries Queries by John Veiscas
or maybe even my very first article
Exploring Data Visually with MS Access

Idea: Go for a theme using the great SharePoint search tool at

Happy Linking
Garry Robinson
See my Twitter and Facebook links at the top of every page.

Sunday, August 31, 2014

Stop Press: is now hosted by Office 365

A month ago I decided to see if I could transfer from a traditional ftp hosted site to SharePoint. You know the story, looks like it will be over in a few days but instead it blows out.  Finally most of the heavy lifting is over and the SharePoint version of is live. 300 articles, 150 pdf articles, 1000's of pictures and many links. 

Summary:  Works pretty good on a mobile phone, search is way better and now includes the pdf files and  I am building my skills in SharePoint.

The job was undertaken with the Metalogix Content Matrix Console - Web Edition

Screen capture of on Win Mobile 8

Monday, August 04, 2014

Access Web App technology is important as demonstated in this talk.

In this talk at SharePoint 2014 USA, the very important topic (to SP people) of what is happening to InfoPath and Forms in SharePoint 2013+ featured a lot of Access technology. To be more precise, Access forms were displayed in the great Excel Web Surveys (Available now), List Forms (Later 2014 and displayed in the talk) and there was even a good demo of Access 2013 at the 33rd minute of the talk.  This happened in front of a big audience of over a 1000.  In slide 1, a green tick shows where the Access web form technology is used and in slide 2, you will see where the Access web app is demonstrated in the video. If there are InfoPath users reading this, don't panic, its still being supported for many years to come according to the talk.


Sunday, August 03, 2014

Access Web Apps Home Page at

I have created a page at where I will put most of the links and detail pages on the Access 2013 Web apps technology.  If anyone has a resource page or video they would like to share, post it here

In last weeks competition, Jeff Pye and Robb Wait won copies of Professional Access 2013 Programming ebook.

Saturday, August 02, 2014

Forget the lamp posts - glow in the dark trees


A really great story (unless you don't like tampering with nature) about an invention that has managed to pass the glow in the dark properties of jellyfish into plants.

See an Access Desktop App running normally using cloud data and more

This edition I have some more videos on Access Web Apps from my talk at the Aussie SharePoint conference. 

Video 4 describes what you get when you start a Web App from scratch. Its a bit different to ordinary Access desktop so its worth reviewing the features. Video 5 shows a desktop running with all the data in the cloud.  This totally rocks and speaking of which, its rock solid with SQL Server on Azure and ODBC connections. Video 6 explains how you setup the ODBC so you can connect your desktop with a read/write connection to SQL Server in the cloud.

If you followed last weeks newsletter and watched the videos, you are now up to video 4.  Here is the full deck of Access Web App videos on YouTube.

Tuesday, July 22, 2014

Talk On Access 2013 Web Apps Running at Office 365/

Access Web Apps are being closed down in 2018

Last week I gave a talk at a big Australian SharePoint conference about Access 2013 Web Apps. This was remarkable in itself as no SharePoint person has ever listened to me before. To get tuned up for the presentation, I made some YouTube videos.  You can watch the first three of those Access Web App videos here

Video 1 -  Introduction to Access Web Apps and your humble scribe (me)
Video 2 - This is probably the first movie ever made on an iPad that shows an Access Web App. You would watch this just to get a feel of what a good Web App would look like.  I am not trying to sell the App I wrote, just demonstrate the tool.
Video 3 - Here is my take on setting up a Web App from scratch on  It shows what happens when you use the Table template.

Saturday, July 19, 2014

Monday, July 07, 2014

Making The Grade

Its good to have look at challenging query situations every now and again as most Access problems are solved by queries, not code.  Doug Steele wrote an article on how to classify student marks into Grades A to F.  Have a look and see if you can follow the logic. Even if you can't, if you are a database programmer, you may need to one day.

You will find many challenging articles on queries on the LHS of Doug's article.  Read one query article a week and you will be a much better programmer as a result.

Free Ticket to ShareThePoint conference in Sydney


You need to be able to attend the conference for at least one day. It runs for 2 days at the Hilton in Sydney.

Links to Sydney details here

Garry Robinson
Access MVP 2006-2015

Friday, June 27, 2014

Read this to feel PtrSafe about Office 64 Bit compatibility

If you write Microsoft Access databases with basic VBA code and no fancy Windows calls, you will find your database will run with Office 32 bit and Office 64 bit with no problems. But if you have used fancy windows library calls, you are likely to get this bug

Compile error 
The code in this project must be updated for use on 64-bit Visual Basic VBA7. Please review and update Declare statements and then mark them with the PrtSafe attribute.

This issue is discussed in detail here at Microsoft and Conditional Compilation is discussed here

Better still, here is an article by Garry that explains conditional compile in more detail

Thursday, June 12, 2014

19 mod 5 <<< Does this ring a bell

If I typed

ival = 19 mod 5
msgbox "The answer is " & ival

or what would I get for

 22 mod 10

Read about the mod operator here     Sorry no clues, this is good to work out.

If you head to, choose the Index words Key (see picture), and type Mod, you will find a few articles where Mod is used.

Sydney SharePoint Conference in July

I am speaking at the Sydney ShareThePoint conference on July 15th. The Agenda for the conference is here

My 1hr talk is advertised as follows

Access MVP Garry Robinson now collects billing and to-do items from 5 sub-contractors across 30 projects in using an Access 2013 Web App. It's secure, contractors only see the projects they are assigned to and it runs well in all browsers and even on iPads.

In this session, Garry will address these points:
What have I learnt in the short time that I have been developing the system ?
How would an Access Web App fit into a SharePoint world?
What about Office 365, do Access Web apps have a future in the cloud?
And where does the legacy desktop database fit in ?

Hope to see you there   

Garry Robinson
Microsoft Office Access MVP

Tuesday, June 10, 2014

Use Your Skills as A Database Developer to create a Web/Sharepoint App

Access Web Apps are being closed down in 2018

In my blog post entitled Impress the Head Of the IT Department For the First Time,  I said that I had adopted a new development tool for collecting data in the cloud. That tool is the spanking new 2013 Microsoft Access Web App environment.

Access Web Apps are new and powerful edition to SharePoint 2013. They provide you with a rapid way to generate an interface that runs on PC's, iPads and Android and can be viewed in Firefox, Opera, IE and Chrome. But its what the interface sits on that is most impressive, clean crisp screens that use SharePoint security fronting SQL Server both on-premise and in the cloud. Even the macro environment, much despised in desktop Access, produces SQL Server stored procedures.  But who cares about all that, does it work?   YES.

If you click here, you can see some screens from the Access Web App that we are running to manage task lists and timesheets (for billing) on Office 365.

But as you probably want to find out what Access Web Apps are all about, I strongly recommend this presentation from Jeff Conrad and Chris Usher entitled Anyone can build a SharePoint App with Microsoft Access.  It goes for a full hour so settle back with a beer and some nachos and enjoy.

Happy Garry
Before you get worried that everyone will be developing Access Web Apps, I am of the belief that Access desktop developers with patience and a good knowledge of what Web Apps can do will be very well suited to this "Totally Different Development Environment". The exception to this will be those who expect the same old Access Desktop in the web. That ain't happening any time soon.

I am a happy man, this new environment could well become one of the most productive environments in the cloud just like Access still is on the desktop and on file servers.

Saturday, June 07, 2014

Impress the Head Of the IT Department For the First Time

If you tell the head of IT in a large company that you are thinking of writing an Access Desktop database running on a file server, he/she would probably ban it.  I know that you would probably be successful but the IT manager would probably not be happy. Most of my business in big companies has been with technical managers who haven’t involved the IT department. Please don’t tell them

If you were to tell the same head of IT that you were going to write a database that was secured by SharePoint, hosted in SQL Server, very efficient in how it used SQL Server and that it ran on any HTML5 ready browser, he/she would probably be a happy person.

In a similar vein, if you told a manager of a company that ran its businesses across multiple locations around the country that you could write a database that used SharePoint security, SQL Server and HTML 5. If you then said the database could be running in Office 365 anytime, I think you may have an interested boss.

This brings me to my story. I am now running my company’s important time sheets collections in the cloud.  For the last 7 years, provided us with simple html screens where each contractor that works for me would only see the projects that they were assigned to at that time. Entering timesheets was very easy and could be done from any browser. I downloaded the information from BaseCamphq and produced invoices and management reports in an offline Access database.
We are now doing our data collection in an App that I wrote for the Cloud in 3 weeks part time. I have configured the App to work securely like BasecampHq did. I have also set it up so that all our desktop billing software works the same as before. Can you guess what program I used to build the online HTML5 interface to SQL Server hosted in Office365  ?  Stay tuned  …

This was posted as a youtube video here

Garry Robinson - Manager of a handful of very good Access developers in Australia

Monday, April 28, 2014

Closing a database at a certain time every day

Compacting an Access database is important because it clears out the replaced objects and records and organizes the data according to the primary keys in each table. This can only happen when no one is using the database.

The easiest way to get everyone out of the database is with a macro that runs at a certain time of the day.  You can do this with VBA but if the user doesn't allow VBA to run for security concerns, this won't happen.

and if you want to find out how to easily compact a database, read last weeks post here.

Saturday, April 19, 2014

Compacting a database is now as easy as 123

Two months ago I released a new program called 123 for Microsoft Access. I had some comments and on reflection I decided it was too tricky for the average Joe. So I completely rewrote it to be more Office centric and its now called 123 for Microsoft Office and its a lot easier to use. Think of it as a collection of smart shortcuts for your current Office files and databases.

To illustrate how easy it is to compact a database that someone has open, here are the steps to setup a 123 shortcut and Compact the database.

Click on Select a File

Choose a MDB or ACCDB file

Your 123 shortcut looks like this, the people icon displays if database is IN USE

Click Compact. If the database is IN USE, Compacting will be attempted again in 10 minutes

Once everyone leaves the database, it will be Compacted
The next time you open the 123 program, click Compact and it will do it in one step. If it wasn't successful last time, it will click Compact for you automatically.

Check out and download 123 here   If you can, use Internet Explorer to download the program as its easier.

Garry Robinson
Office Access MVP since 2006

Thursday, March 27, 2014

Access Web Apps - A Good Video Presentation

Access Web Apps are being closed down in 2018

Jeff Conrad and Chris Usher gave a 1 hour and 10 minute presentation on Access Web Apps at the recent SharePoint Conference.   The talk is hosted on the Microsoft Web site.  I would recommend that you throw this up on the screen, grab a drink and a snack and watch it when you have some free time.

Here are my takeaways.

This technology is where most of the new features of Microsoft Access are going.

Access Web apps are a feature of SharePoint and Office 365. Both of these are important to Microsoft.

The engine behind the tables that Access Web uses is SQL Server and SQL Server in Azure. So your data is being stored in a world class database.

The samples of the interfaces that you can create are world class for the internet. I know the we have been building these sort of interfaces in Access on the desktop for a long time but tab controls, sub forms, combo boxes are hard work on the web and Access Web does it pretty easy.

Wave good bye to VBA on the web. It ain't happening any time soon. You need to get into macros, data macros and just learn to go with the great interfaces that Access can produce.

There are some good curly questions starting at the 1hr 5 minute mark.

Here are links to other talks at the SharePoint Conference.

SPC338: The 'how to' guide for selling and managing SharePoint Apps built using Access

SPC335: Rich extensions to SharePoint Apps using Microsoft Access

Here are some screen shots from the first presentation

Tuesday, February 18, 2014

Microsoft Access database management is now as easy as 123

With Windows 8 taking hold, it was time to rewrite the Access Workbench. So I hid in my bunker and ground out a new version with a much simpler interface. The program is called 123

The software is free and live here   

<<< I have changed the link since the newsletter went out >>>

The program makes compacting easier, backups easier and it handles the issues you have when users have your database open.

Notes: Installation is much easier with Internet Explorer. Please submit comments here

Monday, February 03, 2014

The PaperClip Returns - Toshiba Laptop Reset Trick

A Toshiba Satellite laptop that I own shutdown and then wouldn't do anything. In the old days, I would pull out the battery with the power off and that would reset it. In this laptop the battery is built into the frame like an ipad. The solution (after talking to the support guy) was to unplug the laptop and put a safety clip (not pin) into a very small hole in the corner. This reset the computer.  Then I could resume as normal.

Saturday, January 25, 2014

MISSING: The lowdown on VBA Project references

Doug Steele explains why Access applications suddenly stop working, or work on some machines but not others. What this means in a technical sense is: VBA Project references. Read about it here and find the download for the article on References here.  Doug includes quite a few links to other websites on the page as well. has been fully categorised

In these days where search is everything, people tend to forget that categorising is also important for people who don't know exactly what they are searching for. So over the hot summer break (apologies to those suffering severe cold at the moment), I went through and categorised many of the articles that were stored in chronological order.   Here is a link to the Queries category so you can read about queries without joins, iif group by queries and other important topics that you will never find in the Access help.

Thursday, January 23, 2014

The Quality of Your Database Work

In 2005, Peter Vogel wrote in an editorial

"What about all the criteria that we, as developers, use to rate our work? Clean code, good object design, fully normalized database designs, timely delivery, and so on. These are all admirable goals and would be very useful if your work was being judged by other programmers. However, all of these criteria are really just a means to an end and not an end in themselves. That end is your ability to make enhancements to your application and to find and fix bugs in it (should there be any). Those goals are only relevant if your users have enough faith in the quality of your product to have you back again. 

Read the editorial on quality here or all of Peters great editorials here

Infrared Keyboard

This infrared Bluetooth virtual keyboard might be a great after dinner conversation piece because it will work on the dining table.  Not hugely expensive and should hook up to smart phones and pc's and maybe even a TV. Don't forget the usual bugbear ~ "charging" .

Apply The Same Condtional Format Equations Across Multiple Fields

In this short article, I demonstrate how the Access 2010 Ribbon allows you to apply the same conditional Formats across multiple fields.

Thursday, January 16, 2014

My Favourite Conditional Format + Listing Conditional Formats

My favourite conditional format is one we use in our invoicing system that highlights the old invoices that haven't been paid. This shows invoices that are a month overdue with a purple box, invoices that are 6 weeks old with a red box and invoices that are 6 weeks old where we have had a follow up conversation with a yellow box.  See this in figure 1. In figure 2, I illustrate the conditional formatting expressions for the coloured box.

Figure 1 - Highlight mature invoices with Conditional formatting

Figure 2 -Conditional formatting expressions for the coloured box

Multiply conditional formatting 10 times and what do you have ?

As you can see in the figure 2, conditional formatting can get out of hand really quickly and you soon will wish that you could document and program conditional formatted fields in VBA. We found out this week that we couldn't find an elusive piece of code to document the actual expression in the conditional format equation. Finally after much digging, I found the code in the sample database that Rick Dobson submitted for his 2005 Smart Access article on Conditional Formatting.  I have now published that elusive code in this new article on how to list conditional format expressions

and to get into this, you will need to download the Access 2007 sample database here

Tip: To make a colourful highlight box as in figure 1, put the forms index field in second text field and lock it. The conditional format will need to show Red text on a Red background for the Red condition. You can work out the rest.

Monday, January 13, 2014

A Huge 860 Megs for $ 3 grand

I found this old Aussie magazine from 1995 which actually looks a lot like the latest Dick Smith material that gets dropped in the mail box.  In those days I would dial up my special MSN email account that only worked in Microsoft's special sheltered internet. Two years earlier than this I purchased 16 megabytes of memory for 1500$.