Tony's Microsoft Access Blog : bug

FindExecutable API call fails for ACCDB file extensions

Tony — Sun, 13 Sep 2009 21:22:58 GMT

The API call "FindExecutable: Find Exe Associated with a Registered Extension" fails on a file with the ACCDB extension. The API call does work fine with the MDB extension. Clicking on an ACCDB file in Windows Explorer does work. This is a on a new, fresh Win XP/Access 2007 install with nothing else on the system and no uninstalls, etc.

This has been confirmed by folks over in the microsoft.public.vb.general.discussion newsgroup.
Microsoft Communities - FindExecutable API call fails for ACCDB file extensions
Google Groups - FindExecutable API call fails for ACCDB file extensions

Note Mike Williams statement: "There were all sorts of strange anomalies where certain file extensions were found by FindExecutable and other file extensions were not, with (up to now) no apparent rhyme or reason behind it. "

Irony/Stupidity/Blind spot

That said ShellExecute will do what I want. I had a blind spot and forgot you could send it parameters. So I’ve spent several hours getting this API call working, or so I thought until someone hit this bug, for nothing. <sigh> Worse, I could’ve added some requested functionality to the Auto FE Updater a long time ago. <bigger sigh>

I will be issuing a new release of the Auto FE Updater in the next few days after I fix up a few other minor bugs.

Navigation Pane Description missing - Access 2007 SP2

Tony — Sat, 02 May 2009 02:33:44 GMT

"Use MS ACCESS 2007 to manage large volume of accounting data, and have many
tables and queries. I enter a description in these tables and queries to
help me remember what they all do. MS OFFICE Service Pack 2 was released
into my machine last night, and now the descriptions have disappeared in the
navigation pane I use to run queries."

Navigation Pane Descriptions -2007 Office Service Pack 2

Confirmed by multiple people and Microsoft.

"Object or class does not support the set of events." error message

Tony — Mon, 06 Apr 2009 01:54:08 GMT

I start getting the following message when opening a form after doing some minor changes to it.

"The expression On Open you entered as the event property setting produced the following error:
Object or class does not support the set of events."

Commenting out the code in the OnOpen event, compiling, uncommenting and compiling didn't help. Neither did decompiling which startled me.

However saving the form with a different name fixed the problem. Bizarre.

"Type Mismatch" bug and line numbers in the Auto FE Updater

Tony — Sun, 25 Jan 2009 03:58:33 GMT

I've been getting intermittent email reports of a "Type Mismatch" error message in the Auto FE Updater. However the problem just wasn't making any sense. Thankfully the most recent person who emailed me was quite persistent and so the problem is solved pending the last version he is going to be testing very shortly.

The lengthy amount of delay in figuring out the problem was exacerbated by my own stupidity. I use a lot of API and other code especially from Randy Birch's VBnet Visual Basic Developers Resource Centre. I didn't bother adding my standard error handling routine in much of the code for whatever foolish rationale I can't quite figure out now. In hindsight of course. So the error message was passed back to upper level procedures where there was no numeric handling so I was greatly puzzled as to how some string manipulation was causing "Type Mismatch" messages.

Turns out the problem was due to converting the OS version number "5.1" string (meaning Windows XP) in systems where the decimal symbol is , (comma).

In that code look for the line

Text1(1).Text = win.VersionNum

In that example the code is being converted to a string so no problem. However my code converted it to a Single variable.

Now when I set the decimal symbol to comma on my own system the number gets converted to 51. And not 5.1. I'm not quite sure why Norwegians and others are getting type mismatch. But I'm not going to worry about that for now.

Thanks also to MZTools for having buttons to add and remove line numbers from subroutine and function calls. This allowed me to ship new versions of the Auto FE Updater to the tester and narrow down the line in the routines causing the problem. You can use the variable Erl to display the line number of the line of code causing the problem.

MsgBox "The following error occurred in procedure GetWinVerAll of Module APICalls" & vbCrLf & vbCrLf & _
Err.Description & vbCrLf & "Line Nbr: " & Erl & vbCrLf & vbCrLf & Command() & _
gblstrSupportMsg & gblstrCopyrightNotice

There are a variety of methods to determine the decimal symbol including API calls such as GetLocaleInfo: Regional Locale Numeric Settings. However Rick Rothstein, fellow MVP had the following elegant solutions.

