Microsoft Access Inaccessible

I recently copied a Microsoft Access data base file from my Windows XP/Access 2003 desktop pc to my Windows 7/Access 2010 laptop.

My first attempt to open the Access file failed miserably. I encountered the following error message:

Undefined function 'Date' in expression.

I had set my data file to automatically open a form with several underlying fields that do in fact draw upon Access's standard Date() function. I was thoroughly puzzled why such a basic function that had worked for years in Access 2003 and prior versions now failed in Access 2010.

I googled the problem and stumbled upon a variety of proposed solutions with the following mixed results.

1. Switch to Now() function

I originally had two underlying queries in which the Date() function occurred a total of five times. I changed all five occurrences to Now() instead of Date().

Voilà! The file now opened without a hitch, and the computations appeared to be correct. However, this "solution" left me unfulfilled. It was tantamount to sweeping the problem under the rug because it begged the fundamental question: why does the basic — and in this case, more appropriate — Date() function fail to work in the first place?

2. Copy elements to new database

Several contributors posting on various blog sites recommended creating an entirely new database in Access 2010 and then copying and pasting all elements from the old defective file to the new one. Based on my later findings, I came to understand how this might work. However, I did not personally try that approach because I simply had too many tables, queries, forms, reports, macros, and modules to make it practical to copy so many objects one by one.

3. Decompile/recompile

Several other writers recommended this process to fix the Visual Basic for Access (VBA) code's compiled state. Decompiling discards all the old VBA compilations, and leaves just your VBA source code. You'll need to then recompile again to return to a compiled state.

After decompiling/recompiling, you can also invoke a separate Compact and Repair process to compress the data tables. That additional process reportedly has no affect on the VBA code itself but might significantly reduce the size of your database.

Here are the steps to decompile and recompile:

• In the "Windows | Start | Run" command line, type the expression "msaccess.exe /decompile"
  You might have to type the full path name to Access instead of just masccess.exe. For Access 2010, that would become

  C:\Program Files\Microsoft Office\Office14\MSAccess.exe /decompile

You could also create a shortcut with the same command line for repeated future use.
• From the resulting Access window, open the database you want to recompile; allow trusted authority for Access 2003 or later.
  
  Important note: If you have any startup code in your database, you should hold down the shift key to bypass the startup code execution.
• Select the "Database Tools | Visual Basic" menus.
• In the resulting "Visual Basic for Applications" window, click the "Debug | Compile..." menus.
• You may then click the "File | Save" menu options to save your work.

(While you are at it, you might want to re-open the database and compact it. The location of the Compact command varies by Access version. In Access 2010, simply select the "Database Tools" tab. The "Compact and Repair Database" button is the first button on the far left.)

If it were not for bad luck, I would have no luck at all. I could not even do the decompile! I created the prescribed shortcut successfully, but when I attempted to use it to open the database in question, I encountered the following error:

Your Microsoft Access database or project contains a missing or broken reference to the file 'msado21.tlb' version 2.1.

To ensure that your database or project works properly, you must fix this reference.

The compile/recompile approach failed in the short term, but the error message provided the clue that ultimately yielded the effective solution.

4. Fix Library References

I found at least two ways to approach fixing library references: (1) editing the original file on my old XP/Access 2003 computer before copying to the file to my newer Win 7/Access 2010 laptop and (2) editing the file directly on the the Win 7/Access 2010 machine after copying the file from the older machine with the older version of Access.

a. Edit file before transfer

After I opened the original file on my XP/Access 2003 desktop, here are the steps I followed:

• Right-click on the open area in any one of the object types (tables, queries, forms, reports, pages, macros, or modules; it doesn't matter which)
• In the resulting menu, click the "Visual Basic Editor" menu option; it should be the last entry
• In the resulting window entitled "Microsoft Visual Basic," click the "Tools | References" menus
• Deselect (uncheck) the "Microsoft ActiveX Data Objects 2.1 Library" entry (file 'msado21.tlb' cited by the error message from Step 3 above)
• Click the "OK" button to save your work
• Exit Access

I then copied the edited database file to Windows 7/Access 2010. Lo and behold: it worked just fine! The problem was clearly an invalid library reference to an ActiveX library not present on the Win 7/Access 2010 computer.

Just for grins and giggles, I then went through the prescribed mechanics of decompiling/recompiling my database, but that was a hollow exercise at best. The decompile/recompile was superfluous, of course, because the problem had already been resolved by deleting the faulty "References" entry. In fact, I had to correct the reference problem *before* I could even execute the decompile/recompile. There is a self-fulfilling prophecy if there ever was one.

b. Edit file after transfer

Upon opening the file, I clicked my way past the "Undefined function 'Date' in expression" error and then clicked the "Stop All Macros" button. To access (pun intended) the Reference library in Access 2010, perform the following steps:

• Select "Database Tools" tab
• Click "Visual Basic" button
• Select "Tools | References" menu

Unchecking the "Microsoft ActiveX Data Objects 2.1 Library" reference generated the following error message: "Error in loading DLL." I nevertheless clicked the OK button to save my (de)selection. I then saved the file, and when I re-opened it again, amazingly enough: everything appeared to work properly.

c. Cleaning up loose ends

Even though my file now worked after implementing both methods, I was still left with the old "Microsoft DAO 3.6 Object Library" reference from Access 2003. This was a less-than-ideal configuration because of the differences in the default libraries for Access 2003 compared to Access 2010. For Access 2003, here are the default reference libraries:

• Visual Basic for Applications
• Microsoft Access 11.0 Object Library
• OLE Automation
• Microsoft DAO 3.6 Object Library
• Microsoft ActiveX Data Objects 2.1 Library

For Access 2010, the default reference libraries are as follows:

• Visual Basic for Applications
• Microsoft Access 14.0 Object Library
• OLE Automation
• Microsoft Office 14.0 Access database engine Object Library

I have already addressed the issue of the problematic "Microsoft ActiveX Data Objects 2.1 Library" reference and how to solve it. The first three library references seem to automatically adjust themselves when the Access 2003 file is opened in Access 2010. Let us focus instead on the remaining fourth entry listed for each program: "Microsoft DAO 3.6 Object Library" (Access 2003) versus "Microsoft Office 14.0 Access database engine Object Library" (Access 2010).

As an experiment, I tried to activate both the "Microsoft DAO 3.6 Object Library" and the "Microsoft Office 14.0 Access database engine Object Library" reference libraries at the same time. My attempt failed with the following error message:

"Name conflicts with existing module, project, or object library."

Clearly, those two references conflict. You may activate one or the other, but not both. From everything I read, it is much better to go with the "Microsoft Office 14.0 Access database engine Object Library" reference that is native to Access 2010.

Regardless of which solution we use to solve the basic problem (step 4a or 4b), it is probably a good idea to restore the proper library reference to the Access 2010 file rather than allow the Access 2003 reference to linger. To address this situation, go to the Access 2010 references section one last time (see step 4b above), uncheck "Microsoft DAO 3.6 Object Library" reference, and instead check the "Microsoft Office 14.0 Access database engine Object Library" reference. Save the file and reopen: everything still works, and now you have all four default Access 2010 references properly selected.

I suspect that this is the same net effect from Method #2 (Copy elements to new database) discussed above. To refresh your memory, that process involved opening a fresh database with the four default Access 2010 references and then copying each object element (database, form, macro, module, query, and report) from the old file to the new. The result is a new file whose elements duplicate those from the original Access 2003 data file but with all of the library references associated with Access 2010.

Another potential pitfall

More than once during the course of my experimentations, I copied my original Access 2003 file, edited or otherwise, to my Windows 7/Office 2010 laptop, and when I tried to open the file in Access 2010, I encountered two problems:

• A window labeled "Macro Single Step" appeared with the following field values:
  
  
  • Macro Name: AutoExec
  • Action Name: RunCode
  • Arguments: setActionQueryPrompt (false)
  • Error Number: 2001
• An orange ribbon across the entire Access window reading as follows:
  
  
  "Security Warning   Some active content has been disabled. Click for more details."

When I did click for more details, I encountered a window that said:

"... The following content has been disabled: VBA Macros ..."

Fortunately, there was a button within the orange ribbon that said "Enable Content." Clicking that button did successfully re-enable macros, so all was well once again.

Upon further reflection, it dawned on me why this problem occurred. I had copied the file to a folder where I had not relaxed security restrictions. To correct this oversight by authorizing Access files to execute from designated folders, it was necessary to perform the following steps:

• Open Access and then click the "File | Options" menus
• In the resulting Options window, click the "Trust Center" menu item
• Click the "Trust Center Settings" button
• In the resulting "Trust Center" window, click the "Trusted Locations" menu
• Click the "Add new locations..." button
• In the "Path" field, type the path an name of the folder you wish to allow. You may check the subfolders option if you wish to allow subfolders, and you may also type a meaningful description of the folder in the "Description" field
• Click the "OK" button to save your work

If you are careful to open and execute Access files from folders you have designated in the Trust Center, you should not encounter the orange security notification ribbon as I did.

Finally, you might want to consider applying this same process to Excel, Word, and PowerPoint as well.

Postscript

On my Window 7 laptop, I visited the folders

"C:\Program Files\Common Files\System\ado\"   and

"C:\Program Files (x86)\Common Files\System\ado\"

Both files contained just one ActiveX file, msadox28.tlb, that clearly designated version 2.8. However, when I went back to Access and attempted to select "Microsoft ActiveX Data Objects 2.8 Library" from the References list, I encountered the following error message:

"Error in loading DLL"

Recall that this is the same error message I encountered when I deactivated the "Microsoft ActiveX Data Objects 2.1 Library" reference in step 4b above. Needless to say, Access 2010 did not retain my selection even though the indicated file was clearly present. I can only speculate that there is yet another unresolved internal conflict present.

How typical of Microsoft. I finally fix one problem after hours of research only to encounter yet another obstacle. Because I have no immediate need for any ActiveX module, I will set this new problem aside for another time. For now, I can only shake my head in resignation.

Toshiba Sleep-and-Charge

The Problem

The battery to my son's Toshiba laptop computer was discharging far more rapidly than my other two Toshiba laptops, even when completely turned off and the power unit unplugged. Recently, whenever I turn the laptop on after 2-3 days of disuse, the battery level was down to 80% or so. I have never seen the other two computers below 95% even under the same circumstances.

Proposed Solution

When I googled the problem, at least one blogger reported the issue as being caused by his laptop's Sleep-and-Charge feature. Sleep-and-Charge ports allow users to charge smart phones, MP3 players or other portable electronics even when the laptop itself is turned off. It so happens that my new USB external hard drives for both my son's and my daughter's laptops do not have an a/c power supply; instead, they draw power from the laptop itself.

If you have a Toshiba laptop, here is how to access Toshiba's Sleep-and-Charge utility:

• Click on the Windows Orb
• Click "TOSHIBA | Tools & Utilities | Sleep Utility" menus
• Uncheck the Sleep-and-Charge box

To see if that writer's suggestion even applied to my case, I disabled Sleep-and-Charge on my problem machine and then turned that laptop off with the intention of checking back in a few days.

Even if the battery were to then retain its charge, that raises the question: why just on that one laptop? Why do the other two Toshiba laptops remain unaffected?

The perfectly logical explanation suggested itself when I examined the settings on each machine: the Sleep-and-Charge feature was evidently turned off by default on both my daughter's and my own laptop, whereas it was apparently turned on by default only on my son's. The fact that only my son's laptop has this battery problem strongly suggested that Sleep-and-Charge might indeed be the cause of the problem.

Test Results

After I changed the settings and turned off "Sleep and Charge," I let the machine sit for a full 24 hours with the power off. When I finally did turn it back on, the battery had a full 100% charge remaining, not the 80-85% I had been experiencing. I then allowed it to sit for 48 hours with the power turned off. When I turned it back on after two full days, the battery still had a full charge of 100%. Both tests seem to indicate that the problem is indeed solved.

Final Observation

Upon further research, I discovered that on Toshiba laptops, Sleep-and-Charge USB ports are marked by a colored USB port (blue on my computers). I never knew the significance of the colored USB ports. In retrospect, it explained some phenomena that I did observe: not knowing any better, I frequently plugged my external USB hard drive into that colored port. Whenever I did, I noticed the hard drive's light on even after I turned off the laptop altogether.

In my ignorance, I never made the association. I should have, because the battery reading was really low when I turned the laptop on again after several days. However, I learned the wrong lesson: whenever I could remember, I used the "Safely Remove Hardware" icon in the system tray to disconnect my external hard drive before logging off. That "solution" apparently only papered over the real problem. In the immortal words of Homer Simpson, "Doh!"

On my own Toshiba, there are only three USB ports, and none of them are colored. The user manual says any Sleep-and-Charge port will be marked by a vertical lightning bolt icon, but I cannot make out any lightning bolt on any of the USB ports. They all seem to have the standard USB icon, but it is very difficult to make out such tiny, black-on-black icons. I do not know if activating Sleep-and-Charge will activate all three USB ports or not - and I am not about to find out!

Postscript

Even though I solved the problem of the run-down battery by disabling Sleep-and-Charge, I still have to make sure I unplug the USB external hard drives before I turn the machine back on. If I make the mistake of leaving the hard drives plugged in during boot-up, the laptop cycles endlessly and never does boot up. I checked the BIOS and verified that the internal hard drive is checked first in the boot sequence and the external USB hard drive is last. It remains a mystery why the boot process still hangs, but that is a problem whose solution will have to wait for another day.

Google Gmail Filters: Unleash the Power

Problem

For many weeks, I was receiving at least one and later two e-mails a day from a spammer using the comment section of my Google blog site to lure me into visiting his or her many web sites. That might not sound like much, but that amounted to 30-60 unwanted e-mails per month. While I welcome genuine comments about my blog postings, I do not welcome people trying to generate business for their commercial web sites or even worse, trying to sucker me into visiting a malicious, virus-infected site.

I have been using Google e-mail filters for a long time, but this situation posed a new problem. All offending e-mails contained a variation on a common theme: some mention of a blog site, web page, or home page followed by a url. I certainly did not want to create a separate filter for each individual phrase. Instead, I needed to consolidate those multiple conditions into a single filter field, something I had never done before.

Solution

To access Google's Gmail filters, open Gmail and then:

• Click the "Settings" cog near the upper right corner
• Select the "Settings" menu option from the resulting pull-down menu.
• Select the "Filters" label from the menu options across the top
• Click on the "Create a new filter" link at the bottom

There are five e-mail filtering categories: From, To, Subject, "Has the words" and "Doesn't have." You may create entries in more than one category suitable for your needs.

All of the obnoxious e-mails, originating as they did from my Google blog site, contained the sender

"noreply-comment@blogger.com"

so that became the sole entry in my filter's "From" field.

The key, of course, becomes the "Has the words" field. I needed to include several different alternatives utilized by the spammer. After poking around Google search for a little while, I found something that worked. The key to multiple values is the following syntax:

enclose the entire expression in { } curly brackets, and then within those brackets, enclose each individual expression in " " double quotes.

The curly braces indicate an OR condition where any one of the conditions will trigger the filter. Simple parentheses ( ) indicate that all of the specified conditions must be present, not just one. As an alternative, users can forego the braces and parentheses altogether and instead place AND or OR between the conditions (making sure to preserve the capitalization). Both alternatives work equally well.

Bingo! The following example worked like a charm:

{"my web blog" "my weblog" "my blog" "my web page" "my webpage" "my home page" "my homepage" "my site" "my web site" "my website"}

The following example also works:

"my web blog" OR "my weblog" OR "my blog" OR "my web page" OR "my webpage" OR "my home page" OR "my homepage" OR "my site" OR "my web site" OR "my website"

I lean towards the first example if only because it is slightly more compact.

In addition to my sender criterion, I also placed the above compound expression into my "Has the words" field, set my disposition instruction to delete all e-mails, and saved the filter settings. Now, instead of receiving one or two offending messages each day, I have not received a single one in over a week. The filter's syntax clearly works.

[Side note: I have since relaxed my disposition to send the e-mails to the trash bin instead of immediate deletion. By doing so, I can monitor the trash bin occasionally to determine if the e-mails are still arriving while simultaneously confirming that my multiple conditions are indeed still working properly.]

Postscript

Google apparently imposes no numerical limits on its users. Even so, it seems far more economical to create one filter with multiple conditions rather than multiple filters each with one condition. In general, it is far easier to manage a smaller number of filters even if those filters have multiple conditions.

By contrast, Yahoo's mail limits its e-mail users to 100 filters. (Cheer up: only a few years ago, the limit was just 20.) Yahoo offers four categories (sender, recipient, subject, and body) on which to filter. For each of those four options, users may select "contains, "does not contain," "begins with," or "ends with."

Alas, despite some claims that Yahoo's conditions recognize Boolean operators such as "AND" and "OR," I could never get them to work. I tried the OR condition both lower case and upper case (as in Google searches); with and without the brackets (and plain parentheses, also like Google searches); and with and without the double quotes. All combinations failed miserably.

To me, the inability to express multiple conditions within a filter is a fatal flaw, especially when you have only a limited number of filters available.

Microsoft: Essentially Secure From Its Own Self

Digging Myself Into a Hole

On 26 September 2012, Microsoft released an update to its own Security Essentials anti-virus software under the moniker KB2754296. It was classified as an "Important" update. In the past, almost all "Important" updates are checked by default, so I was a bit surprised when this update did not have the usual check mark. Little did I know that this was only a harbinger of trouble to come.

I tried to implement the update several times, but all attempts failed. I then began trying various solutions I read about when I Googled problems associated with KB2754296. Unfortunately, I did not keep a record of my various attempted fixes because I did not expect to get so wrapped around the axle as I eventually did.

In the course of my failed experimentations, I did notice that the shortcuts on my daughter's machine for the Security Essentials executable file "msseces.exe" pointed to the parent folder

"C:\Program Files (x86)\Microsoft Security Client\"

which as we will see shortly is not correct for a computer with a Windows 7 64-bit operating system. I must have erroneously installed the 32-bit version of Security Essentials at one time, an error which might have contributed to my initial downfall.

Probably the worst thing I did was follow a recommendation to delete any and all registry entries associated with Security Essentials. Big mistake. One of my machines degenerated to such a state that not only was I unable to update Security Essentials, I also could not uninstall the software with the ultimate goal of re-installing it anew for a fresh start. In fact, I could not even open Security Essentials to access any settings. In short, I became totally distraught because I had a machine whose "security" system was in such disarray that it rendered the machine next to useless.

Perhaps worst of all, my daughter needed to download some images of the American flag for a school project, and while we found many pictures she liked, we could not successfully download or save any of them anywhere: not onto the laptop's hard drive or onto her external USB thumb drive. Stymied at every turn.

Climbing Out of the Hole

In sheer frustration, I tried using the most recent restore point to restore my system to the state it was in three days before. Whew: that seemed to help. I lost three days of file and system updates, but at least I had a functioning security system back, outdated though it might still be.

In the course of Googling the KB2754296 update problem, I stumbled upon one particular web site that contained the following paragraph:

There are quite a few updates that seem to get blocked by security S/W. There are times disabling a security app will allow the update to take place. The better alternative in these cases may be to download the update in question from MS and save it to your PC, disconnect from the internet, then disable your security apps prior to running the update. Once the update has completed you can enable any security apps you disabled, then re-connect to the internet.

With no other options remaining, I decided to give this approach a try out of sheer desperation.

I first visited the Microsoft Security Essentials download site and downloaded the file applicable to me: the Vista/Windows 7 64-bit option. There were also files for Windows XP 32-bit and Vista/Windows 7 32-bit.

Download whichever file applies to your situation, double-click the downloaded file, and let the fun begin. You should soon encounter a window that says:

The feature you are trying to use is on a network resource that is unavailable.

Click OK to try again, or enter an alternate path to a folder containing the installation package 'epp.msi' in the box below.

After many failed attempts and much time and effort to research the proper location, I was able to use the "Browse" button in that same window to navigate to the following folder:

C:\Program Files\Microsoft Security Client\Backup\amd64     (64-bit)

For 32 bit systems, the applicable folder reportedly is

C:\Program Files\Microsoft Security Client\Backup\x86     (32-bit)

And in 20-20 hindsight, for my apparent misguided installation of the 32-bit version of Security Essentials on a 64-bit machine, the epp.msi file of interest was probably in folder

C:\Program Files (x86)\Microsoft Security Client\Backup\x86     (32-bit)

In any case, armed with the correct location of the troublesome epp.msi file, I had a eureka moment: the process now ran to completion! The new version installed successfully.

I felt an immediate surge of relief. My almost inoperable machine was back to what passes for normal. Both machines were now able to perform Security Essentials and other Windows Updates, including the laptop that could not even open Security Essentials before I started.

Fittingly enough, after my installations were complete, the first Security Essentials definition update was number 666. How spooky is that???

Lessons Learned (Maybe)

In summary, here are a few lessons learned from this bitter experience:

First, be wary of updates marked as "Important" but that are not checked by default. That appears to be an indication that something might be amiss. Some bloggers claimed that this was an early release and not a mature, fully-tested, stable update.

Second, before attempting to install such updates, create a manual restore point and assign a recognizable name (e.g. "My manual update") to distinguish it from the many system created restore points, most of which carry the same generic "Windows Update" name. In addition, the default type for a system-generated restore point is "Critical Update" whereas the type will read "Manual" when you initiate your own restore point.

To manually create a restore point in Windows 7:

• Click on the Windows Orb icon in the lower left corner
• Click on "Control Panel | System and Security | System" if "Category" mode is set;
  Click "Control Panel | System" if "Large Icons" or "Small Icons" mode is set
• From the menu links to the left, select "Advanced System Settings"
• In the resulting "System Properties" window, select the "System Protection" tab
• Click the "Create..." button
• In "System Protection" window, type the customized name of your choice
• Finally, click the "Create" button to generate the restore point

Last but not least, if the update involves Microsoft Security Essentials, learn the correct functional location for the "epp.msi" file on your computer. There might be a version of that file in one or more temporary folders, but the relevant folder locations are those listed above, depending on the version of your operating system.

Postscript

All of this begs the question: why did Windows Update work more or less satisfactorily on my laptop but not on the laptops belonging to my two children? They are all made by Toshiba, and they all have Microsoft's Windows 7 64-bit Professional operating system. The only significant difference I can think of is that my laptop has an Intel Core i3-350M processor while theirs has an AMD Quad-Core A6 processor. Can a processor make so much difference in how the Windows Update function works? If so, yikes!

Most exasperating, however, are the continuing problems with Microsoft's updates to its own software. I previously reported on difficulties involving both Silverlight and .NET Framework 4. Now we must wrestle with yet another troublesome update, this time to Security Essentials. Microsoft's repeated failures are rapidly becoming strong arguments for adopting Linux.