Tuesday, November 26, 2013

How to make X-Y plots using MS Graph in Access 2007

Ever thought about turning X-Y coordinate data into a grid of results with cells containing the average of all points in the cell.  This article on spatial data will show you that Access can do it. The download in Accdb format with the spatial code is here

Thursday, November 21, 2013

How do people get a barcode on a report ?

Just in case you didn't know, you can add a bar code to a report by adding a Bar Code font.  Then you simply put the numbers/text you want in the report and change the font to the bar code font and hey presto, there is the barcode. 

If you look at the second picture you will see the barcode font in action where the Barcode says Hello Readers. 

Garry Robinson

There was an article on barcodes in Smart Access back in the 90s.

Wednesday, November 20, 2013

J Street Access Relinker 2 now available

If you have an Access Front-End ~ Back-end database configuration, you may grow tired of using the built in MS Access relinking tool. If so, you may want to check out the J Street Access Relinker free download, supports 64-bit Access and encrypted BE databases. Armen Stein, Access MVP.

Tuesday, November 12, 2013

A challenge - Finding the last value in query

Peter Henreid writes

     Until recently, I have never been able to get the LAST function to work within a GroupBy and I need this a lot.  It returns random results, it seems no matter how the primary key is structured.  I always have to use a tri-level query because the sub-queries are too slow.  Yesterday, I just decided there must be a solution to it somewhere so I googled probably for the 10th time over the years – same articles, use tri-level query (one level to find the record that contains the max, next level to retrieve the desired field value, then join this query into the main query… a pain), but then in the very small print of one post, without explanation, I found this link.  As far as I can tell so far, it works and is just darn clever (not all that intuitive unless you really think about it).


And here is the second article that I ever wrote on consolidation queries for a bit more background on group by queries.  Garry Robinson

Tuesday, October 29, 2013

App-ocalypse Now

Music content, gamers and movie directors, its time to sit up. If you remember the fact that MS Office is installed on most business pcs and Xbox and PlayStation games at $30+ a copy sit in most homes and both have been paid for, the charts in this article are well worth the visit. Read the cold hard facts about the growth of Apps here.  The comments are also worth a view as well.

Monday, October 21, 2013

Great Looking Sub Reports and Multi Column Reports

Rich Gorvin put together a great looking set of Access report samples in 2001 that still look great in 2007 format (because nothing has changed).  Might be worth reviewing if your complex reports don't look so crash hot. Read the article here and view sample here.

Saturday, October 19, 2013

Gantt Charts Explained

Back in 2001, Doug Den Hoed wrote about how to build a Gantt chart in Access and Excel.  Here is where you can find the link to the Gantt article

Sunday, September 29, 2013

Filter By Colors

Microsoft Excel has an interesting data filter that works on the colors of Cells.  Here it is in action.  If your data is just a little fuzzy (ie its hard to code), you may just wish to hand the end user an Excel worksheet and ask them to color it up so you can do things with the filtered results. Its certainly not something that would be too easy to do in Access.

Excel has a Color Filter

Microsoft has an article on color filters here

Thursday, August 08, 2013

Complex Tab Order - Tip

When you have finished most of the work on an Access form, you need to fix up the Tab Order or people will jump around fields in your form. Usually you would do this with the Tab Order wizard or use Auto Tab. If you have too much of a scrambled order, view the Other Tab in the Properties of your field(s). enterthe correct tab order number into the Tab Index property. Access moves all the fields from that index number further down the order.  So now you can move around the form typing 1,2,3,4, etc. manually till you get to the last field.  Much quicker than the Tab Order wizard.

Tuesday, August 06, 2013

Emails - Everyone has a (different) system

I love discussing how people manage their emails, its clearly a huge topic with just about everyone. Weirdly though its not a topic that seems to warrant training in companies. Sure they have their spam systems and filtering systems but they never seem to suggest a uniform way to approach email.

Techniques that people use are
- never file, just leave them in the inbox
- move emails to other email folders
- move emails to physical folders (this one happens at places where IT departments clean out old emails automatically)
- one person opens every new email and leaves it open, she doesn't leave until she has actioned every one (she gets a lot so its a lot of open windows) and closed them

Here are a list of techniques that are worth thinking about. 

