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.