DecimalPoint = Format$(0, ".")
DateSeparator = Format$(0, "/")
ThousandsSeparator = Mid$(Format$(1000, "#,###"), 2, 1)

Tony

P.S. In another day or two I'll have a release of the Auto FE Updater which solves this problem.

"Out of memory" or "Object not found" while importing reports?

Tony — Mon, 19 Jan 2009 03:06:44 GMT

Just trying to figure out some weird problems with a clients medium sized front end. I isolated the above messages while importing reports to having PDF Creator as my default printer. Once I switched my default printer to my HP Laser Jet printer I was able to import the reports just fine.

Note that the commercial product calling itself PDFCreator as at http://www-pdfcreator.com is just trying to make $$$s attempting to masquerade as the open course product See Impostors on the Internet.

Unusual software bugs

Tony — Thu, 04 Dec 2008 22:26:34 GMT

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.

Losing the [Event Procedure] on a command button

Tony — Tue, 11 Nov 2008 07:40:03 GMT

I just added a tab control to my standard File Locations form in the Granite Fleet Manager program. I added the tab control and cut and pasted all the controls from the form to the page on the tab control. I then discovered that I had lost all the [Event Procedure] entries in the command buttons. I've added them back in before I took this screen shot.

I've noticed this behavior occasionally before but had never realized what caused this. In this case it was quite easy to recall as I had made this change a few days ago and all three command buttons on the form were missing the [Event Procedure]

Background.

I've decided to install the Access Front End (FE) and the demo BE MDB and demo JPG files in the users Application Data\Granite Fleet Manager folder. I decided to install the live data BE MDB into the users My Documents\Granite File Manager folder. Thus the data MDB is easier to work with if the user wants to move the files onto a memory stick or anything else. The default settings in Windows Explorer make getting to the files in the Application Data folder rather difficult.

However my current database location form displays all the BE MDBs in the folder in a list box. I felt that while that was good for me in testing and good for network installs it was sub optimal for an end user who just wants to use the program on their own PC. (Sub optimal, yeah, that's a nice euphemism for sucks.)

For stand alone users they only care about the demo system and the data system. So on the Basic (Single User) page on the tab control I created a listbox which displays all the BE MDBs in the same folder as the FE MDE is installed and all the BE MDBs in the My Documents\Granite Fleet Manager.

And I cut and paste the already existing controls on the form to the tab Advanced (Networks) page on the tab control. Which caused this problem.

A refinement

I then thought about things a bit. Once the user sends me some money I email them a 265 byte license key file containing their encrypted business name. Now I can tell them just to drag and drop that file into their My Documents\Granite Fleet Manager folder. The application looks for a key file in that folder and asks the user if they want to relink to the data file in that folder. If they click Ok then they get relinked automatically. They don't even see the above form. Now if they ever do want to switch between the data and the demo BE MDBs they can get to the above folder.

Another refinement

A further refinement is to handle the situation where they email me a spreadsheet containing all their equipment. I will import the spreadsheet into the data MDB and email them back a complete data MDB. Typically with the name of their organization in the file name.

Below is the screen should the license key and two BE MDBs exist in the My Documents\Granite Fleet Manager folder.

That should be simple enough for any novice computer user. Maybe. I hope.

Note how I display the unit and service order count in the list box. I've taken to displaying the number of records in one or two key tables in the app on the main menu. This really, really saved my butt big time once about eight or nine years ago. I had to convert their backend to the new data format manually. (Now I use VBA code and the Compare'Em utility.) Well, I screwed up and converted a copy they had emailed me a few weeks before.

I also added a flag to a table (I always have a GlobalOptions table) in the BE MDB which states if the MDB is Demo, Data or Test. Test should only reside on my system.

Access 2003 - "Please wait while Windows configures Microsoft Office Professional Edition 2003"

Tony — Fri, 03 Oct 2008 22:57:42 GMT

GaryS started a thread titled All Applications work but Access. I was stumped but he did some more sleuthing and figured out that the Access 2003 post-Service Pack 3 hotfix package: December 18, 2007 solved the problem.

To GaryS - thanks for posting back the solution.

Compile error: Sub or Function not defined

Tony — Fri, 27 Jun 2008 20:06:00 GMT

As a follow up to my previous blog entry Compile error: Method or data member not found here's the identical VBA IDE problem but due to a function name not found.

In the above screen shot CalcPrice is the undefined function yet the VBA showed the previous variable.

