Two Problems Involving Access 2010 Runtime on an XP Machine

[Dan Hugos]

I am running an ACCDE using Access 2010 Runtime on an XP machine. I have two issues:



#1:

It apparently fails when the application hits code that tries to set up an ADO connection, and it then gives me this error message:

Class Does Not Support Automation or Does Not Support the Expected Interface

I have seen several posts here and elsewhere regarding this problem, but a haze of suggested answers. At least in this instance, maybe we can help out with a definitive answer, since Microsoft doesn't seem to give us one, at least that I can find.

I know that I can run some VBA code, because I can make an ACCDE out of a simple, "Hello World" database with a little bit of DAO code in it. I would agree with other posters that it's ADO that is giving me the problem and returning the error message. Also, another application runs fine in the Runtime environment.

That is, until it encounters problem #2:

#2:

I try to click a dropdown box with an expression as a RowSource: SELECT DISTINCT WireWeightSpecs.PartNo, IIf(IsNull([Bunches]) Or Trim([Bunches])="",[Strands] & " x " & [Gauge],[Bunches] & " x " & [Strands] & " x " & [Gauge]) AS Construction FROM WireWeightSpecs ORDER BY WireWeightSpecs.PartNo, IIf(IsNull([Bunches]) Or Trim([Bunches])="",[Strands] & " x " & [Gauge],[Bunches] & " x " & [Strands] & " x " & [Gauge]);

It runs fine in full Access 2010 and has been used for years in an Access 97 database, both MDB and MDE. It runs fine as an Access 2010 ACCDE on a Win7 machine.

If I click various other dropdown boxes using Access 2010 Runtime on an XP machine, they work fine. In this particular case, it returns the following error:

Function is not available in query expression (iif(IsNull([Bunches]) Or Trim([Bunches]) = "" .... and so on. You can see which function(s) are in question, IsNull and Trim.

Why aren't they available?

[Dan Hugos]

Well, hallelujah - for the second problem - I tried the broken reference route, as often and rightly suggested, and it worked!
I simply un-referenced and re-referenced Active X Data Objects 2.8 once again, recompiled and made the ACCDE, and then made sure the program that moves new versions down to the users' PC's was configured correctly, and everything worked fine. Nice that the issue was actually easy to fix.
So now I'm working on problem number 1. That one seems to be more subtle.

[Dan Hugos]

Something as simple as de-referencing the Active X Data Objects Library and re-referencing it cured both problems.
I chose the 2.8 Library, but just for the heck of it tried it with 2.0 to test the idea that Access chooses the latest version available, and that worked fine as well.

Many thanks. Seems rare that you actually find a simple solution!