To add to that, I would suggest
- making some email rules to automatically move news emails into another folder so they don't clog up the inbox
- making sure that your mobile and tablet can be used to file your emails (move folder)
- turning emails into Tasks with dates and Calendar items so that you act on them at the correct time.

Garry Robinson

Thursday, July 25, 2013

Windows 8 Is Pretty Good

Sitting on a comfy couch or a bed with an Ipad for a browser is a lot more relaxing than a Win 7 laptop and a hell of a lot more fun. I have been doing it for a year or three already.  But if your laptop is equipped with a touch screen, its a whole different story because now you have gestures at your fingertips and a lot more real-estate to see things.

In the first picture, I am running the Win8 version of IE and I pull down the list of open IE windows. This is nice.  In the second screen the internet web address window is really handy with a list of recent sites that you have visited. In the third picture I am using the great Win 8 reader app. It shows news like a newspaper with narrow columns for easy reading.

So in a nutshell, next time you go looking for a computer or a browser device, give a touch screen Windows 8 pc a lot of thought. I did and I am happy I made a good choice even with a 2hr battery life and a fan whirring away in the background.

Figure 1 - pull down to see open IE tabs


Figure 2 - Web Address bar is very useful
Figure 3 - Windows 8 Reader looks like a real newspaper

Tuesday, July 02, 2013

I Love Tempvars

I have posted on this before but as I keep going back to this page all the time to make sure I have the syntax correct, I guess its ok to mention it again.  Tempvars were introduced in 2007 to support SharePoint. You are allowed about 250 in your database application and they are rock solid. When compared to Global Variables which can be lost if the code crashes, tempvars are good gear.

Saturday, June 29, 2013

Popup Form as an alternative to the MsgBox

In this new article at vb123.com, you can download a form and some code to displays messages on the side rather than in your face. Read the Popup Form article here

Tuesday, June 04, 2013

3D Printing - Quite an Exciting Topic

Imagine having a van with a sophisticate 3d printer. You go into a house. You measure up the space under the stairs. You head to the van and you print out shelves and drawers to fit the storage space and screws to bind it all together.

This video shows that 3D printers are going to be immensely valuable in space and probably everywhere else as well.

Monday, June 03, 2013

Thursday, May 23, 2013

2000, 2003, 2007, 2010 Tables Are Stored Identically

I took a database with only tables and indexes in Access 2000 format that had 400 tables. Compacted it. Converted it to 2003 format. Converted it to 2007 accdb using Access 2010.  All 3 databases were exactly the same size.

Conclusion: Nothing actually changed in Access data storage in a decade through 4 versions.

Garry Robinson Access MVP 2006-2013

Friday, May 10, 2013

Watch Out ~ Query Behaviour of Control Break keys

At some stage in the last ten years, the behaviour of what the Control Break keys did to queries changed. Before the Ctrl-Break key sequence used to stop the query when it had finished its action. Now the way it works depends on how big the transaction is.

If you press Control Break it stops the query running.  This has two different behaviours
Small Queries) If the query can be managed in memory, the query is NOT committed and your data does not change.
Big Queries) If the query does 10,000s of lines of updates and Access cannot handle the query in memory, it stops the query and all actions taken are committed. That is they are NOT ROLLED back.