Note that I frequently use the If ... Then _ if there is only one line of code to be executed. Looks nicer, in my opinion, and shortens code.

Compile error: Method or data member not found

Tony — Mon, 16 Jun 2008 07:49:00 GMT

I'd forgotten about this bug in Access 2003 VBA. I was getting the message Compile error: Method or data member not found but the me. control was just fine. In fact I could see the control in the line above. I spent about 10 minutes trying things getting more and more frustrated.

A dim memory of getting something weird like this before surfaced and so I tried putting a quote in the code to separate out the parts of the line of code that weren't, apparently, causing a problem. And that fixed the problem.

Oh yeah, now I remember that bug. If you have several me controls on a line, or maybe two field from a recordset or such VBA can get confused if one control or field doesn't exist and indicate an error with the other control or field before it on the same line.

In the below the red circled control ptxCostz is the problem while the white on black control ptxMarkupPct is innocent of any wrong doing. And see how ptxMarkupPct is used three lines up and at the beginning of the line?

KB article 950812: Compact and repair might delete your database (Access 2007)

Tony — Fri, 30 May 2008 00:41:40 GMT

If you're reading my blog then you should also be subscribed to the MS Access team blog. Nevertheless this is an important notice for Access 2007 foilks so here you go. KB article 950812: Compact and repair might delete your database (Access 2007).

There have been isolated reports in the newsgroups of vanishing databases after doing a compact and repair in Access 2007.

I've been off to two funerals and visiting family in Manitoba these last few weeks thus nothing new on my blog. Besides I was on dialup. RIP Alice Toews and Jan Bron.

Bug in ADO, SQL Server and latest OS SPs

Tony — Sun, 11 May 2008 04:56:55 GMT

On a computer that is running Windows Vista, Windows Server 2008, or Windows XP, an incorrect value is returned when an application queries the identity column value of a newly inserted row in various versions of SQL Server 2005 and of SQL Server 2000

Now I don't know that this affects Access but the KB article states "Applications that use the ActiveX Data Objects (ADO) interface ..."

Thanks to fellow MVP Steve Foster for mentioning this KB article.

The expression On Click you entered ...

Tony — Wed, 23 Apr 2008 06:40:00 GMT

I was getting the below error message in Access 2003 when I clicked on a command button. Likely because I was working on a form module and possibly saved it while the form was in regular view and not in design view.

"The expression On Click you entered as the event property setting produced the following error:
Object or class does not support the set of events."

Commenting all the code behind the form did not fix the problem.

Setting the Has Module to No and saving the form, surprisingly, did not fix this problem.

Decompiling the MDB also did not fix the problem.

This problem also existed when I opened up the MDB in Access 2000 and clicked on a button on the form.

Importing all the objects into a new MDB finally did the job.

Hint: when importing lots of objects into a new MDB minimize the database container window before importing. Access will import much, much faster.

As a bonus a child sub form which wasn't saving the parent value in the child table also started working properly.

As a side note every hour or two I always go into Windows Explorer, click on the MDB and do a Ctrl+C and a Ctrl+V to copy the FE MDB. Every few days I zip all those and move them to an archive folder.

Added: I later figured out that I just needed to do a Save As of the troublesome form.

Getting Your "Favorite" Bug Fixed

Tony — Mon, 14 Jan 2008 00:54:09 GMT

A very interesting article describing how Microsoft personnel decides which bugs get fixed first. Getting Your "Favorite" SQL Server Bug Fixed. This does apply to other products within Microsoft.

Allen Brown, of course, has done a fine job of documenting a number of these "quirks", shall we say, in Access 2003 and older and has had some of those fixed in Access 2007. He is also tracking bugs in Access 2007.

If you want a bug fixed write something up along the lines suggested and post it to the public Microsoft newsgroups. This will bring up some discussion for possible work arounds and, if we fellow MVPs feel it is worth while, we'll send it off to the Microsoft Access team. Be meticulous on your repro steps.

For example there was some recent discussion in the public newsgroups on why The ID field is increment by one every time that you add a field to a new table in Datasheet view in Access 2007 was not fixed in Access 2007 SP1. Part of this may very well have been that it was too late in the Service Pack cycle to get it fixed. However Microsoft personnel may also have decided that this particular bug doesn't impact many people and put it at a lower priority than other bugs. So it might get fixed in SP2.

Access 2007: "This database is in an unrecognized format"

