Saturday, December 22, 2012

Building software, is it the same as renovating a house?

Over the last year, we have been renovating our house. We have a good builder, we have had to make lots of decisions and we know absolutely nothing about building. Looking back at the process, sure I have lived in houses for 50 years, I actually never paid the slightest bit of attention to what makes up a house. But when the builder rings you says have you got the tiles ready, the tiler arrives on Thursday, you sure have to pay attention. When you look at the newly stripped bathroom and measure up that dream vanity and realise it finishes inches from the toilet, you realise you need to pay attention to what it going on and do some planning. But until that moment, architects plans drift by, bathroom drawings sit on the plans and you really don't pay attention.

Surely this is what our software customers must feel when we write software for them. We have our little meetings, they drift in, sit down and we all chat, we walk out with visions of great user interfaces and they go back to what they were doing yesterday. Then our software arrives and they start to use it and ....

So ask yourself, did they actually tell you what they needed at the first meeting or were they only 80% focussed like I was with our house. You will never know until the software is installed and they start using it. Given that software is rarely as important as fixing a home, its a good bet they will not have focused. Is that their fault, no, its human nature.

With Microsoft Access you have always got to strive for good design and getting the software (read data model) into the hands of the user as quickly as possible. Don't be worried that they will not be excited about an interface with basic buttons and datasheet views of data and quick exports to excel for reports. These are the tools that you have that gets users to focus on your software. Just like me and my house, once the bathroom walls were in place, I could make cardboard cut-outs of vanities and toilets and make sure that things fitted before the carpenter fixed them to the wall.

Have a great festive season.

Garry Robinson
Access MVP since 2006

Wednesday, November 28, 2012

2003 verses 2000 format

I asked this question of Wayne Phillips from
In your website you stated  "When using Access objects (e.g. forms, reports, modules etc.) consider upgrading Access 2000 databases to Access 2002/2003. This is due to a much improved file-system for storing these objects in Access 2002/2003."
I am working on a huge system in a2000 format.  Is it really worth the upgrade to 2003 ?
and Wayne answered

I generally go with 'if it ain't broke don't fix it' :)  But if it is broke, or you're experiencing regular corruption, I definitely would recommend it.  In Access 2000 the forms/reports/vba etc are all stored together in one big compound file called a DocFile, which is then itself split into 4000 byte or so chunks and each stored as records in the MSysAccessObjects table.  This DocFile adds an extra layer of abstraction (and complexity) to the storage of Access-objects which is not needed and degrades performance.  The 2002+ format removes the DocFile complexity and instead stores all the Access-objects data directly as separate records in the MSysAccessStorage table.  Furthermore, in my experience, the 2000 format is more prone to corruption issues.

Kind regards   Wayne

Thursday, October 11, 2012

Recording Steps To Solve a Problem

In Windows 7, a nifty little tool called the Problems Step Recorder was shipped with the Operating system. In otherwords, its on EVERY win7 machine.

What this tool does is record a screen capture everytime the user clicks or types. So its great if your user can go back to the beginning and start creating the problem again.

At the end the user saves a Zip file. In the zip file is a MHT file that is just one long html file. Its the same format as you get when you save a website page from IE.  Click here to see a Zip file recording sample of how to run the Problems Step Recorder

Problem solver output for click 3 - { type problem solver in run box }

Extra Notes
You can edit the files in MS Word
You can run from a command line, samples below. Read more here
You can make comments as you go
You can send as an email after the file is saved
psr.exe [/start |/stop][/output ] [/sc (0|1)] [/maxsc ]
[/sketch (0|1)] [/slides (0|1)] [/gui (o|1)]
[/arcetl (0|1)] [/arcxml (0|1)] [/arcmht (0|1)]
[/stopevent ] [/maxlogsize ] [/recordpid ]

