Tony's Microsoft Access Blog

Time To Abolish Software Patents? Hell yes!

SlashDot has an interesting article Time To Abolish Software Patents?.  I completely agree with this.   Many of the patents, well those you read about, are exceedingly simplistic, stupid and rather obvious.  And they've been done before.  The system is being totally abused by lawyers and thugs.

I've been following some of the idiocy at the Patent Troll Trackers blog.

There is very little that is truly innovative in software.  Incremental sure.   Interesting sure.  Truly innovative?  I think not.

"ESP Estimates Software Patent Litigation Costs U.S. Companies Spend $30.4 Billion* Annually"  Foul-smelling bovine byproduct.  The only people who make money are the frigging lawyers.  And that's enough of a reason to me.

Copyright?  That should be enforced. 

The Travel Day from Hell

The Travel Day from Hell - Wow, what a story.

An interesting tidbit there.  If something very strange is going on ensure the driver takes your bags out of the trunk before you exit the cab.   Otherwise they leave with you standing there and take your bags.  Of course if you are concerned for your life ...

My most interesting ride was a cab driver in Yellowknife, NWT who got a speeding ticket while I was in the car.   This was just after have gone through a stop sign at a considerable speed.  The RCMP member asked if I was in a hurry.  Presumably he would've kept the drivers license and have the driver meet him at the station after dropping me off.

I few months later I was reading in the local newspaper about how city council put in a bylaw about how many points a taxi driver could have to be driving.  They mentioned one particular driver who was almost suspended on points.   "Aha" I thinks.

How Do You Find Programming Superstars?

A very interesting SlashDot discussion - How Do You Find Programming Superstars?

However they really need to do a better job with their comment posting software.  It's confusing at best.

Multi Row Tab control not

A number of fellow Access MVPs disagreed with the UI of a multi row tab control.  John Mishefske pointed out a excellent page at the Interface Hall of Shame - Tabbed Dialogs.    Of course *MY* multi row tab would not look anywhere near as ugly as some of those examples.  Yeah, right.

I've been thinking on that today and am very much inclined to agree with them.  I'm seriously considering using a list box as per the example a short way down the above page.

One suggestion was to use a Treeview control.  However I'm not going to distribute any kind of OCX given the version troubles that are possible.   At least not for something so minor as one form.

Performance will suffer slightly though as the user switches from one subform to the next and then back and forth as the subform would be refreshed each time.

That is I'm currently only filling the subform record source when the user clicks on the tab.  This really speeds up the initial form open.   So the first time on a given tab it will be slow.  Currently if they switch back to the tab performance will be fast as the data has already been fetched.

If I switch to a list box then performance will be slow each time the user chooses a different subform as it will be loaded each time. Unless I have a whole bunch of hidden subforms.  It'll be at least a few weeks before I add enough tabs to that form so I have lots of time to think about what option I want to choose.

Multi Row Tab control

In my Granite Fleet Manager I'm soon going to be adding enough tabs to the following form that I'll run out of space.   I didn't like the looks of the default behavior.

So I asked in the private MVP Access newsgroup how you could get multi line tabs?  Ken Snell was the first to reply that there is a Multi Row property on the tab control.  So the following is how it will look.

Duh!!!!!

Minor unexpected behavior in VBA DIR function

This is exceedingly minor.  However the following example brought two different results.

?Dir("Q:\1 access\Fleet Mgmt\*.key")
PD.keyzzzz
?Dir("Q:\1 access\Fleet Mgmt\pd.key")
<nothing>

Background.  For my "shrink wrap" application, the Granite Fleet Manager, I email the user a small encrypted license key file with a KEY extension.  This file contains information such as the organization name, the maximum number of units they are licensed for in the app and other similar data.

If a *.key file is found in the BE MDB folder then I pull in the data.  If not found then it's a demo system.  (If more than one *.key file found I display a message and exit.  Should never happen of course.)

In this case I just renamed the file to have zzz after the ".key" extension just to test some logic.  And DIR still found the file.  Of course putting any character after the . and before "key" such as pd.zkeyzzz gave the expected result of nothing.

Now that function has been there for about nine years.  These days I would've used the FindFirstFile API call.  So is it worth changing?

I'm not going to fix it right away because I might introduce more errors in the code. Highly unlikely but possible. And the chances of anyone ever encountering this problem are very, very small.

I'll put it in my To Do Tracking database but I may never get to it.  I did a quick search through the code and I do use DIR in a few other places.  But they look for all the files or a specific file name.  No cases using the wildcard *

