Tony's Microsoft Access Blog

File Compression using the OS and NTFS file system

Thanks to a question in the newsgroups I did some research and updated the Microsoft Access Compatible Compression Solutions page with the below File Compression using the OS and NTFS file system section  (And I renamed the web site page from Microsoft Access Compatible Compression DLLs, OCXs, etc.)

Given that I do automated backups of the BE in some of my systems I'm thinking I will investigate this a bit more in the future when I have some time.  For example I will rename the backend MDB into a backup folder and then compact it back into the original backend folder.  However I append the date in yyyy-mm-dd format to the file name.  And then delete files older than the last ten copies.  Why ten?  <shrug>  Arbitrary number.

Hmm, I"d have to test this in a server environment too as the user may not have permissions to set that flag on a server.  Maybe.  Who knows.  

File Compression using the OS and NTFS file system

See Visual Basic > Files Directories Drives - Compress-uncompress an file or directory for sample code.   See File Compression and Decompression (Windows) at the Microsoft MSDN site for more details.  All you need to do is to set the compression state of the file and the OS will compress the file for you.

Note that I haven't tested this code myself.

The Crazy Ants That May Eat NASA

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

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.

Computer Game's High Score Could Earn The Nobel Prize In Medicine

Via the Geeks are Sexy blog Computer Game's High Score Could Earn The Nobel Prize In Medicine.  (Note the fold.it Web site may be clogged.)

"Gamers have devoted countless years of collective brainpower to rescuing princesses or protecting the planet against alien invasions. This week researchers at the University of Washington will try to harness those finely honed skills to make medical discoveries, perhaps even finding a cure for HIV.

A new game, named Foldit, turns protein folding into a competitive sport. Introductory levels teach the rules, which are the same laws of physics by which protein strands curl and twist into three-dimensional shapes -- key for biological mysteries ranging from Alzheimer's to vaccines."

This is an awesome idea that could make a huge difference to mankind.  Seriously. 

Bug in ADO, SQL Server and latest OS SPs

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.

Permission Denied

Short story:

I was trying to copy the backend MDB from the front end but hadn't closed a database variable pointing to the backend MDB.  I used the presence and absence of the backend LDB file in Windows Explorer to track down the problem.

Long story:

The client wanted to email PDF copies of daily field tickets in his pickup truck while on site to his clients using the cell phone system data network. Given that his laptop would be out in the field and subject to hazards including theft I decided to also give him the ability to email a zipped copy of the backend MDB to the office. As this is a single user app I wasn't concerned about other folks also access the backend MDB. So my code closed all open forms and copied the backend database to the temp folder while giving it a name that ended in the yyyy-mm-dd format.

After I installed some updates on their systems it starting giving them the permission denied message. Well, they were running on Windows Vista and I was on Windows XP. So, despite it having previously worked on their system, I decided that it was a weird Windows Vista problem and said I'd figure it out later. I did not test it myself on my system at that time. Cue ominous music.

Later, just for grins and to avoid the time spent in copying the 9 Gb Virtual PC hard disk file to make a test install, I decided to test it myself. Oops, I got the same error. Ah hah, not Windows Vista. Cue dumb *ss assumptions music with Goofy hyuk hyuk laughter.

Turns out I had added some code to update the backend table, field, indexes and relationships when I gave them a new version. So I opened a database object against the backend MDB to do these updates but I forgot to close the database object.

Now I always use a GlobalOptionsHidden form to store those Global Options variables and reference through out my application. These are things such as Goods and Service Taxes percentages, account numbers, default settings, email address, email server name and so forth. But when I started the app, got to the main menu and I closed the GlobalOptionsHidden form I saw that the LDB lock file was still present for the backend MDB in Windows Explorer.

As the main menu did not have a record source or combo box referencing tables I knew it couldn’t be the problem. Although I could've double checked this by closing that form and looking to see if the backend MDB's LDB file was still present.

It took a bit of debugging to figure out where the problem was. But I could track it by running Windows Explorer looking for when the LDB file was created in the code which started when the MDB was opened. Once I saw the OpenDatabase I went looking for the close database in the same subroutine and discovered I had forgotten it.