/start :Start Recording. (Outputpath flag SHOULD be specified)
/stop :Stop Recording.
/sc :Capture screenshots for recorded steps.
/maxsc :Maximum number of recent screen captures.
/maxlogsize :Maximum log file size (in MB) before wrapping occurs.
/gui :Display control GUI.
/arcetl :Include raw ETW file in archive output.
/arcxml :Include MHT file in archive output.
/recordpid :Record all actions associated with given PID.
/sketch :Sketch UI if no screenshot was saved.
/slides :Create slide show HTML pages.
/output :Store output of record session in given path.
/stopevent :Event to signal after output files are generated.

PSR Usage Examples:

psr.exe /start /output /sc1 /gui 0 /record
/stopevent /arcetl 1

psr.exe /start /output fullfilepath.xml /gui 0 /recordpid

psr.exe /start /output fullfilepath.xml /gui 0 /sc 1 /maxsc
/maxlogsize /stopevent

psr.exe /stop

1. Output path should include a directory path (e.g. '.\file.xml').
2. Output file can either be a ZIP file or XML file
3. Can't specify /arcxml /arcetl /arcmht /sc etc. if output is not a ZIP file.

I heard about this on the listserver newsgroup run by

Sunday, September 02, 2012

1000 Test Versions All At Once - Now thats different

In this article about Mark Zukerbergs letter to shareholders pre-float, there is a section on the programming methodologies deployed by Facebook. What I like is the "get it out to the users approach as quickly as possible" approach, something I thoroughly agree with. This is taken to a new level at Facebook with upto 1000 different versions of the same feature all being tested at the same time. I closed my Facebook account recently. 

Monday, July 16, 2012

Desktop Services Template

One of the really good things that came out of Access 2010 and 2007 were the database templates. The template that caught my eye was the Desktop Services Template as pictured below.

Unfortunately when you download it, it is in web services format and cannot be readily edited. Then a bit more hunting and I found a post by Jeff Conrad that has the database in client format.  This database makes very good use of the new Navigation Control.

Tuesday, June 19, 2012

Microsoft Surface

Its long, its skinny and thin and has a keyboard and its a tablet.  Its Microsoft Surface and read more here

Lets hope it flys, Ipad IOS is like Fort Knox to find and manipulate files.

I like long and skinny, reading is easier if your eyes dont have to wander too far across the screen. Witness columns in a newspaper.  Take this and imagine that MS shrinks this using the same screen ratio just like Apple Ipad and Iphone.  Then you have a long skinny phone.  At least its different and it may fit good in the hand.

My comment on the Ipad vs Surface article here...

I program in Microsoft Office so an Ipad was not a big thing for me. But I bought one for each of the kids. For them its now a portable YouTube device after the initial rush of garage band and ipad games. Now they play games on PC internet and Xbox and leave the Ipad games for car trips.  But for me the Ipad has been fantastic for reviewing documents and designing my house in a 3D cad program for the Ipad. Its the finger zoom function that totally rocks and when you share files that with Dropbox on the PC, the Ipad becomes an amazingly productive file and picture viewer. But this functionality will be available from day one on the Surface.  I think this new machine will totally rock. Also i read the longer narrower shape of the Surface is the perfect shape for a woman's handbag. This could become the perfect place for computerised interactive shopping lists. Final word USB 2 and 3. Say no more.

Great follow up post at zdnet on the awesome hardware that is Surface

Monday, May 28, 2012

My First Ms Access Job

I reflect in my new managers blog on why it is important to split a database and why a little bit of research can be a good thing when dealing with a programmer. Yes, I draw on Access 2 memories for this little piece.

Saturday, April 07, 2012

A New Blog - the Managers Viewpoint

I always have wondered what a business manager would think reading all the posts that I have written on programming, nothing I guess.. So I decided to write a blog specifically for the manager that is involved in our type of software. My first post is about complex databases and software, rewrite or grind on

Eddie the Eagle

In Australia we play an unstructured brand of football called AFL where you pick the ball up and kick it forward. When the ball comes back to the ground 50m later you can jump and grab the ball and if you hold onto it, it is called a mark. Well last Thursday, one of our smallest players, Eddie Best, took two of the best marks you would ever hope to see. The link to the video of these marks is here.

