Tony's Microsoft Access Blog : bug, Access

Be aware of "Privacy Manager for HP Protect Tools"

Tony — Sat, 03 Dec 2011 03:28:14 GMT

A client of a fellow Access MVP was having "Too many databases open" errors.

Several users reported that Access was hanging. AppHangB1 Access

That thread in turn referenced Access 2007 hangs/stops responding

Removing Privacy Manager for HP Protect Tools solved these problems.

Access 2010 Compact & Repair breaks autonumber seed if index is descending

Tony — Thu, 18 Aug 2011 01:14:49 GMT

While this is very unusual having more than 1000 records in an Access 2010 MDB with the autonumber index in descending sequence a compact and repair loses the seed and the next record inserted fails.

Note this is the Microsoft partner forums so if you haven’t registered as a partner you won't see this.

http://social.microsoft.com/Forums/en-US/partnerofficeaccess/thread/9dc92ff4-c5a5-47c5-ad8c-9590d3eb8e69

I have reproduced this behavior myself.

(I sure would like to know why 1000 records is required to repro this though. <smile>)

This behavior also happened in Jet 4.0 for a while. AutoNumber field duplicates previous values after you compact and repair a database

Bug with Access and SQL Server: Erroneous #Deleted bug on Insert with null values

Tony — Sun, 27 Jun 2010 23:04:35 GMT

When a new row is inserted into a view that uses identity but contains null values for at least one field and Access' subsequent attempts to locate the new row uses "a_field = NULL" and thus will always fail to find that row and return #Deleted.

As viewed in this thread Access 2007 bug with SQL Server Back End, a user AVG has an open case with Microsoft and has been told that it is a bug. Fellow Access MVP Ben Clothier can reproduce this in Access 2003 and against a base table as well so it's not restricted to views only. Because the scope includes even plain old tables this can be problematic and very confusing due to its intermittent nature.

Paragraphs added: "and against a base table as well so it's not restricted to views only. Because the scope includes even plain old tables..."

The tables should be qualified to Non-SQL Sever linked tables. This occurs because from I can tell, Access will only look for keyword "identity" against linked tables that are said to be from SQL Server. Any other ODBC linked table will be more vulnerable to that bug because Access will not make a call to "SELECT @@identity;" if it's not a SQL Server linked tables. This bug occurs whenever Access has to fall back on the last resort of comparing every fields. End added

As the user AVG noted, he was following advice from MSDN article Optimizing Microsoft Office Access Applications Linked to SQL Server by Andy Baron regarding using a view and INSTEAD OF trigger:

Thanks to Ben Clothier for writing the above summary and AVG for finding the problem and reporting it in the newsgroups. (I’ll take a small amount of credit for urging AVG to post the problem when he was asking about problems in SQL Server.) Fellow newsgroup denizens Banana and David W. Fenton did a great job with suggestions in that thread.

An amusing bug – Report with 36505 pages has negative page numbers

Tony — Wed, 16 Jun 2010 22:23:03 GMT

A most amusing thread ms access negative page numbers

“I have an access report that generates 36505 pages (un filtered, and about half of each page is taken up by group headers and page headers) , though the footer at the bottom of the report page says "36505 of -29031".”

Some good sleuthing found that the Pages variable is an integer while the Page variable is a long. I can just see the discussion in the Microsoft Access product group. “But nobody will every have a report longer than 10,000 pages” <smile>

Also there is a work around for this problem in another posting. Well, I suspect if you hit 65K pages you will run into the problem again but hey, no report will ever bet that large.

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

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

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.

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