Twitter @ smartaccess

For the last couple of years, I have posted every article that I have placed on vb123.com 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"

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

Jim Hungerford is heading to Peru

Jim Hungerford, the general manager of a company I worked for, left and went to manage a society that improves the lives of 100's of deaf kids. Now he is heading to Peru to climb a big mountain when he has rarely walked to the bus stop. Here is a challenge for you, the readers. If you sponsor Jim and his needy children, I will give away a free copy of the Gold Smart Access Collection 2004-2006.  All you have to do is make a donation and let me know by emailing me. You do not have to tell me how much you donated, 10$ will do.

Jim's trecking web site where you can make the donation is here.

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

Some Sample Magazines and Send Me Your Search Words

I am as guilty as the next guy about putting together a not perfect website. Now when that website consists of 500 articles, improvements take a while. Over the last few months I have done a lot of things to remove the "daggy pages" on the website and I thank Adam Cogan for pushing me along in this endeavour. Here are two new innovations.

1) The page that describes the Smart Access collection that we sell has been simplified. There are 2 full sample editions of the magazine for you to review on this page

2) The Help and Manual search engine that I use on the site is a little slow but its pretty accurate. To assist you guys to find what you need I have setup a search interface using Google that features a number of key search terms. If you have key MS Access word/phrases that you think should be featured on this page, let me know using the email address access@gr-fx.com.

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

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.

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.

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.

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.

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 vb123.com

Windows Excel, Word, Powerpoint on an Ipad2

This ZDnet article shows how Onlive Desktop runs on an Ipad.  There is a discussion on using Dropbox as well, a topic that I will cover here soon

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

 

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

vb123.com has been refurbished

vb123.com has been upgraded.  Its got a new look, all the 500 articles from Smart Access content is front and center on the site. Enjoy it readers, its your Christmas present from me.  

Garry Robinson
Microsoft Access MVP - 2006 till now

Microsoft Office On the Ipad

Office at 10$ on the IPad, read this article on zdnet.