Tuesday, March 27, 2012

How to find out how many records in multiple tables in one query

In the picture, you can see how I gathered up the total numbers of rows from more than one table.  Note how I use a "one" 1 in the DCount(1,"tableName") to refer to the first field in the query. This is easier than looking up the name of the first field.

Click on picture to enlarge

Wednesday, March 21, 2012

Google Plus

If you happen to be into Google+, and would like to track my Access articles in that environment, use this link You need to be a Google member to see my page.

Thursday, March 15, 2012

A New High Quality Website in 8 hours - you decide

I was bouncing around looking for a online website builder for my youtube posting mad eight year old when I stumbled across Weebly. The fact that it was one of 50th biggest websites in the world caught my eye. So I dived in. In 20 minutes I saw a lot of potential. 8 hours later I had revamped my boring old company website and here is the new home for my business information. If you like that, try the same site in your android phone or iphone, it is optimised for small screens as well. If you want to create your own weebly site, follow this link,

The only caveat for a site like this is it only does what it does. If you want something customised, you are restricted to being able to add some html code and thats about it.

Wednesday, March 14, 2012

AutoIndex Gotcha - Access 2007/2010

The AutoIndex option, which is turned on from the Get Go in your databases, will automatically add Indexes to your tables without you knowing it is happening. This indexing article shows the symptoms and maybe even persuades you to turn off this "feature".

Monday, March 12, 2012

Backward Compatibility between 2010 and 2007

Reently I published a post on Scatterplots with a download accdb database. Somehow that file became 2010 only as discussed in a previous blog article. Now to downgrade the database, I found I had to open 2007 and make a blank database. Goto the 2010 database and open it in 2010. Export each object one by one to the 2007 database. Open the 2007 database in 2007 and it was fixed.  The 2007 version of the database is in the download now. This is way more complex than it needs to be.  Note that I switch Access versions using the Workbench

Monday, March 05, 2012

Indexed Words

Many years ago Microsoft wrote in its How to Write a Help Manual guide, that people were 30% more efficient when they used Indexed words to find the help they needed. Well if you didn't already know, has hundreds of indexed words that you can use to find help topics. The picture below shows that you first click on the Key then start typing the Word and then click on the Word you want. If there is more than one page on the site that has that key Word, a list will be selected as in the figure. For those of you that have tried this before, I recently cleaned out a lot of words the cluttered your choices. Hopefully that will make it work even better.

Indexed words in action at

Working Out The Size Of Objects in your Database

Whenever we are called in to fix a database that is misbehaving, we always head for the old standards of Compact and Repair, decompile and exporting all the objects into a brand new database.  When the dust settles and the database looks way too big for the information that you percieve is inside the database, there is nothing you can do to find out what is taking up all the space.  This page discusses how you can use the two recent save to text posts to work out the size of objects. There is also a link that discusses unnecessary indexes as they too can bloat a database a few percent.

Wednesday, February 29, 2012

A Timeclock Control for Microsoft Access 2007/10

I was involved in a truck weigh bridge entry system for a mining company in Australia. As part of that project, I developed a form used to enter times in an Access database (see below). This form ended up being used at a number of sites of a large Australian trucking company, to clock in the drivers and enter the times each job was begun and completed.    This article explains how to use it.

Sunday, February 26, 2012

Twitter @ smartaccess

For the last couple of years, I have posted every article that I have placed on into twitter. You can find me by searching for  twitter smartaccess.  Every article I post on twitter is one line of text and a link.  Maybe that will suit your lifestyle more than the wordier posts in my newsletter.

"Feel free to retweet my tweets"

Friday, February 24, 2012

Setting Up a Scatterplot in Access

Over the years a few people have shown in interest in the Scatterplots using Microsoft Graph.  Here is a simple page that shows one in action on an Access form..

Wednesday, February 22, 2012

Editorial - Out with the old and in with new - most of the time

