Tony's Microsoft Access Blog

Playing music severely degrades network transfer performance in Vista

Playing music severely degrades network transfer performance in Vista

Now isn't that one interesting.  Thanks to David for the posting Vista network problem.  Access Performance FAQ page has been updated.

Developer roadblocks

The first half of the page titled The Cost of High Security is bad enough but such delays do make a bit of sense.  One MVP I know had to wait six months to start a new job in the U.S. government due to security checks.  And he was relatively fortunate in that he had lived in the same zip code all his adult life so only one person (or team) needed to do the background checks.

However the indented and italicized story on the bottom half is fricking unbelievable.  No wait, it is entirely believable.  <sigh> The idiot managers at that corp are classic PHBs.  (Dilbert's Pointy Haired Boss.)

If an index is a duplicate of another index then ...

Visit If an index is a duplicate of another index then Access/Jet does not take any extra space to read the article and download a zip file containing the sample MDB to see for yourself. 

 I do have a tool available to find duplicate indexes but it needs a few hours of polishing.  This is particularly useful if you hit the maximum 32 indexes per Access table and get the following message.  "The operation failed. There are too many indexes on table '<table name>'. Delete some of the index on the table and try the operation again."   If you are interested in the tool email me and I'll do the polishing.

Updated Tony's Table and Field Naming Conventions

Upon some mild prodding and questioning by a fellow MVP I realized I was missing some of the reasons why I do things.  So I've did some editing and added text to Tony's Table and Field Naming Conventions

I love Dilbert

I thought the cartoon at http://www.dilbert.com/comics/dilbert/archive/dilbert-20070824.html was particularly funny. 

I will add though that I've never had this problem at any time in my 28 year career.   Clients have always understood priorities and that you can't do everything at once.

One client did hire a jack*ss in middle level management who thought he know a little about software development.  After a few months I gently fired the client.  I later found out that not only did they fire him they escorted him out of the building with security guards. 

I also particularly enjoyed the following Orphaned Projects xkcd - A webcomic of romance, sarcasm, math, and language.

Tony's Object Naming Conventions

We were having a very interest and enjoyable discussion in the microsoft.public.getting.started newsgroup with the subject Best Practice for tables? (For some unknown reason Google missed the first posting in the thread.)  I jumped in with my contrary viewpoint and was forced to explain why I do things.  As a result I created a new tips page titled Tony's Object Naming Conventions

Software is not made of bricks

Bricks   A very interesting blog posting and well worth reading.

I hit the compile button every few lines of VBA code I enter

Compile button you ask?  Then you say "But I always have to go Debug and Compile Project."  and "Sometimes I don't even think of it."

When in the VBA IDE I always have the debug menu visible.  I put it on the right hand side of the Standard Menu.  I then added the compile button to the Debug menu.

Do make very sure that you have the Require Variable Declarations check box on.  You get there from within the VBA Editor by Tools >> Options >> Editor tab.   Access 2000 in particular turns this off. 

This once cost me an hour of wasted effort while doing a very high pressure database on site during the last few weeks of building what was at that time North America's largest every construction project.  It never occurred to me to look for Option Explicit not being present at the top of the VBA module.  GRRRR.  Fortunately MS turned that option back on in Access 2002 as I recall.

While you're there I always turn the Auto Syntax Check off as every line that has a compile error gives you an annoying message box.  You should see the red line of code with an error.  But if you don't then hitting the compile project button will show you the lines of code with errors.

And yes, that is MZ-Tools on the lower left hand side.

I find that working with the Toolbars to be quite non intuitive but I'm getting a bit better at it.  

1) To add the Debug Toolbar right click anywhere on the ToolBar and select the Debug Toolbar.  It will now be plopped (excellent techie term that) as a floating toolbar on your screen.  Drag it to the existing Toolbar and to the right hand side.

While you're there add the Edit Toolbar too as it has some nice features too especially the comment and uncomment buttons. 

