Tony's Microsoft Access Blog

A new hot fix for Access 2007 is now available

A new hot fix for Access 2007 is now available

But I shouldn't have needed to tell you this on my blog because you already read this on the official Microsoft Access blog, right?

Why Do So Many Programmers Use One Character Index Variables?

Why Do So Many Programmers Use One Character Index Variables has a very good point for those of us of a certain age accompanied by greying hair, bifocals and ...   And yes I did learn Fortran as a teenager at the local college. 

Although it was actually Watfiv, a student version of Fortran.  How did Watfiv get it's name?  Well, the first student version of the Fortran compiler was created by Waterloo University and thus called Watfor.  Clearly the next version must be five so Watfiv it was named.

I must admit that just a week or four ago I used i as an index variable myself in a loop of some sort.

Microsoft's Fargo campus mocks Redmond's snow

Microsoft's Fargo campus mocks Redmond's snow - A highly amusing mock emergency alert.

I should add that in the last few weeks we've had a few days of wind chill temperatures of -40 as I walk to the coffee shop.  And at those temps it only takes about 15 seconds of removing a mitt, pulling out the car keys, inserting them into ignition and starting the vehicle to get painful fingers.  Not quite frost bitten but pretty close.

Typically -30 with the wind chill in the last few weeks though which is more reasonable. A bit of an unusual cold spell.

A very interesting solution for Code 39 barcode

Jim at a client found the following a very interesting solution for 3 of 9 bar code which was done as VBA code by James Mercantile. Barcode Generator for Code 3 of 9, Code 39, and Mil-spec Logmars.   The VBA code reads the size of the control and creates white and dark lines accordingly.   The advantage is that unlike with some free fonts you can have the control as tall as desired so it's much easier for the folks on the floor to hit the bar code especially from a distance.  

While searching for the original source of the above VBA Code and it's author I also came across Code 39 barcode encoding and Code 39 Barcode FAQ and Tutorial.  More techie stuff for the technical explanation of how the 3 of 9 bar code works.

The Using bar codes within a Microsoft Access application has been updated.

To James Mercantile thanks.  A very interesting solution.

The case of the form which took 30 seconds to open

The client had two transaction file forms which took 30 seconds to open while a similar form took 3 or 4 seconds to open. I went through many of the items on my Microsoft Access Performance FAQ  page and none made a significant difference.

The list of items Included "Subdatasheet Name property set to [Auto] should be [None]. " so I'm forced to conclude that while that tip may make a difference when using datasheet view it isn't helpful when using forms.

There are 740K records in the largest two tables of the app. The MDB is about 250 Mb in size.

To simulate their environment I placed the BE on a spare older laptop . I also opened the BE in another instance of Access as performance significantly decreases with the second user into the database. I was then able to get performance times to approximate the client reports.

Note that just opening the form wirelessly caused performance to decrease by a factor of four or five. Although Access frequently crashed. Note that I'm running the 54 Mbps wireless networking version so this number quite surprised me. I'm thinking that the encryption might be the bottle neck there.  And is the bottleneck at the consumer grade WAP that I own or in the wireless networking device or the CPU doing the decryption?  

In this case the problem was in the form load event somewhere as I determined by putting debug.print at beginning and end of form load. For example:

Debug.Print "BuildAndApplyFilter - " & strFilter & vbTab & Now

Note that you must put the debug.print at the very end of the routines you want to test as well. Otherwise you don't realize that the last line in the routine is the one that's causing the slowdown. <sigh>  Ironically that line was  commented a year or three ago "'Note: This sub also calls BuildAndApplyFilter - a small performance hit." Yeah, well, it's a small performance hit when the BE is local. A significant performance hit when on the network.

I then sprinkled debug.prints in the VBA code as appropriate to narrow things down.

Turns out the problem was due to a form filter based on an Inactive boolean field on a master Job table which had 792 records. Indexing that boolean field fixed this particular performance problem.  