For big queries, this is a possible disaster because you really do not know the state of your data. 
You Have A Big Query When You Get this prompt
There isn't Enough disk space or memory to undo the data changes this action query is about to make
So Is This A Problem ?
If this only happened when you pressed Control Break, it wouldn't matter because it is unlikely your users are going to do this. What does matter is that your user turns off their computer (in frustration because the query is taking so long) or finds some other way to interrupt the process. We have even had instances where switching to Excel triggers the Control Break behaviour. Then you have to decide, does this make my database incorrect.
Solution :(
I don't have a great solution for you apart from breaking up the query into smaller updates so it only effects smaller number of records.  That way if the process is stopped mid stream, you know that the data is in its original state.
Another clunky way to avoid the problem is to inform your users with a message box or popup form that this query takes a long time so that they don't become impatient. 

The vb123 story on control-break keys is here

Garry Robinson
Microsoft Access MVP since 2006


Thursday, April 25, 2013

My Windows 8 Phone

When it comes to mobile phones, I am not up with the times at all. For the last year I have fiddled around with a couple of cheap Google phones and they were ok. When I added the Swype keyboard entry system, those phones became pretty good. But it lacked one thing, my Calender and Contacts were hard to sync. I missed a meeting or two and enough was enough.

So when my mobile plan came up, I decided I had to stop being a Windows 8 doubter and hopefully get proper Outlook Exchange Synchronisation. I also didn't have pockets big enough for the new Samsung Android.4G phones (that is trouser pockets, not $$$ pockets). So I purchased a HTC 4G phone.


I totally love the very customisable main screen of the Windows 8 phone as shown in pictures 1 and 2. It makes the icons on Google and IOS look like a boring Desktop. In figure 1, I see all my emails, messages, and things I need to know at a glance. When I slide the screen up, I can see HTML pages and other apps.

My Outlook Exchange account now syncs beautifully. I now can sort out my email mess in a browser, in Outlook on a desktop and on my phone and it instantly changes in all the others. This is what a phone is meant to do.

What is missing is some apps and the totally slick Swype keyboard interface. Please Microsoft, pay Nuance to add it to Win 8 mobiles.

I now can see what Microsoft are trying to achieve with the UI of Windows 8, before I was only excited with the way it looks. And the phone fits very comfortably in my pocket.  All in all, the upgrade has been a success and here are the screen dumps

Screen 1: What I see on the home screen. 5 in Messaging means I have 5 unread SMS messages. At the top of the Date tile is the next Calendars task. Screen 2 is still the main screen which you get to by sliding the screen up. The white tiles are links to HTML pages in IE.

And the bottom of the main page is here. The stocks app updates at regular intervals.

Garry Robinson:
Access MVP and a not so clever mobile ph user.

Saturday, February 16, 2013

Access 2013 is one of the Pay as You Go carrots

From this article at ZdNet, the following quote says a lot about the future of Access in the Office suite.  "With an Office 365 Home Premium subscription, you get those programs (Word, Excel, Powerpoint and OneNote) and Outlook, Publisher, and Access.

My thoughts on this "Though I haven't read the fine print, this means that Microsoft need to keep including Access in the Premium line-up because the licence entitles users to free upgrades as long as they keep paying. So there you go dear readers, Microsoft have to keep Access going... As Excel and Word and Outlook work well in the cloud, its a good bet that Access will get the resources to fly to the cloud in the not to distant future. 

Garry Robinson - Access MVP from 2007 till now.

Friday, February 15, 2013

Wednesday, February 13, 2013

Access 2013 - Close to A Revolution according to the Scottsman

In this MS article that I mentioned in a recent post, Access aims to be the "easy to use database tool for the web". For anyone who has ever headed out into this world, you will realise this was always going to be hard. As this is early days in a new tool, the best place to look is the book writers as they can be a little impartial and certainly have to test the product well. So when I saw I post by Andy Couch, author of a few books and way smarter than me, I thought I would share his early insights.

Book Links:  Access 2013 Inside and Out  by Jeff Conrad (Andy is an editor)

Microsoft® Access® 2013 Plain & Simple by Andy Couch

Microsoft® Office Professional 2013 Step by Step by a bunch of people http://shop.oreilly.com/product/0790145365163.do

From Andy...

Hi All,

Like most of you skilled Access users, what I really want to see in Office 2013 is a cracking offering from MS for Access. For too long we have been wandering in the wilderness. All of you have wanted a browser based experience, and it has not come to pass. Until now.
I have said this so many times is we needed a solid platform for the internet before we added the WOW factors. After working with the beta and now the live version of 2013, I think it is difficult to dispute that we now have the platform.

I have just spent all of today going though one of Jeff Conrads Chapters on constructing views (which is basically a form in the browser). What Jeff has show me is that these views go much further than you would imagine in an online programming world. This excitement is something that I haven't experienced for a 15 years. When I was a Paradox Developer and got my hands on Access 2.0, and I saw just how great that product was. Then with Access 97 when OLE Automation came of age, I thought wow. When the Ribbon came along I thought well it is nice, but not a revolution. Other than that, Access has just been good old Access.

Now I have pleasantly had my third Access Revelation. The work that has gone into the View Design Tool is just awesome. Not only have the Team managed to preserve so much of the standard productivity of good old Access, but they have added features that actually make the UI surpass Desktop Access!

For a simple example, when you drag and drop a field onto a form, the design surface moves all the other controls out of the way to make space for when you drop down or move an existing control. But that is just one of a number of very sexy new features. Now we wait while Microsoft posts some worthwhile examples, great help guides and videos and irons out the kinks in Office 365 to see if these revelations actually become a revolution. In the meantime, why not pre-order one of these books to whet your appetite"

Andy Couch

Monday, February 11, 2013

Surface Pro = MacBook Air

 FYI: A Surface Pro has very similar usable disk space to the MacBook Air according to ZDnet.  Especially if you move the recovery space to a USB stick.

For more pesky questions to the MS team about Surface pro, read this page or just read an early ZDnet review

FYI 2: People who use Macs, Ipads and Google devices don't seem to develop custom software and just like suffering with what they have been given. Good businesses should continually improve their business practises, standing still or downloading the odd app is unlikely to liberate the information that individuals collect.

Monday, February 04, 2013

Access 2013 - Wots in the Box

Access 2013 looks like it can be summed up by the following words  - SharePoint, SQL Server, Macros, Azure, Office 365 (this may not be ready) in an environment called MS Access Web Apps

So if you are looking for the upside, my guess is it may be an environment where Access programmers can generate solutions that run in more worldly places than our conventional files server solutions. I believe we had better hope that all those big companies with SharePoint find this is a great idea and then they start looking for people with Access skills to build these apps. Only grandfather time will reveal if this is going to come about.

Remember when installing a new version of MS Access to test, always install with the additional options and do not remove your old version of Office. Generally I like to only add Access and leave Excel and Word at the lower levels.

Note:  Access was included in the Office 365 Home Premium pack, something people can rent for 15$ a month or thereabouts. That's a positive sign.


Tuesday, January 29, 2013

Bit of a laugh on a rainy day

If you have the time, here is a show I would have loved to have been at. The writer sees the funny side of the fancy dress.  Link here

Tuesday, January 15, 2013

Learn how to switch primary keys in Access

When I showed a programming friend about indexes in Access the other day, I realised that this may be a topic that many programmers know very little about. Then after changing about 30 indexes in a database becuase of the Autonumber bug, I thought I had better write up some notes

See all the steps to change indexes in Access here

Friday, January 04, 2013

The AutoNumber "goes crazy" fix

There are posts everywhere about Autonumbers misbehaving in Access 2007 and Access 2010. Most post end up pointing to a solution on Allen Brownes great website

Unfortunately there are problems with that solution (and if I am wrong about that sorry).

1) It doesn't work in Access 2010 but seems to work.
2) It fixes the random number seed but eventually the problem returns.

