Saturday, August 27, 2016

Rollover  Beethoven - Access Web Database Users Beware

Ok I know that there are less people in the world that use Access Web databases than there are people that know that Beethoven's ninth was the feature music in the cult R rated film, Clockwork Orange.

That ridiculous analogy aside, if you have an Access web database, then beware when Microsoft asks you politely if you mind changing servers. If you agree, you may find your  odbc 11 linked tables sending out weird readonly messages.  What has happened is you will still be connected to your old server and that server will be readonly. Meanwhile in the cloud, your new server is working ok and Office 365 cloud data is being stored there. This new data is not visible in your old links. That's the symptoms.

All I can suggest that you do is open up your Access Web Project file in design mode and find those Connection buried under the File menu.  Now reenter your new connections into your odbc 32 links and you will be on the way. I hope.

Meanwhile I feel like I have been done over by , the star of Clockwork trying to work this out. I thought adding a project to my Web database system tonight was only going to take a minute, little did I know. :(

Thursday, August 18, 2016

Combo Boxes in Access are really good but how do you do them in Excel ?

Sorry for a long absence, one of the companies that I work for was taken over and that used up all my gas in the last month or so.  So now I am back, here is a good article on Excel by Susan Harkins that sheds some light on the mysterious world of "Structured Excel programming".

Susan writes... Excel's basic validation is usually adequate, but sometimes you may need an on-the-fly solution. Here are the steps for using VBA to make it happen. Valid data is the cornerstone of any application. Thanks to Excel's Data Validation feature, input values can be tested and accepted or rejected based on criteria you specify. One of the easiest ways to validate data is to restrict users to a list. You can even update that list in one of two ways: You can manually add new items to the source list or you can allow users to enter new values using the data validation control itself. In this article, you'll learn how to update and alphabetize a data validation control's source list using VBA.  Read more on Excel Combo boxes here

Wednesday, August 17, 2016

Windows 10 Anniversary Edition

If you wondered what that giant upgrade to Windows 10 was that locked up your computer for 10-15 minutes, it was the anniversary edition of Windows 10. If you are a windows 7 user, your window for upgrading for free is closing fast. This story is very well told over at ZdNet and you can read about it here

Friday, June 24, 2016

Something a little different - an electric chopper

After you have digested my introductory article on Excel file types and how they work in Dropbox,

here is a story about a new style of helicopter.  If you live on a farm in remote Australia, this could be just the thing as long as you remembered to charge it on your solar panels the day before.  Enjoy your weekend.   Garry

Sunday, June 19, 2016

Excel Online - An Introductory Piece

An article on Excel Online, Dropbox and filetypes published into LinkedIn.  Have a read and like it to improve its visibility in the LinkedIn network.

The purpose of this is set the framework for using Excel files that end up in the cloud

Tuesday, June 14, 2016

BREXIT The Movie

Very soon it is possible you will hear a lot about Britain voting to leave the European Union.  Its possible that the news will become bigger if the stock markets of the world crash.

To be better informed, I recommend the 1 hour show called Brexit the Movie  I watched the whole story and personally if the value of my superannuation investments fall for a few weeks because the British decide Euro bureaucracy is not necessary, I am fine with that.  I especially recommend watching till the end of the section on British fisherman which is about 10 minutes in.  Very sad.

I apologize for a political post but the reporting on this is one sided and this movie highlights the other side.    Garry Robinson

Microsoft purchases Linked In - Garry is set to DiveIn

You have probably heard the News, Microsoft has stumped up 20 billions or thereabouts for Linked In.  It will remain a separate company with the head of Linked In reporting directly to Microsoft boss Satya Nadella.  A news story about this is here  and the Letter to his employees from the CEO of LinkedIn

So what has this got to do with Garry, your editor.  For a long time I was thinking of putting content into Linked-In as I need the extra eyeballs to With Microsoft now well on the way to being the owner of Linked-In, I may as well publish stories into Linked-In.  My initial thoughts will be discussions on Excel and Access with particular emphasis on Excel cloud spreadsheets.

So please connect to me in Linked-In by searching for Garry Robinson.  Then await my stories.

Friday, June 10, 2016

Web databases - once you are on the wave, it may be hard to get off

Over the years I have had discussions with good Access programmers who have said that they can link to a web database from an Access database. It always sounded a little insecure to me and because my website host didn't offer the same "openness", I just forgot about the process.  This article which discusses a popular free unix database MongodB highlights the issue of open ports. The hack in this instance is simple. One guy produced a search engine that looks into open ports. Another guy uses that search engine to get email address in any database.  I guess the second guy looked for anything with an @ in the word.  So be-warned, don't store native email addresses or passwords in a web database and if your web database sounds too good to easy to get to, at least encrypt your important data.

Huge waves battered Sydney over the weekend, this picture was near Coogee.

Tuesday, May 10, 2016

Crystal and Adrian Share A Video on Free Tools for MS Access

Crystal Long and Adrian Bell recorded a video on free tools for Microsoft Access at the Microsoft Channel 9 studios.  You can view it here.   Covers topics such as free templates, tools that Adrian has released for free and more...

Monday, May 09, 2016

An Alternative to the BrowseTo Command

I use Navigation Controls in my software quite a bit. I find them cleaner than Tab Controls and they do have the advantage of automatically shutting down recordsets when you move focus from one navigation button to the next. But I have never actually controlled which button has the focus, I have always left that to the user and just organised the buttons in a sensible order. Of course the day came when I decided to get in Control. This article shows you how.

The BrowseTo Method that I just couldn't get to work

San Francisco ?
Refer to the newsletter for details on this post.

Monday, April 18, 2016

There is gold in them thar phones

I saw this article about Apple recycling and wow, a million old phones means a lot of gold. That led me to think about silver, the best electrical conductor (releases the least heat).  Why aren't they using silver instead of gold. I found this article and from that article, copper and gold are used more often in electrical applications because copper is less expensive and gold has a much higher corrosion resistance. Because silver tarnishes, it is less desirable for high frequencies because the exterior surface is less conductive."

If you have 1000 phones in your drawer, here is an article on extracting it

That's my thought for the day and in case you are wondering, I was rewarded the Access MVP prize again for the 11th time, you are still stuck with me for another 12 months.   Garry Robinson, Sydney NSW.

Tuesday, March 29, 2016

Two More SQL Query Articles that I recovered from the Smart Access Archives

Like most developers, you probably join tables in your queries by dragging a field from one table to a field in another table. While tremendously easy to do, this is just the tip of the proverbial join iceberg. There are at least two other ways of joining tables, and both let you do some neat things. Read the first of Peter Vogels articles here

In the second of the articles Peter discusses how to optimise queries for speed. There is a discussion on something called JETSHOWPLAN in the article.  Head to this page for links on ACCDB related registry keys, (I am guessing that they will work).

Garry Robinson
Access MVP 2006 - March 2016

Thursday, March 24, 2016

Excel 365 Got Some New Functions

Susan Harkins writes for TechRepublic about 6 new functions that Office 365 now has available. These include

The TEXTJOIN() function combines text from multiple ranges with the added flexibility of a specified delimiter.

Use CONCAT() to combine multiple strings or ranges similar to TEXTJOIN(), but without the flexibility of a delimiter or empty argument.

The IFS() function checks one or more conditions and returns a value that corresponds to the first TRUE condition.

SWITCH() MAXIFS() MINIFS follow similar themes.

I bring these up as it is good to know what the successful office 365 products are upto.  Also be very aware that Excel 365 is a different beast to Excel Desktop. VBA programming is the standout biggest 365 omission.

I recommend trying the free versions of IOS or Android Excel just to get a feel for what is happening in that space. It is a guarantee that you will run into someone who converts to Office 365 and needs help.

Monday, March 21, 2016

Access databases, its time to collect your badge and keep things more under control

As I mentioned last month, I would pick my way through my book from 2003 and find content that is still valid to Microsoft Access users today. This week is the chapter on Good Programming Practices.

Sherriff Woody
In a couple of new extractions from my 2003 book, will find that AutoExec macros are a way of managing what happens in your database when it starts up and you will get a few insights into
Error handling, a skill that separates the sheriffs from the cowboys.

Sunday, March 20, 2016

An Access Multi-User Solution in the Cloud at the Click of a Button

Imagine if you could create 5 cloud pc's at the click of a button. On those cloud PC's you have personal C drives and a shared network drive and a installed copy of Office Professional Desktop with MS Access. Then imagine that you could give 5 people an shortcut from a PC anywhere in the world that opened their own pc in the cloud and they entered a username and password and hey presto, that cloud PC was running.

Now on the network drive on the Cloud PC group you transferred one of your great Access solutions  (via dropbox or one drive). Users then would only need to open your Access solution with a normal desktop shortcut on the cloud PC and you would be running in a very short period of time.

After explaining what works (and what doesn't), I then go on to plead to the development gods to consider a one button multi-user cloud environment for Access.

Wednesday, March 16, 2016

SQL - A language crafted in another century and still working today

Peter Vogel wrote "The best thing about being familiar with SQL is that it’s supported on virtually every data storage system in the known universe, even if the underlying storage mechanism isn’t relational. If you create a table in Access but want to transfer it to another database system, you may be stuck. But, if you can create the equivalent SQL command for that table, you can just execute that SQL command on your target database system to re-create your table. "  With that in mind, I dug up three of Peters old SQL articles from the pdf files and put them into They are featured in this post.

In this article from way way back, Peter Vogel starts off one of his series of articles on SQL commands by comparing SQL verses DAO for creating and managing tables and adding indexes to those tables.

In the second article, Peter looks at the SQL Alter Command. The Alter command in Access lets you add and delete columns or constraints from your table

In the third article, Peter discusses a SQL keyword that isn’t universal: DistinctRow. This lead us to the universal, and very important keyword, Distinct. On the way,  the impact of indexes on these types of queries is discussed as are Totals queries. Its an interesting read and you will find it here.

I found these articles back in the really old (1997) Smart Access magazines that you can purchase in full here. One day I will get all the good and relevant articles online but they each take over an hour to convert to the web, so don't hold your breathe.