P.S.  Yes, the Fleet Management web pages are rather sparse and ugly. I'm working on it.

Edited 2008-10-01 to update the Granite Fleet Manager URL

Fetching the reference description

I've always been irritated at how you can't get at the user friendly reference description as displayed in the Access VBA References screen.

When you look at the properties of the reference object you can't see the user friendly name you see on the above screen. 

Sub DebugPrintReferences()

Dim ref As Reference
    For Each ref In Access.References
        Debug.Print ref.Name & " " & _
            IIf(ref.IsBroken, "Broken", "") & _
            ref.Major & "." & ref.Minor & " " & _
            ref.FullPath
    Next ref

End Sub

The above code will only show you:

VBA 4.0 C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
Access 9.0 C:\Program Files\Microsoft Office 2003\OFFICE11\msacc.olb
DAO 5.0 C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
stdole 2.0 C:\WINDOWS\system32\stdole2.tlb

Not very user friendly when you're trying to post to a newsgroup telling someone what needs to be removed or added.

However fellow Access MVP Thomas Möller suggested adding a reference to Microsoft Visual Basic for Applications Extensibility 5.3 and the following code. (Slightly modified)

Sub DebugPrintReferencesIDE()

' For the refIDE to work a reference must be set to Microsoft Visual Basic for
'       Applications Extensibility 5.3
Dim refIDE As VBIDE.Reference

    For Each refIDE In Access.Application.VBE.ActiveVBProject.References
        Debug.Print refIDE.Description & " " & _
            IIf(refIDE.IsBroken, "Broken", "") & vbCrLf & _
            "     " & refIDE.Name & " " & refIDE.Major & "." & refIDE.Minor & " " & refIDE.FullPath
    Next refIDE

End Sub

which gives you the following:

Visual Basic For Applications
     VBA 4.0 C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
Microsoft Access 11.0 Object Library
     Access 9.0 C:\Program Files\Microsoft Office 2003\OFFICE11\msacc.olb
Microsoft DAO 3.6 Object Library
     DAO 5.0 C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
OLE Automation
     stdole 2.0 C:\WINDOWS\system32\stdole2.tlb
Microsoft Visual Basic for Applications Extensibility 5.3
     VBIDE 5.3 C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB

Windows Live SkyDrive - 5 Gb of free storage

Windows Live SkyDrive   5 Gb of free storage.  I could've used this a few weeks ago for a client to send me some 5 and 10 Mb zipped files.  I'm sure there are other services like this but in the above example I just had the client email me the zipped files so that worked.

Mind you, being the paranoid type, I'd put a passphrase on zipped files I was going to place on there.

