Tony's Microsoft Access Blog

Spit does not go clunk at -40 aka Converting to Metric

2009-01-05T10:16:44Z

I just had to laugh long and loud when I read the Converting to Metric XKCD cartoon. In light of the Canadian prairie deep freeze where the temps have had wind chills in the -40 and -45 C for quite a number of days recently.

Spit does not go clunk at -40.

Mind you there are some videos on YouTube showing boiling water being thrown in the air. For example From Boiling to Frozen

A number of years ago when I was a teenager I was working for my Dad up in Fort McMurray, Alberta on some 3 1/2 story walkup apartment blocks. The recessed balconies had to be painted so they were covered in multiple players of plastic. It was about -45 or -50 C outside without the wind chill. I was really tempted to cut a small slit in the plastic on the fourth floor balcony and see if err, ahhh well you know, that yellow stuff, would be frozen when it hit the snow. However I quickly realized that a rather important chunk of the male anatomy might get frost bite which I figured would be a bad thing.

My sister, who reads my blog, is just shaking her head right now.

Internals viewer for SQL Server

2009-01-02T02:32:57Z

Internals viewer for SQL Server I always enjoy peeking under the covers. Hey, computer software covers. Get your mind out of the gutter. This blog posting mentions how to poke about under the covers of SQL Server.

Similar to MDB Tools - A set of libraries and utilities for reading Microsoft Access database (MDB) files.

Download the file and open the HACKING file.

A new hot fix for Access 2007 is now available

2008-12-31T22:39:00Z

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?

2008-12-29T05:56:58Z

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

2008-12-29T02:43:47Z

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

2008-12-27T05:53:07Z

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

2008-12-23T22:36:16Z

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

2008-12-21T05:23:34Z

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 ...

2008-12-12T04:19:31Z

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

2008-12-12T01:07:29Z

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

2008-12-04T22:26:34Z

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?

2008-12-03T22:03:48Z

Do You Really Need A Christmas Tree?

A Training Issue

2008-11-30T23:36:28Z

A Training Issue <chuckle> Folks much younger than me won't necessarily get this one as they've never seen punched cards.

I recall a client nearly thirty years ago telling me how they'd lose data if the tapes snapped. They'd splice the tapes back together but would be missing a block or two of data.

Standard NotInList VBA code

2008-11-30T23:18:19Z

I finally got around to creating a Using NotInList in a combo box page at my website. I keep forgetting to make up same sample code and re use it as I don't care for the MS sample code in the Access help. A bit too verbose and it doesn't show how to deal with adding records to tables and then opening up a form. So here's the code I just setup as a sample. Critique away.

Using pictures on a form and report

2008-11-30T22:23:36Z

The following question was asked in the comp.databases.ms-access newsgroup.

I'd like to add a picture in a report's detail section where the path to the picture is from a table.

Incredibly easy. The same code works on a form as well. Using the Toolbox drop an image control on the reports detail section. Note that you will have to follow the dialog and actually insert an image. But then go to the Picture property on the Format tab in the property sheet and delete the actual file. You will probably want to set the Size Mode from clip to zoom. And rename the control to something meaningful.

Then in the report detail section in the On Print event you want to add the following lines of code

    If <logic if file available to view> Then
        Me.PhotoImage.Picture = PhotoFilePath
    Else
        Me.PhotoImage.Picture = ""
    End If

PhotoFilePath being whatever logic you want to use to figure out the file that you want printed.

You don't want to do this logic in the Format event as Microsoft has told us, via Stephen Lebans, that using that event can lead to memory leak problems. Or you can use BMP images. Which is rather impractical.

The reason for extensive bloating in Access when JPG or other graphic files are permanently embedded in forms or reports is that Access converts those file formats to BMP format. This made sense back in Access 2.0 days is viewing a JPG file was very slow as it requires lots of CPU cycles. 80386 computers running Windows 3.1 were rather slow at that. BMP format meant that you basically read the large file directly into the graphics card memory. In Access 2007 the Access product group have updated that logic so the BMP file format isn't created any more.

Shameless plug: I have a number of reports in the Granite Fleet Manager which print a "primary" and a number of "secondary" photo's for each unit. See Granite Fleet manager Main Menu a screen shot and Service Order Details (with Photo) for a sample report.

I never knew that - view linked table file path and name

2008-11-30T22:08:17Z

I was in the table view in Access 2003 and accidentally hovered over a linked table. A tool tip (mouse over tip) displayed itself a second later telling me the backend linked MDB file path and name.

Cool! I never knew that.

I should add that I go to great lengths to ensure a user doesn't have a problem with linked tables. When the user opens the app I ensure that all tables have the same linked path and file name. Just in case. You never know when something really, really weird could happen when relinking tables. And I'd hate to have to debug a customers problem where a few tables are pointing to a demo MDB and all the other tables are pointing to their live data table.

