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

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

Wednesday, February 26, 2014

Office 2013 Service Pack 1 is out

If you have Microsoft Office 2013, you may notice a service pack coming along real soon.  Having a look at the list of MS Access fixes, it certainly looks worthwhile.

Mary Jo Foley has all the details on this page include the list of fixes.

Also I published a comment on "Microsoft Android" on my managers blog.

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$.

Thursday, January 02, 2014

Friday, November 29, 2013


If you have purchased Smart Access at any stage, I will be sending out links with zipped copies of all the downloads before the end of the year.  These will have the downloads in accdb format as long as it was possible. Send me a note if you want the upgraded files using this contact page. Only use the form if you purchased Smart Access at some stage.

For those of you who have purchased Smart Access from me over the years, thanks. I made just enough money to feel happy about continually investing in and Garry Robinson, Editor.

Tuesday, November 26, 2013

How to make X-Y plots using MS Graph in Access 2007

Ever thought about turning X-Y coordinate data into a grid of results with cells containing the average of all points in the cell.  This article on spatial data will show you that Access can do it. The download in Accdb format with the spatial code is here

Thursday, November 21, 2013

How do people get a barcode on a report ?

Just in case you didn't know, you can add a bar code to a report by adding a Bar Code font.  Then you simply put the numbers/text you want in the report and change the font to the bar code font and hey presto, there is the barcode. 

If you look at the second picture you will see the barcode font in action where the Barcode says Hello Readers. 

Garry Robinson

There was an article on barcodes in Smart Access back in the 90s.