You can make files available to specific folks on your Instant Messenger (or whatever it's called now) or key in an email address.

How to Identify Bad CIOs in Their Natural Habitat

An interesting article.  How to Identify Bad CIOs in Their Natural Habitat  Thankfully I've seldom done any work for an outfit with the need for such a title.

Access Basics by Crystal

Access Basics by Crystal fellow Access MVP. While the three or four loyal readers of my blog likely are more interested in advanced Access topics you may have coworkers who could use these PDF files. 

Corrupt Objects within a Corrupt Microsoft Access MDB

Updated the Corrupt Objects within a Corrupt Microsoft Access MDB page to include the following suggestion if opening a particular form or report causes Access to crash.  I also did some reorganization and such on that page.

Try copying and pasting the form or report.   Click on the form/report, ctrl+C and ctrl+V.  You will be prompted for a new object name.  Put in something like the original name and save it.   If the new form/report works then delete the old one.

Error number 16 - Expression too complex

I just had this problem.  I have no idea as to what caused this.   This one is really weird as I'm using the following code to execute startup code after verifying the references.

Access.Application.Eval ("fcnAutoExec2()")

The above was successfully executed but it returned the above error. See Subject: INFO: How to guarantee that references will work in your applications for why I'm using this.

Decompiling fixed this problem.     As did putting a stop statement in the fcnAutoExec2 and removing it.  Weird, really weird.   

The Decompile or how to reduce Microsoft Access MDB/MDE size and decrease start-up times has been updated accordingly.

Bill Gates: Backstage at the Office Developer Conference

Thanks to fellow Access MVP Thomas Möller - Bill Gates: Backstage at the Office Developer Conference.  He shares the history of Office as a developer platform, some hints on the future of Access, how he personally uses Office 2007, if he still writes code, and his view on whether Office has hit the productivity plateau.

A tantalizing hint on Access and Sharepoint at about the six minute point.

Looking for Access constants?

Say you're creating fields on a table using VBA and the field collections.  You're trying to figure out what the possible values are for the field types.  And you've managed to figure out something like the following code.

Dim dbsupdate As Database, wrkDefault As Workspace
Dim tdfUpdate As TableDef, tdfField As Field, prp As Property, idxUpdate As Index

Set dbsupdate = wrkDefault.OpenDatabase(strDatabasePathandName, True)

' Update the Equipment table
Set tdfUpdate = dbsupdate.TableDefs("Equipment")
With tdfUpdate
    Set tdfField = .CreateField("eUnitNbr", dbText, 6)
    .Fields.Append tdfField
    Set tdfField = .Fields("eUnitNbr")
    Set prp = tdfField.CreateProperty("Caption", dbText, "Unit #")
    tdfField.Properties.Append prp
    Set idxUpdate = .CreateIndex("eUnitNBR")
    idxUpdate.Fields.Append idxUpdate.CreateField("eUnitNBR")
    idxUpdate.Unique = True
    .Indexes.Append idxUpdate
End With

But now you're thinking.  "Ok, I remembered the dbText constant.   How do I find the other constants?"  And Access help may or may not work.

In the VBA editor on the menu bar select View >> Object Browser or press F2.  Key in the constant you managed to dredge up from the dim recesses of your memory dbText in the appropriate field and hit the Search icon.  And now you get the very handy screen as below.

Arthur Benjamin: Lightning calculation and other "Mathemagic"

Fellow Access MVP Allen Browne brought the following video to our attention.  Incredible.  Arthur Benjamin: Lightning calculation and other "Mathemagic"

I was quite interested in mathematics as a teenager.   Thanks to my teacher Mr. (Chester I think) Gris.  However he introduced me to a monthly (or was it bimonthly) newsprint magazine called Creative Computing which got me interested in computers.  This was when PCs had 4 Kb RAM and thus significantly predated IBM PCs.

Microsoft Office Binary (doc, xls, ppt) File Formats

Now isn't this interesting.  The Microsoft Office Binary (doc, xls, ppt) File Formats specifications are available for download.   Although I suspect the Office 2007 XML based files would be much easier to work with.

Will the MDB/ACCDB format ever be published?   All kinds of interesting speculation there but I have no idea

Note that there is a group working on reverse engineering the MDB format.  MDB Tools is a set of open source libraries and utilities to facilitate exporting data from MS Access databases (mdb files) without using the Microsoft DLLs. Thus non Windows OSs can read the data.  Or, to put it another way, they are reverse engineering the layout of the MDB file.    Download the file and look at the hacking file.

What is annoying to me are the Access data recovery outfits which have copied that hacking file onto their web site with attributing the original creators of that document.  See http://www.fdrlab.com/files/accessfileformat.txt and http://www.e-tech.ca/001-AccessFileFormat.asp.  This web site copied part of it http://www.filerepair.net/bb/viewtopic.php?p=3&sid=096e617bca1533e1bdf7849e4fc70a69 but then referenced another web site http://www.etechrecovery.com/001-AccessFileFormat.asp.

Life At Microsoft - The Truth Revealed

Life At Microsoft - The Truth Revealed

The value of work

An interesting parable The value of work 

So someone has a brilliant, to them, idea and wants you to develop the software.  Who should own the software?   The idea guy or the person who spent months developing it? 

I've had people approach me with interesting ideas.  Now my first question is "So how do we contact other people who would be interested in that software?"  My second question is "Is there decent software in that area already?" 

Java, Netbeans and Microsoft Access

Someone posted a link to a tutorial on using Java, Netbeans and Microsoft Access - Persistence, Binding, Annotations: an example with NetBeans6.0 and Access.   Undoubtedly 99.9% of my loyal readers won't care but I thought this was interesting enough.  And to be perfectly honest I don't have a clue what Netbeans are and not much about Java either.

I did learn something new though.  I never knew you could insert multiple records in one SQL statement.

INSERT INTO `town` (`zip`, `town`) VALUES
('12340', 'Milano'),
('12341', 'Roma'),
('12342', 'Napoli'),
('12343', 'Bari'),
('12344', 'Bologna');

Heroes Happen Here {Comic Series}

An interesting RSS comic strip - Heroes Happen Here {Comic Series}

The other RSS comic strips I follow are Dilbert (of course) and xkcd