Other things I do on start are opening a recordset based on a particular table and check for a particular field in it. If that fails I assume the BE isn't linked properly and start up the linking table logic.

Note that I install the Granite Fleet Manager into the users Application Data folder and sub folder of Granite Fleet Mgr using Inno Setup. I also install the Demo MDB and the demo graphics files into that same folder. However the live empty Data.MDB gets installed into My Documents\Granite Fleet Mgr.

I then check to see if there is a license key file in the users My Documents\Granite Fleet Mgr folder. If so I then look to see if there is only one data MDB file in that folder. I inform the user and ask if they want to relink to that table. If there are two or more data MDBs then I inform them and pop up a relinking form. This handles the situation where I've converted their data from a spreadsheet and emailed the user that MDB and the license key file.

Note how the number of Units and Service Orders are displayed in the list box. I've found this to be of great value when working with clients and somehow multiple versions of the BE data MDB are floating about.

Thanks to Jim, a friend and client, who suggested adding the Selected Data File Folder field. If folks are interested and understand what is all going on then that's all the better. That field can be double clicked and Windows Explorer opens to that folder. The code behind that double click event for the Selected Data File Folder is:

Call RunShellExecute(tt_StripFromLastRight(CurrentDb.Name, "\"))

tt_StripFromLastRight is a function which removes the "\" and everything to the right leaving just the path.

If nothing found as per the above paragraphs then the linking table logic first examines the current FE path for the Granite Fleet Mgr.INI file. If found I then relink to the BE MDB as specified in that INI file. This handles the situation where the app has been previously installed on the system and the INI file is pointing to a network file location.

If that file isn't found then I assume this is the first time the user has ever run the app and link to the demo MDB.

I also display the path and file name of the BE MDB in the About screen. As well as the current FE MDB/MDE file path and name.

I also put in a double click event on those two fields which the user can then open up Windows Explorer. Do you really want to tell a user how to use Windows Explorer to navigate to a path that Windows by default hides from the user? No, I didn't think you would either.

Still reading? All this when all I meant to type was the first few paragraphs. <sigh>

New version of the Granite Fleet Manager

2008-11-30T21:13:46Z

Ten year annual maintenance costs report
Email feedback form
Added meaningful error 2114 screen
Minor UI improvements and bug fixes

http://granitefleet.com/

Commissions paid to sales agents.

How are your personal backups?

2008-11-29T20:53:34Z

Including all your photos? And your Outlook and Outlook Express files which are, last I read, stored in a sub folder of your Application Data folder and not your My Documents folder?

"The GpCode family is a dangerous form of malware which can permanently destroy files by encrypting them. The capability for AV products to de-crypt files vary and can't be relied on in all cases, especially when complex encryption techniques are used. "

New GpCode Ransomeware attack encrypts victims files

No, no, no. Do not leave this until tomorrow. Grab a coffee and start copying files to a DVD.

My muddiness when it comes to OpenArgs

2008-11-26T21:37:03Z

I was getting an syntax error message when I clicked on a New button on the Equipment Quick Finder form. The equipment form in the Granite Fleet Manager executes a reasonable amount of code on startup.

I had added that New button to the Quick Finder form when I created it and all was well. Trouble is I added some code in the Equipment form that required the OpenArgs start with "New" when the record did not exist. And now the code was causing me troubles. While I fixed the immediate problem I realized I had a larger issue.

When testing I realized that if I'm updating a piece of equipment then the OpenArgs will legitimately be empty.

So I added the following code in the forms Open Event.

If IsNull(Me.OpenArgs) And Len(Me.Filter) = 0 Then _
MsgBox "OpenArgs is null and Filter is empty."

I also did a Find through all the code looking for OpenForm "Equipment" just to ensure I didn't have any other problems hidden away somewhere else.

The lesson is that if you are depending that OpenArgs have values in it ensure that values are always present. Pop up a message for the programmer to give him (or her) a kick.

This is a follow up to the Watch out for Null OpenArgs blog posting. (And I sincerely hope my clients never chip in to purchase the chair mentioned at Microsoft: We Share Your Pain.)

Meteorite search update

2008-11-26T07:13:04Z

Very interesting. Meteorite search update It was estimated to weigh 10 tonnes when it entered the atmosphere at 14 km/sec. Possibly football sized chunks.

I sure hope the local farmers aren't troubled too much by *rsehole souvenir hunters but I know they will be. They might as well get any cattle, horses and livestock off the fields until the snow falls. And even then there'll be folks out there leaving the fences open or just plain cutting the fences. I'd like to see the scientists get the rocks first before the folks who want to sell chunks.