InfoVeg

I think this has to do with the Excel library that is referenced in the Visual Basic Project. There are other ways to add a reference, which I should check into. Also, I should install only Excel and Access 2002 on a computer and check to see if I can run the entry tool like that. Currently, I've only tried it with these combinations:

Access 2002 / Excel 2003
Access 2003 / Excel 2003

Not sure if we want to try the Access 2000 / Excel 2000 stuff or not, probably IS worth it, if I can find the CDs for those programs. With a new office coming out soon, we are likely to need to continue to monitor this, or at least find a better way of finding the right excel library to use, or skip that functionality if it isn't available. Debugging error is not what we want to see!

Access 2003 / no Excel at all yields error:
Your CVS-EEP Data Entry database or project contains a missing or broken reference to the file 'EXCEL.EXE' version 1.5.

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

Show Help>>
The application you are attempting to use has a reference it cannot resolve to an object, type library, DLL, or external database. Either the object, type library, DLL, or database was deleted or its name has changed. Examine the Available references list in the References dialog box in the Visual Basic Editor (Tools menu) to determine if any action is required. If you did not create this application, contact the programmer or administrator of the system.
If the reference listed in the Available references list is preceded with "MISSING:" clear the check box to remove the reference if it is no longer required. If you still need to use the reference, clear the check box entry for "MISSING: <referencename>" in the Available references list, and then create a new reference to the file using the Browse... button. If this is an installed database application, you may need to reinstall or repair the application.
More information about this error message online.

------------------------------------------------
However, you cannot access the references from Visual Basic and it constantly throws errors for functions, even chr(13), so you have to kill the app manually (bad)!

Opening the database without that reference worked, and then removing the MISSING Excel lib made everything work that didn't need the excel lib. So, now I need to figure out how to add it manually from the code.

once a reference is broken, it seems unable to be fixed:
http://groups.google.ca/group/microsoft.public.access.setupconfig/browse_thread/thread/4b2231f8d74c0f0d/ae34027432077d20%23ae34027432077d20

how unhappy. The only solution, it seems to me, is to distribute the database without the reference to Excel and add that in once it becomes needed. If it can't be added, then tell the user to install excel. First, it should try to add Excel 2003, then Excel 2002, then Excel 2000, then it should move up from Excel 2003 to try to get whatever new fangled version that there is out there. If it still can't get anything at 100 or so, it could ask the user for help. Not sure how much help the user will be.

Excel functions need to be in their own module. You can't call anything in that module if the reference is missing/broken.

Need to be careful how stuff is linked to excel functions, as even a reference to a function that needs excel needs to compile and can't, so it's better to load excel earlier than later, but if it's not loaded, functions could still be affected. Best to use "eval()" to link to excel, as this doesn't affect compilation errors.

OK, better yet, don't reference excel explicitly like Excel.Something, instead just use "As Object"

This is slower, but better than causing compiling errors. Plus, this functionality is not used that often, and takes a bit of time to complete. I don't think we would notice the lesser speed.

Also now have a function that registers Excel automatically. Unfortunately, that has to complete before anything else can run. Perhaps I can try to run using eval("goRegisterExcel") but I don't think so.

updated code to not reference Excel.Objects and to call eval("function()") when something later down the line requires excel. I need to test that these functions barf correctly (ie with a message) instead of throwing compile errors when excel isn't available.

I have also added a feature that turns off the excel library referencing if you want to (ie to move to new machine) and will do so before distributing the db.

Need to check to see how my Access libraries move if they are in a weird place (ie install access to C:\Program Files\WeirdPlaceForAccess\" and see how it does.

Tried to get it to fail nicely even when the reference is broken, but it fights me, wanting everything to compile explicitly. May have to give up here, but should be graceful if excel doesn't exist.

To do: uninstall Excel and see how that goes.

It is happy with Excel uninstalled. Just prevents excel exporting from happening. Better than the built-in Excel export, in fact.

Word export was broken on Access 2003, but this is now fixed. Needs a test again on Access 2002. Works ok there, too.

I have now tested the setup with a myriad of possibilities, and everything seems to work ok. I will dereference Excel before distributing the app, and it will find Excel on its own, if it's on the computer. If it isn't on the computer, it gives sensible error messages.

The only problem is when it gets opened on one computer, then moved to another with a different version of excel. There is a manual fix that I could suggest, as well as you could go back to the first computer, dereference excel, and then move it along. I don't think that will be a big problem there. In any case, it dies modestly gracefully, telling you it needs to quit, ok, error, ok. it's gone. No killing apps or restarting computers needed!

TEST: DONE broken reference (with Excel)
TEST: DONE no excel
TEST: DONE excel somewhere weird - finds it even on a different drive.
TEST: DONE: access somewhere weird - finds everything ok.

Access 2003 x Excel 2003 : done LITU
Access 2002 x Excel 2003 ; done CHEY
Access 2002 x Excel 2002 ; done LITU
Access 2003 x Excel 2002 : DONE LITU

To do: make sure error messages are clear.

Excel is now dereferenced whenever the database is closed.

The messages look good, closing this bug.

Original Bugzilla ID was 2708