I emailed the client the new copy of the front end MDB and phoned them to tell them I was a dumb *ss.  They just chuckled and were happy that it was working.

They figure this app will save the company owner about an hour a day in hand writing.   Figuring his time is worth $100 an hour his system will pay for itself in less than two months or so.  Including the cost of the laptop.

Here's an interesting and much more obscure variation of that error - Debugging Access “Permission Denied” Errors

Awesome application description

I read the Jack Cowley's Rodeos posting on fellow MVP Steve Schapel's Real World Access blog.  His blog is "a series of articles about where Microsoft Office Access applications have found a real-world niche."

I felt the description of the tasks behind organizing a rodeo was very well done.   I can just see the volunteers in behind muttering under the breaths as they get the last minute phone calls and such.   And hey, the photo was down right genius. 

Duplicate names and birth dates

Ken Sheridan, long time resident of the Microsoft Access newsgroups, posted a very interesting paragraph in a thread title Duplicates

A year or so ago I witnessed a remarkable coincidence when attending a  hospital clinic.  Two patients, both female, both with the same date of birth  and both with the same names were attending.  I happened to overhear the staff talking about it and it appeared that the 'key' used for identifying  patients was a combination of name, gender and date of birth, so the staff were having difficulty distinguishing the medical notes of one from the  other.  One wonders what might have happened if they hadn't spotted the problem!

My brother, who shares my last name Toews, has a very troublesome time whenever stopped by the police, which seldom happens, or whenever he crosses the border into the U.S.A.  Turns out he has the same first, middle and last name and birth date with a Canadian who has a lengthy criminal record.  My brother now adds an hour to his estimated border crossing times.   Although his last crossing they must've had a photo of the bad guy.  He was being interrogated in a side room when another US Border guy came in and told them that it was someone else.

Of course then there's George Foreman's five boys named George.

The point to my posting being that you can't use name and birth date as a unique key.    This also goes for corporation names.   There could conceivably be duplicate names in provinces and states as these are usually provincially/state incorporated.   Also if you are dealing with different branches of the same company you will likely want to include city and province in your inquiry screens.

Deploy Access 2003 runtime with SP2

References

Paul asked

"I sometimes get frustrated that Access does not always update references between versions...for example a calendar control or Outlook reference"

1) Use a calendar form or the API calls which is what the calendar control uses behind the scenes.  See the Calendar Tips page at my web site

One alternative is MonthCalendar is a completely API generated Month Calendar derived directly from the Common Control DLL.  There are links to several downloadable calendar forms at my web site.   As these are forms you can also do anything with them you want.

You can also use the calendar form which comes in the Access <insert your version here> Developers Handbook by Litwin/Getz/Gilbert, publisher Sybex  www.developershandbook.com.  These books are well worth spending money.  Every time I open one I save the price of the book.

Duane Hookum has an awesome and simple calendar in a report.  See the Calendar Reports sample. 

2) Late binding means you can safely remove the reference and only have an error when the app executes lines of code in question.  Rather than erroring out while starting up the app and not allowing the users in the app at all.  Or when hitting a mid, left or trim function call. 

This also is very useful when you don't know  version of the external application will reside on the target system.  Or if your organization is in the middle of moving from one version to another.

For more information including additional text and some detailed links see the "Late Binding in Microsoft Access" page.

Background colour of forms

In a comment to my Multi Row Tab control posting Diana asked:

"Just curious, is your form really that blue color?  Is it in production?  How do your users feel about that color?  Would you design a web page that color?

I'm always curious why Access developers pick colors for the background of their forms like pink or bright blue or whatever.  I guess I always think of my forms like web pages and design accordingly."

This is the only time I've ever changed the background colour of a form and was due to a client's comment.  Users were getting confused between the Equipment form and the Service form in my Granite Fleet Manager as they had a striking similarity.   I also realize the colour blind folks, generally males, will have problems with this.  Not sure what can be done in that situation.  (Maybe colour blind males can read better than non colour blind males.  <smile>)

I might do the same thing for a web page in a similar circumstance.

I haven't had any comments, negative or positive on these forms.