Tony — Thu, 20 Dec 2007 22:05:25 GMT

An accde or mde created in Access 2007 SP1 can't be read in Access 2007 no SP.

Error message when you try to open a compiled database file or a compiled project file in Access 2007: "This database is in an unrecognized format"

Some Microsoft Access MDB Corruption Symptoms page has been updated accordingly

Access 2003 SP3 bug on bit data type field in a linked SQL Server table

Tony — Wed, 07 Nov 2007 23:13:00 GMT

We've been seeing reports of this in the newsgroups. Here's Microsoft's KB article: After you install Office 2003 SP3, you encounter various problems in an Access project when you work with a Bit data type column in a linked SQL Server table

Running Access on a Windows Vista peer to peer network?

Tony — Wed, 13 Jun 2007 17:31:00 GMT

Are you running Access on a Windows Vista peer to peer network? That is the back end MDB is on a Windows Vista machine. And the front end is also on a Windows Vista box. Wierd things are happening? Performance is terrible or Access crashes? The problem is Server Message Block 2.0 (SMB). (This sounds somewhat similar to the OpLocks corruption problem a number of years ago.)

Then you need to either use a Windows XP or other system as the server or get the recently released hotfix. See the KB article Issues that you may encounter when you open or are using a Microsoft Office Access database on a computer that is running Windows Vista

You're running the database on Windows Longhorn Server? On a beta operating system? You're much braver than I am. However that same article applies.

Interesting bug in column headings of list boxes in Access 2007

Tony — Mon, 14 May 2007 19:55:00 GMT

A client asked why some code behind a list box suddenly started updating 57386 records in a table instead of the expected 38 records. Turns out the problem is a difference in behavior between Access 2007 and previous versions of Access. The following code selects all 38 records in the list box in A2003.

    For lngLoop = 0 To Me.lstUnreceivedPieces.ListCount - 1
        Me.lstUnreceivedPieces.Selected(lngLoop) = True
    Next lngLoop

However in Access 2007 the above code also selects the heading line. You can visually see the difference on the list box between Access 2003 and Access 2007.

Now strictly speaking the code should've started at 1 and not 0 to avoid selecting the heading line. But that wasn't a problem in A2003 and earlier. And I can certainly see a developer not even thinking about this issue.

So later on his query then had the following as part of his WHERE clause

Pieces.pID=pID OR Pieces.pID=52811 OR ...

instead of

Pieces.pID=52811 OR ...

Needless to say all 57386 records were updated.

So now we need to go into every list box in every app and double check this behavior.

This bug has been reported to Microsoft.

Update: Ken Snell, fellow MVP couldn't recall where he got the ideo from in the first place but the suggestion is changing the For line to read

For lngLoop = 0 - (Me.lstUnreceivedPieces.ColumnHeads) To _
(Me.lstUnreceivedPieces.ListCount - 1)

Nice trick that.

Note that this problem happens whether the Multi Select property is simple or complex.

Update: You can download the file and open it in both A2003 or earlier and A2007 to see the difference.

Update: You can either search through all your code looking for .selected or run the following code to open all forms with list boxes in design view.

Sub FindFormsWithListBoxes()

    Dim doc As Document, ctl As Control, Count As Long, Form As Form
    Dim db As Database, ListBoxOnThisForm As Boolean
   On Error GoTo FindFormsWithListBoxes_Error

Set db = CurrentDb

    For Each doc In db.Containers!Forms.Documents
        ListBoxOnThisForm = False
        DoCmd.OpenForm doc.Name, acDesign

        Set Form = Forms(doc.Name)
        For Each ctl In Form.Controls
            If ctl.ControlType = acListBox Then
                ListBoxOnThisForm = True
                Exit For
            End If
        Next ctl
        If ListBoxOnThisForm = False Then
            DoCmd.Close acForm, doc.Name
        Else
            Count = Count + 1
        End If
tagNextForm:
    Next doc

MsgBox "The " & Count & " forms with listboxes have been left open in design view."

On Error GoTo 0
Exit Sub

FindFormsWithListBoxes_Error:
    Select Case Err.Number
    Case 7784 ' This form or report is already being designed as a subform or subreport
        msgbox doc.Name & " is a subform on an already open form so is being ignored."
        Resume tagNextForm
    Case Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FindFormsWithListBoxes of Module Module1"
    End Select
    Exit Sub
    Resume

End Sub