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

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