2) Right click anywhere on any ToolBar and choose Customize.  The Toolbar on the right hand side will move to the left.  This is only a temporary thing.

3) Click on the Commands Tab and click on the Debug Category in the left hand list box. 

4) On the right hand list box you will see Compile Project as the first item.  Drag and drop it onto the Debug menu.

5) Close and you're done.

This was all done in Access 2000 so other versions of Access might be slightly different.

Thanks go to TechSmith for their free MVP license for SnagIt.  Took me a minute or two to figure out the best way of snagging the toolbar but after that it was effortless.   Nice user interface and quite easy to use.  It was quite easy to add the red arrow too.    And I've never used SnagIt before either.   It was a lot less effort than using Ctrl+Print Screen, MS Paint to paste the image into and a graphics tool to resize the jpg.

Correction #2 on undocumented SysCmd items

From fellow Access MVP Krzysztof Naworyta

"Tony, thank You very much for that entry, but I have to correct it ...

The name of person who made that work is Krzysztof (english: Christopher) Pozorek, my very good friend, owner of that polish site, who I try to help editing his site with. He has investigated SysCmd() with very similar Loop as yours

My only work was about 715 (build number) and 609 (process id)
(http://www.access.vis.pl/af0424b.htm)

He is MVP Access, too
https://mvp.support.microsoft.com/profile=625860ed-bec2-424c-a29f-0b0d6712d5e8"

I then asked the question:  About what date did Krzysztof Pozorek do his original work? I'd like to ensure that's <in my blog> as well.

"It is very hard now to find that date ...

Google makes many mistakes with our language since several months...

I've just found my message with syscmd(715), that was made several days after Krzysztof's first announcement:

date: Oct 9 2002"

Alt+Tabbing from VBA IDE to Access 2003 Window

A few years ago I whined about how sometimes Alt+Tabbing from VBA code window to form view in
A2003 only got up the property sheet over .   Someone, I'm pretty sure it was former Access MVP Sandra Daigle, responded that Alt+F11 would work.   And it did.

Correction on undocumented SysCmd items

I've been informed that Krzysztof Naworyta, fellow Polish MVP, was the first person to document the usage of the undocumented Syscmd itemss.   However his site is in Polish so understandably a lot of us weren't aware of some of these details.

When I do some searching in Google Groups I see mention of one code, 888, back in 1998 and mentioned of syscmd back in 1993.  But certainly very little discussion of other codes that far back.

IE is no longer my default browser

Microsoft now has f***ing Flash ads on their website.   My first encounter with such was on a Microsoft Access page titled Enable the use of an ActiveX control in expressions which has an explanation of SysCmd(14 ...).   (Thanks to Gunny, fellow Access MVP, for doing a bit of searching.) 

I despise animated ads.  And they're nicely blocked in FireFox.  I will be using IE as little as possible. 

So what are the undocumented SysCmd items?

In my previous posting I linked to Undocumented SysCmd Functions.  So I was curious and created my own code to go through the various SysCmds.   I came up with the following code.

Sub FindSysCmds()

Dim ReturnCode As Variant, i As Long
On Error GoTo tagError

' 1 through 13 are document in A2003
For i = 14 To 1024
    ' The following caused A2003 to crash
    If i = 602 Or i = 605 Or i = 606 Then _
        GoTo tagNextLoop
    ReturnCode = SysCmd(i)
    Debug.Print i & " " & ReturnCode
tagResume:
    If i > 605 Then
'        Stop
'        If i Mod 10 = 0 Then _
'            MsgBox i
    End If
tagNextLoop:
Next i
Exit Sub

tagError:
    Select Case Err.Number
    Case 2439 ' The expression you entered has a function containing the wrong numb
        Debug.Print i & " - wrong number of arguments."
        Resume tagResume
    Case 7952 ' You made an illegal function call.
        Resume tagResume
    Case Else
        Stop
    End Select
    Resume Next
End Sub

This code returned the following items.

14 - wrong number of arguments.
555
603 - wrong number of arguments.
604 - wrong number of arguments.
607 252
609 5912
610 - wrong number of arguments.
611 - wrong number of arguments.
612 - wrong number of arguments.
701 - wrong number of arguments.
710 0
711 67699721
713 - wrong number of arguments.
714 True
715 6566

Most of those are on Wayne's list.   A few aren't such as 14 and 713.   And his list hasn't been updated as a few on his list appear to not work in Access 2003. 

Now I'm sure the folks at Microsoft are going to see the crash reports regarding codes 602, 605 and 606.   And I suspect a few on the Access team follow my blog.  So it'll be interesting to see if I get any comments.

Anyhow I don't have the time today to continue this research on what syscmd(14) does or which crash or don't crash in other versions of Access.

Undocumented SysCmd Functions

Thanks to fellow MVP Thomas Möller I was reminded of the page Undocumented SysCmd Functions.  Pages like this are always interesting reading for hard core Access geeks such as I.   (Should that be I, me or myself?  I always get confused.) Pages like that are always interesting reading for hard core Access geeks.

Testing Automation for MS Access

A very interesting question was asked with an excellent response on the subject of "Testing Automation for MS Access"   Of course I had a minor disagreement so I jumped in.  <smile> That thread was a week ago but as I was at a family reunion I'm now catching up on older threads.

Feel free to jump in with comments by clicking on the Reply button while viewing the thread in the Google groups interface above and logging in.  Alternatively you can visit that thread on the Microsoft Office Online newsgroup interface.  You will also need to log on there using your Passport/Windows Live account.

Sometimes the "Sizzle Factor" works

Dad always liked have something he called the bullsh*t factor.  In polite company it would be called the sizzle factor.  One example is he was a very early adopter of laser technology for doing perfectly flat T-bar ceilings in large buildings such as offices, shopping centres or schools.

Another was a huge stack of drywall outside an apartment complex which was right on the main drag called Franklin Avenue right next to downtown in Fort McMurray.  The pile of drywall was about 16' high, three rows wide and sometimes eight stacks deep.  The stockpile lasted for two or three months long. 

The manufacturer had a kiln explosion in their plant in Edmonton so they lost half their production.  Dad told them that he didn't need any drywall for a while and to defer their shipments to him for other customers.   When the sales rep came up a month later he saw the drywall stack and laughed.

But I digress from Access or even developer related issues.

See The Cool Cam from the Worse Then Failure web site.

What have you done that you felt was a sizzle feature with little substance?  Yet it was a hit with your users?

One person I know would always ask his clients about their corporate colours and ensure the background of the forms matched.  Of course this was in a configuration setting somewhere that each form read when the form was opened.

Testing at Microsoft - The SDET position

Testing at Microsoft - The SDET position I thought this was a very interesting article.  One of our own Access MVPs, Jeff Conrad, did such a good job coauthoring the Microsoft Office Access(TM) 2007 Inside Out book Microsoft hired him. 

Updated the Microsoft Access (Office) Developer Edition FAQ page

I've updated the Microsoft Access (Office) Developer Edition FAQ page to include more information on the Access 2007 Developer Edition

You can now request MS Hotfixes online

In the past you had to phone MS Support.  My experience was that was quite painless.  A minute or two on hold, a statement stating I needed this hotfix, gave them an email address and within a few minutes I had an email with a link.

Now it's even simpler.  Fill out the form at Hotfix Request Web Submission Form.

The Institute for Backup Trauma

The Institute for Backup Trauma starring John Cleese nicely illustrates some of the basic problems we encounter.  And does so very, very well.   Although I haven't seen a failure rate of tape backups as high as 50%.

There was just a short story in the newspaper about a woman who left her purse in a hospital washroom after changing the baby's diaper.  Stolen from her purse was cash, digital camera and memory stick containing the only copies of photo's of the first month of her baby.