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

Tuesday, March 15, 2016

OK, I have never heard of these subquery commands - Exists, All, Not Exists

Peter Vogel writes A subquery is literally a query within a query. Using subqueries allows you to perform some action using another query as part of your Where clause.

I personally have used subqueries many many times but just about always I use the In clause. Stumbling on this 1997 article, I realise that I may have missed some opportunities along the way. Though when I tried to work out where I would use the Exists and Not Exists clauses in my solutions, I drew a blank :(  


A simple subquery in design mode


Monday, March 14, 2016

Hiding Objects in the Navigation Sidebar Using VBA

In the following article, I show some simple VBA to show and hide objects in the Navigation Sidebar (ye old database window) using code. I also offer up a bit of code to list all the hidden and system objects and even temporary tables if they are still hanging around in your database. Read the article here

If you want to download the office 64 bit vba zip file that accompanies this article, click here




Thursday, March 10, 2016

Hiding Objects in the Database Window

Here is an article on hiding objects in the Navigation Sidebar



Garry Robinson
Access MVP for 10 years

FMS On the Access 2010 KB 3085515 Issue

FMS are covering the issue and if you have their products, read the post.

The Access 2010 KB 3085515 Issue ~ 10 March 2016

Quite a lot of people installed an office update that is causing problems with Access

The story on the bug which mainly effects MDE and ACCDE files and libraries and may require an uncompile of a database is here in the Microsoft Blog

The story on how to uninstall it is here
https://support.microsoft.com/en-us/kb/3085515






Wednesday, March 09, 2016

Monday, March 07, 2016

Database Options can help keep things a little more protected

In these excerpts that I have dug up from chapter three of my book , I discuss some database options that can protect your databases.

Specifically I am covering that little scoundrel, Compact on Close, the good to know edit and save record settings, Open Exclusive and hiding the Most Recently Used list if your paranoia runs to higher levels than mine.

Remember this reading material will only hang around for a week and then I will be dragging out more content from my book for this newsletter. True its not earth shattering but its probably good to contemplate the points made.

Garry Robinson
MVP - Sydney Australia


Monday, February 29, 2016

Woops - Should Have Called Tech Support

This detailed article on the FBI vs Apple legal case had a little side note according to this Bloomberg article.

"Unfortunately, the FBI, without consulting Apple or reviewing its public guidance regarding iOS, changed the iCloud password associated with one of the attacker’s accounts, foreclosing the possibility of the phone initiating an automatic iCloud back-up of its data to a known Wi-Fi network, which could have obviated the need to unlock the phone and thus for the extraordinary order the government now seeks. Had the FBI consulted Apple first, this litigation may not have been necessary."

Woopsie ! 

Just in-case you think Apple is the only good guy around, Microsoft have been battling with the DOJ to stop their request to hand over Irish server emails. An article on that here.

Are You Paying Attention ?

So you like to read my newsletter, now you will be able to read the book I wrote in 2003. Read on.

Back in 2002 I wrote a book on Access protection and security. It sold thousands of copies and I made about 1$ for every hour I put into it. With the release of Access 2007, 50% of the material became defunct. But that was the bad news, I picked up some important clients as a result of that book and the momentum from that publicity, my Smart Access acquisition and my Access MVP nomination has kept me and a few friends in work for a long time.

So when I worked out a way the other day to convert the book content into vb123.com.au, I thought its time for one last little book hurrah.

So every week or two I am going to release a chapter from the book with one caveat. It has to still be relevant. This will restrict me to chapters 1-7 out of a total of 12 chapters.

So here is the link to Chapter 1 {link removed - sign up for newsletter to get the links }, it describes the book and gives a little advice on protecting your Access database. Links to future chapters will be published ONLY in the newsletter and in my twitter feed.

So what is the catch: None really except that you have to read the chapter quickly because when I release Chapter 2, I will take away Chapter 1. The chapters will stay online for a minimum of one week.



Enjoy the read.  Garry





Wednesday, February 17, 2016

Reply in Outlook 2013 like the good old days

I just never could get used to the inline replies in Outlook 2013.  Sure they look good but if you click off message, its hard to know what you were working on.  This page explains how to change back to the old way.