Question Problem Opening MS Access 2007

[Dick_in_Dallas]

I have a fully operational VB.NET program (called prjPathfinder) that uses MS Office XP Professional and its related Access database. Because of security concerns, I recently upgraded from Office XP Professional to Office 2007 Professional and Access 2007. I can tell Access 2007 is working because I can launch it from the shortcut on the desktop and run database queries. When I try to open Access 2007 from prjPathfinder, I get "Error 5: The type initializer for prjPathfinder.Upgrade Support threw an exception.", then it displays the first form in prjPathfinder without opening Access 2007.

Apparently there is a difference or differences in the requirements for opening Access 2007. What am I missing?

The database reflects the amateur packet radio node network of North America. prjPathfinder takes the GPS coordinates of the specified starting node, and the GPS coordinates of the specified destination node, and recursively finds its a viable path between the two nodes if a viable path exists. It uses an Excel object model to display the path in real time as it is finding it. The Office XP version works great. Except for upgrading the version of MS Office, there have been no other changes.

I have posted a very similar question on the Access forum, and they recommended I post one here as well.

 

[jmcilhinney]

Did you originally reference one of more Office libraries in your app? If so, did you remove those references and add new ones for the new Office version? Have you actually looked at the exception to see what the details are?

 

[Dick_in_Dallas]

prjPathfinder originated under Office 97 or earlier and evolved to Office XP. The bottom line is, I don't recall referencing one or more Office libraries in the app. Because of security concerns attributed to Office XP, I recently found it necessary to attempt this latest upgrade. If I had a succinct list of differences between Office XP Professional and Office 2007 Professional, it would narrow the spectrum of suspects. I will follow that idea and see where it leads. I am hoping someone else may have already done that and can share some insights.

It has already occurred to me that it would be more helpful to know the details about the exception that was thrown, rather than to simply know that an unspecified exception was thrown. As I rework that section of the code, I'll see what I can do to make that happen.

JMcIlhinney in Sydney sounds very familiar. I'm sure you have helped me in the past. Thanks! It has been quite some time since I was last on this forum, but this ain't my first rodeo.

 

[jmcilhinney]

I don't recall referencing one or more Office libraries in the app.

If you open the References page of the project properties, is there any Office application type library listed there? If there is then you should remove it and add it back for the version you want to use. If not then you must be using late-binding, in which case you might have encountered a breaking change between the Office versions. You can debug any code that performs any Office Automation to see where the code succeeds and where it fails.

 

[Dick_in_Dallas]

I may have found some relevant clues: They include four warning messages, as follows:
* The referenced component, AxMSComCtlLib, could not be found
* The referenced component, Excel, could not be found
* The referenced component, Microsoft.Office.Core, could not be found
* The referenced component, MSComCtlLib, could not be found

Correct me if I'm wrong: MS Office XP was a 32-bit app, whereas MS Office 2007 is a 64-bit app. There are certain areas in the source code that the editor will not allow me to change, because they are 64-bit code. Other areas in the source code can be changed with no problem.

So that I don't miss anything, the following are all the references from the References page of the project properties:
* ADODB
* AxMicrosoft.VBE.Interop.Forms
* AxMSComCtl 2
* AxMSComCtlLib
* AxMSComDlg
* AxMSFlexGridLib
* DAO
* Excel
* MDACVer
* Microsoft.Office.Core
* Microsoft.Office.Interop.Access
* Microsoft.Vbe.Interop.Forms
* Microsoft.VisualBasic.Compatibility
* MSComCtl2
* MSComCtlLib
* MSComDlg
* MSDASC
* MSFlexGridLib
* Scripting
* stdole
* System
* System Data
* System Drawing
* System.Windows.Forms
* SystemXML
* VBIDE

There is another warning: Max Warnings Exceeded. Almost all of these warnings are about Forms (which work exactly as intended) that have been declared obsolete, or about software related to obsolete forms that is also obsolete. One of our team members is looking into the best way to deal with the obsolete forms issue (if for no other reason than to reduce the number of warnings).

Another of our team members has successfully replaced FlexGrid with DataGrid in some of our other software. I may ask him to replace FlexGrid with DataGrid here, after everything else is working properly.

 

[Dick_in_Dallas]

I think I may have caused myself some more grief. I incorrectly defined a couple of variables at the top of the module. Now the compiler thinks the entire module is 64-bit code, and will not allow me to change anything in the module, including the incorrectly defined variables. I may be able to get a copy of the unmodified project from our repository server and start over, but is there a better remedy?

Also, the first warning says "There are updated custom wrappers available for the following referenced components: DAO, Microsoft.Vbe.Interop.Forms, VBIDE". I have seen other warnings like this one in the past, but I have no idea what I'm supposed to do about them. So far, I have ignored them. Please enlighten...

 

[jmcilhinney]

Office 2007 is not 64-bit. Office was not available in a 64-bit edition until 2010 but, even then, the vast majority of installations were still 32-bit, which is the default and Microsoft's recommendation.