The most important trait I like to see in my consultants is that they question what they are being asked to do. In many instances, we will tell the client that making those software changes is not in their best interest. Whilst we might kick ourselves later when we realise that we have lost some work, a week or two later the client thinks about it and comes back with a different approach or they mention that they like our honesty :-- vindication. The reason that I bring this up is because Peter Vogel wrote about this topic in one of his editorials back at the end of the last century. This is what he wrote..

Old Lamps for New

The title of this column comes from the story of Aladdin in "The Thousand Nights and One." At the point in the story where this phrase crops up, the evil wizard has come through town offering to trade brand new lamps for dirty old ones. Of course, what he's really after is Aladdin's magic lamp. The wizard does get Aladdin's lamp, which leads to much anguish and confusion and, of course, a more interesting story.

We often get to repeat this story in our business. The vendor comes into town offering to swap their brand-new shiny technology for our worn out, dirty, obsolete technology. And, of course, we do.

To switch metaphors in mid-stream, this is very different from the way a doctor works. A doctor will tell you, when prescribing medicine, what the possible complications are. Every medical practitioner realizes that each new cure comes with its own set of problems. The usual problems are unfortunate side effects, interactions with other medications, allergies, and a subset of patients who don't respond to the new therapy.

We seem to repeat the Aladdin story in our business. New technologies are presented as being free from problems. Each new software miracle is supposed to eliminate our backlog, increase our productivity, prevent bugs in our programs, clear up your acne, and get me a date on Saturday night. The reality is more like the doctor's story: Some things get better, other things get worse. More importantly, some things can't be done at all under the new regime. Lately, I've been thinking more about the side effects of new technology than I have in the past.

I remember installing a new Access system to replace an existing paper-based purchasing system. I had a lot of fun explaining all the benefits of the new system. One of the users, though, pointed out that they were giving up a significant ability by losing the paper-based system. One of the things that this group did frequently was pull out the file drawer and flip through the file folders, reviewing the orders due for the coming week. This was a simple thing to do and gave them a feeling for what was going to show up on the receiving dock in the next few days. I guess that, given the amount of time they'd had the paper system, I shouldn't be surprised that they used it very well.

No new technology ever supplies all of the features of the one it supplants. Users moving to Access from some other database are distressed to find that they can't add records to a table anywhere at all -- records must be added at the end of the table. These users had developed "legacy knowledge" around using their old toolset. A certain amount of training is required to "untrain" the users from their old expectations. After a while, they'll take it for granted that records are added at the end of a table.

Systems departments also have "legacy knowledge." If you have a systems department that's been using a particular toolset for a while, they'll be very good at using it. They'll understand all the error messages ("Oh, yeah, that message means that when you assigned the object variable in a previous line that you left out the Set command."). They'll know what does and doesn't work ("Well, the manual says you can do that -- but you can't."). And they'll know what you can do with the tool that isn't in the manual ("Let me show you a neat trick").

More importantly, no one in the department will be writing a totally new program. Instead, programs will be pulled together, at least in part, from bits and pieces of other programs. Sometimes those bits will be purely virtual ("Oh yeah, you write that routine like this") or just used as a starting place ("Take the order inventory program and turn it into"). While this is hardly code reuse, there's a lot of efficiency in that "legacy knowledge."

I think that abandoning our "legacy knowledge" is one of the major side effects of adopting a new technology. So far, for me, the benefits have outweighed the costs, so maybe it's just because I'm getting old (just got my first set of bifocals) that I'm thinking about this more. But, like good doctors, we need to consider the side effects and costs associated with any treatment.

by Peter Vogel - Smart Access April 1998

Tuesday, February 07, 2012

Drag and drop in Access

Many years ago, Microsoft wrote an article on how to drag and drop in Access 95, 97, 2000 and 2002. Then Doug Steele improved on that in an article for Smart Access in 2004. I have just tested the code in Access 2007 format and it works fine. If you like a bit of a coding challenge, the Access 2007 download and the samples are available here.

In the following diagram, you will see what happens when you select multiple entries from a List box and drag it to a Text box. Its pretty neat really. Well done Doug.