Now given that this forms recordset automatically opens only the last two weeks worth of transactions that field really isn't necessary as a filter.  The users may not be that likely to close a job within two weeks of the last time transaction against it.  And even if it is closed well, so what.  Maybe it's a bit misleading to have some transactions entered yesterday, the job is closed today and now those transactions don't appear on the form.  You could argue this case both ways.  <smile>

‘Tis The Season To Think About Backups

An excellent blog posting ‘Tis The Season To Think About Backups.  " Just this week a close friend as well as a colleague here at Microsoft had their houses broken into and their PCs with all their photos and videos stolen.  Luckily they had done a proper backup. "

The author mentions Outlook and Outlook Express without stating that those files are found in the User Apps folder which is hidden away.   In Windows XP it's found in C:\Documents and Settings\ttoews\Application Data while in Windows Vista it's found in C:\Users\ttoews\Application Data.  (Or similar.)   These days many folks are using Gmail, Hotmail, Yahoo and similar so that may not be of concern to you.   Unless, of course, they lose your email for you.

For genuine stories about lost data see the following blog postings Egyptian poet pleads for stolen works to be returned and Have you made a recent copy of your files and photos?

I've been meaning to visit a friend ...

I ran into her husband today.  I told him I'd give them a call to invite myself over for supper.  He stopped me by saying "We lost her in September.  The cancer spread to her brain."  Granted they're both seniors but I had no idea she had cancer.  Never mind that she had died.  Sure she had some tremors when I saw her last a year or so ago.

Last email from her was in April subject "Haven't heard from you in a coons=age".   We exchanged a few emails and that was it.

Margaret had been around long enough that she knew the meaning of cc as used in emails quite literally.  "I was asked by the Captain to make seven copies of the memo.   I told that that the seventh copy would be so fuzzy as to be illegible. "  This was back in the days of manual type writers, no photo copiers and carbon paper.  cc stands for carbon copy.

Also interesting how many terms used in Microsoft Word come from the  lead based (that's lead as in lead batteries) hot metal typesetting machines which used, among other technologies, molten lead.  I was quite fascinated watching just such a machine being operated a while back.

I've got an aunt who has said for the last four or five years "Yes, we'll call or come out and visit your Dad soon."  Well, he's had some strokes a few years ago and another one a month or so ago.  Minor ones but he's definitely slowing down.  (Not quite as bad as Bilbo in the final scene of the Lord of the Rings but getting there.)  To my aunt who doesn't read my blog - "You'd better call soon."

Java Takes Down the Network

Java Takes Down the Network is a fascinating story about a timed, intermittent network outage.  In this case though I can't believe they still had coax Ethernet cabling.  Well, actually I can.

Jim, who I've mentioned before, stated that when his current office was wired for networking the high school kid run the cable across not one but fluorescent light fixtures.   Worse the cable was right over the ballast.   Jim had been having the occasional network disconnect message but it was a misleading message.

Relevance to Access?   This kind of network problem can easily cause Access corruptions.  When I look at the Corrupt Microsoft Access MDB Causes - Hardware page I stated "If you are running BNC/coax throw it out.  However this highly unlikely these days thankfully."   And I wrote that probably about ten years ago now.

I've personally seen corruption happening in a old house renovated to be office space with an overloaded electrical system.   It was so bad that the server and every work station had a UPS.  I was over visiting and suddenly there was some corruption on a table right there.   I went and looked at the server and saw it had a UPS.  I couldn't find the hub/switch as it was hidden behind a filing cabinet.  I told them it needed a UPS.  The network guy said it didn't.  I insisted back. They purchased a UPS and I never heard from them again.

(That office was so crowded they had two smallish desks in the small bedrooms.  To get past one person sitting at one desk they had to move out of the way.)

Unusual software bugs

Thanks to fellow Access MVP Graham R Seach for showing that these kinds of bugs really do have a name.  And that it's not just me.

"Unusual software bugs are a class of software bugs that are considered exceptionally difficult to understand and repair. There are several kinds, mostly named after scientists who discovered counterintuitive things."

Heisenbug, Bohrbug, Mandelbug and Phase of the Moon bug are among the bugs mentioned.

Do You Really Need A Christmas Tree?

Do You Really Need A Christmas Tree?