There is only one solution and its a manual fix.

Open the table in design view. Find the auto-number field.  Make it the primary key. Look at the sort on the primary key. It must be Ascending. 

Do not remove the Primary key from the autonumber key, no matter how you want that table to behave.

What I think is going on

At some stage, someone in Microsoft decided that when a database is compacted, they could look at the very last record in the table and then look at the autonumber field and save that number as the seed.  Then when the next record is added, it looks at the seed, adds one and then updates the seed.  All fine if the table is sorted correctly.  I think the bug crept in because Microsoft templates all have autonumbers as the primary key in each table. Therefore they wouldn't have noticed the bug.  For people who are getting an error like "Autonumber already exists", its because the seed is lower than the highest autonumber in the database and eventually it hits a record that already exists.

I hope I am wrong, this is totally unnecessary bug that should be fixed in the next service pack. It has caused problems in at least 3 of the databases we are maintaining.

We have this bug in Access 2000 databases but we are running them with Access 2010 now.

I do not want anyone to lecture me about primary key design or autonumbers but if you know anything about house paint colours and kitchen design, happy to chat.

Here is an article that shows you how to change (primary key) indexes

Garry Robinson
Access MVP 2006-2013

Wednesday, January 02, 2013

Happy New Year

Hello vb123.com followers

This year promises us a great comet display, and probably for many of us, our first taste of Windows 8 and Office 15. 

Should be interesting

Have a great year

Garry Robinson