Figure1. Drag and drop from a list box to a text box

Thursday, February 02, 2012

Office 365

Here is a pretty good review from the crew at Office Watch on Office 365.  It will clue you in on how to test Office 365.

Sunday, January 29, 2012

Ipads - Good for ordering chinese food

Back in the 90s, many of the engineers and geologists in the mining company that I worked for hated mainframes and loved pcs. We had good word processing and spreadsheets on the mainframe so this wasnt rational thought in the view of the IT department that I worked for. Nevertheless, we eventually gave in and let them have their PCs and the users went from hating us to loving us. They also started to mention that they like the mainframe (it was actually a Vax Mini computer or 2) a lot more than they used to. I suspect this phenomenon is just starting to occur between Tablets and PCs.

This article from september last year discusses this topic and as us PC developers are now the new IT department to tablet users, we need to get a handle on what is happening with our tablet users. Eventually when tablet users have had their fun, we can pull them back to less fragmented solutions.

Tuesday, January 24, 2012

The sun maybe getting ready for a cold phase

One of my geology buddies, Ian Levy, found the rather cold Australian summer was getting him down. So he wrote a paper on global temperature trends. Unfortunately for all those global meltdown enthusiasts, he put together a thesis that I concur with, Man does effect global temperatures but there are much bigger forces at play.
Ian writes "The soon‐to‐be released mean global surface temperature results for calendar year 2011 will be more important than most years for those who believe that the sun dominates our climate to such an extent that human‐induced carbon dioxide is only a minor influence on our global climate."  Read the full article called 2011 – A Watershed Year for Global Temperature Trends? here

Here is an alternative paper discussing the same cooling trend

Bottom line for Aussies is dump the tax on carbon.

Monday, January 23, 2012

Moving Files Using DropBox And Processing Them Later

This article shows how I move files to a remote pc using Dropbox and then rename them on the remote server using Logmein and an Acccess database.  Its mainly a pictorial but it  only involves files and folders and no Internet uploads and downloads.  So I like it. Here is the article

Saving All Tables To Comma Delimited Text Files

Before I started programming using Access 2, I was involved with a database of geological data that cost $50 million to put together. Another company bought the project, did nothing with the data for five years, and in the end couldn't read the backup tapes. When we were asked to help, we managed to recover the text data backups from our tapes, and these were used to build a reasonable copy of the database. We also recovered the database files from tapes, but the format was proprietary, and the software that could read the proprietary format was long gone. This is why I occasionally export database tables to text files. I dont trust files that that cannot be read with a text editor.

This story shows you how to export all the tables in an Access database, linked or standard Access format, to text files that you can read with Excel or a text editor. It then shows you how to read them back into a blank MS Access database.

Sunday, January 22, 2012

Save All Objects To Text

The article from last months newsletter on Save all Objects to Text had a wrong link to the article (and another one that worked).  Here is where the article is at

Tuesday, January 10, 2012

Dynamic Access 2007 and 2010 Reports - Get more from your database

This article explains the dynamic report view that came with Access 2007 and shows how you can add hyperlinks to your reports to open other reports and forms. The article is written for managers as it is not a complex topic. There is also a section on how to highlight data on your report using conditional formatting using colour in a special way.

See how hyperlinks and colour can be added to reports

Thursday, January 05, 2012

Backup and recover queries, forms, reports and modules to/from text

A Microsoft Access database is a complex object that is subjected to much stress in network environments and by programming. As a result you need backups of your work. In this article I will show you the

Application.SaveAsText  and Application.LoadFromText methods

to save and then recover queries, forms, reports, macros and modules. To do this you will export all objects to text files in a sub folder using code that I will provide you.  Then you copy and paste some vba code that will be autogenerated and use that to populate an empty database with all your newly exported objects.

This whole process can also help you diagnose and sometimes help you recover an already corrupted database.

It will take you less than a quarter of an hour once you get the hang of it. Read the article here

A Happy New Year to all of you.

Garry Robinson
MS Access MVP 2006 - 2012