There's no such thing as 64-bit source code. Source code is just source code. Only the compiled output is either 32-bit or 64-bit and then that applies to the entire output, i.e. the EXE or the DLL. The same project can output either a 32-bit or 64-bit assembly, depending on your settings. If you want to reference Office libraries then, in almost all cases, your project settings should be such that it generates 32-bit output in all cases.

As I said earlier, if you want an existing project to use a newer version of Office then you should remove all the old Office references and add them back again to reference the desired Office version.

 

[Dick_in_Dallas]

It isn't working any better yet, but I discovered one thing:

* The suffix for Access databases has been '.mdb' almost since Adam & Eve. It was '.mdb' under Office XP. Buried deep in the text of my book on Access 2007, it mentions in passing the suffix should be '.accdb'. Even if everybody but me knew that, I think textbooks on Access 2007 should state it for the record. I began suspecting the suffix was now different, but had to look long and hard to find what the current suffix should be. Why was that bit of important information so hard to find?

 

[Dick_in_Dallas]

The error message, "The type initializer for prjPathfinder.Upgrade.Support threw an exception." appears to have originated from Windows.Forms.Designer-generated code. I don't think I can change it to provide more information. However, if the code were working correctly, I wouldn't be getting that message in the first place. I think I need to make changes elsewhere in the program to fix the underlying problem. I'll keep looking...

 

[jmcilhinney]

It isn't working any better yet, but I discovered one thing:

* The suffix for Access databases has been '.mdb' almost since Adam & Eve. It was '.mdb' under Office XP. Buried deep in the text of my book on Access 2007, it mentions in passing the suffix should be '.accdb'. Even if everybody but me knew that, I think textbooks on Access 2007 should state it for the record. I began suspecting the suffix was now different, but had to look long and hard to find what the current suffix should be. Why was that bit of important information so hard to find?

Um, could you not just have looked at the file to see what extension it had? Office 2007 included new file formats for most applications, including DOCX for Word, XLSX for Excel, PPTX for PowerPoint and ACCDB for Access. All those applications can still open, edit and create files in the old formats, so it's not correct to say that Access databases used to be MDB files and are now ACCDB files. MDB files are actually Jet databases and Access was a GUI application created as a front-end for them. Until Office 2007, there actually was no such thing as an Access database. An ACCDB is genuinely an Access database while an MDB file is actually a Jet database. Access can handle them both.

 

[Dick_in_Dallas]

Early on, I commented out one line of code in UpgradeSupport to get rid of a single error that was inhibiting the debugging process. Things are now down to a single run-time error resulting from that line of code being commented out. I reinstated that one line of code and am getting the single error once more. It is 'Excel.Global is not defined.' I have attempted to define Excel.Global a number of times, but no matter how I have tried to define it, The ErrorList always responds 'End of statement expected'. How should I be defining Excel.Global?

 

[jmcilhinney]

Early on, I commented out one line of code in UpgradeSupport to get rid of a single error that was inhibiting the debugging process. Things are now down to a single run-time error resulting from that line of code being commented out. I reinstated that one line of code and am getting the single error once more. It is 'Excel.Global is not defined.' I have attempted to define Excel.Global a number of times, but no matter how I have tried to define it, The ErrorList always responds 'End of statement expected'. How should I be defining Excel.Global?

I don't do all that much Office development so I'm not overly familiar with the namespaces and types that are commonly used. That said, I'd expect that Excel.Global is a namespace that is expected to be defined in an Excel library. Where and how is it actually used in your code? That may give some clue as to where it's supposed to come from.

 

[Dick_in_Dallas]

Here's the entire UpgradeSupport module:
Module
UpgradeSupport





Here's the entire UpgradeSupport module:

Module UpgradeSupport
Friend DAODBEngine_definst As New DAO.DBEngine
Friend ExcelGlobal_definst As New Excel.[Global]
End Module

To reiterate, this was a working program with MS Office XP, and the only thing that was changed was upgrading to MS Office 2007. I believe the code to be generally sound.



















Friend DAODBEngine_definst As New DAO.DBEngine

Friend ExcelGlobal_definst As New Excel.[Global]
End
Module

 

[jmcilhinney]

I would take a look at the original project with Office XP and use the Object Browser to see where that Excel.Global type is defined.

 

[Dick_in_Dallas]

I would like to do what you suggest, but I'm not sure I can. My only computer with Visual Basic 2010 Express.NET installed no longer has MS Office XP; it has MS Office 2007 installed instead. My only computer with MS Office XP still installed does not have Visual Basic 2010 Express.NET installed. The 'Excel.Global is not defined' error showed up immediately after I pulled the copy of the working program off our repository server and tried to compile it. I think that is telling us Excel.Global was defined somewhere within MS Office XP, but is not defined within MS Office 2007.

I do have hard-copy listings of the original working program, but they do not include the UpgradeSupport module.