Access expressions not working in Runtime environment.

[jerem]

Hi,


There is plenty of documentation on this but nothing that seems to help. I have a LEFT function in a query that does not work on Access RunTime 2010 while it works perfectly fine on Access 2010.

Basically, I have a memo field called Notes and in the query I create a new field called ShortNote with the following function: IIF(LEN([Note])>45,LEFT([NOTE],45)&"...",[Note])

Again that works perfectly fine on any machine running full Ms Access 2010, the issue appears on machines that run Access RunTime only.

I have isolated each function and it is definitely LEFT that is the problem. I have also tried MID and the same problem appears. Same error message comes with the DATE() function.

I have been told it could be a reference issue, some others said it is not. And if it is a reference issue, how do you want to check missing reference in a runtime environment anyway.

Thanks for your help!

[June7]

Does the same error occur if the expression is in a textbox on form or report instead of in query?

[jerem]

Yes it does. I actually moved it from a text box to a query to see if the error was still occurring

[June7]

Sorry, never used runtime version.

Do the functions still work in VBA?

One poster's solution was to create custom functions that used the intrinsic functions. Then call the custom function in query or textbox.

For example:

Function LongString(strString As String) As String
If Len(strString) > 45 Then
LongString = Left(strString,45) & "..."
Else
LongString = strString
End If

Call the custom function:
LongString(Nz([Note],""))

Or a more generic substitute:

Function MyLeft(strString As String, intLen As Integer) As String
MyLeft = Left(strString, intLen)
End Function

Call the function:

MyLeft(Nz([Note],""), 45)

And if Nz also fails, declare all the string variables and functions as variant type and handle the null within the custom function.

[jerem]

It was a reference issue. I used a code to list them all and then I packaged them with the application. There still is an issue with one reference file: EXCEL.EXE

Since some of the machines do not have office I need to package EXCEL.EXE for some pivot table features of the application. The issue is when packaging additional files, the only location I can put them is in the application default folder (C:\) or one of its subfolders. What I would like to do is to put EXCEL.EXE in C:\Program Files\Microsoft Office\Office14 but this doesn't seem to be possible with the Access Packaging Solution

The reason for this is that on some machines, with EXCEL.EXE intsalled in the default folder, it works perfectly fine (the application looks for EXCEL.EXE the first time it opens) and for some it doesn't work until I manually move EXCEL.EXE to the Office14 folder.

So, is there a way to have the packaging solution put EXCEL.EXE in C:\Program Files\Microsoft Office\Office14 or can this be solved with an additional registry key? If the later, how to do?

Thank you.

[June7]

I know code can copy files but that can hit permission restrictions set up by IT.

However, I don't know if Excel.exe can be packaged with Access. And even if it could be, how would Excel be installed? I don't know enough about registry keys.

[jerem]

It is not the entire application, just the executable file for reference. I am searching through de registry keys from one of the machines where it worked and will try to add them to the setup file. Hope it works.

[June7]

Can't just change code to deal with absence of Excel?

[jerem]

You mean late binding?

[ItsMe]

This thread is missing a huge factor. The only thing that even hints at a missing reference is, "some of the machines do not have office". Late binding is not going to help without some version of office installed on the client.

Access RT should manage the expression. So (should be safe to assume) there is code somewhere trying to create an Office object. Maybe .TransferSpreadsheet method. In this case, Office needs to be installed. If there is not an Excel.exe, office is not installed. If there is, use late binding to avoid version conflicts.

[jerem]

Alright ItsMe, thank you! So I packaged EXCEL.EXE in the setup file. Now it installs in C:\MyApplicationFolder. Any way to make the setup install it in C:\Program Files\Microsoft Office\Office14?
Or if it stays in C:\MyApplicationFolder what are the registry keys to make it work?

On one of the machines where the application looked for the EXCEL.EXE file the first time I ran it I managed to identify the following keys:

Computer\HKEY_CLASSES_ROOT\TypeLib\{00020813-0000-0000-C000-000000000046}\1.7\0\win32
Value data: C:\MyApplicationFolder\EXCEL.EXE


HKEY_CLASSES_ROOT\Wow6432Node\TypeLib\{00020813-0000-0000-C000-000000000046}\1.7\0\win32
Value data: C:\MyApplicationFolder\EXCEL.EXE


HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib\{00020 813-0000-0000-C000-000000000046}\1.7\0\win32
Value data: C:\MyApplicationFolder\EXCEL.EXE


HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\Ty peLib\{00020813-0000-0000-C000-000000000046}\1.7\0\win32
Value data: C:\MyApplicationFolder\EXCEL.EXE


HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\Ty peLib\{00020813-0000-0000-C000-000000000046}\1.7\0\win32
Value data: C:\MyApplicationFolder\EXCEL.EXE

[ItsMe]

Post #11 does not make any sense to me. So you are registered with Microsoft and have access to OPK for Office? How are you installing office?

[jerem]

The machines on which I install the application run Runtime for Access 2010. Therefore there is an Office14 folder but no EXCEL.EXE, which is needed by my application. So I use the additional file feature of the Access Packaging Solution to add the EXCEL.EXE file. The problem is that it installs it in the default application folder, and while some computers detects it and run the application fine, some others don't. For those that don't, I need to manually move the EXCEL.EXE file to the Office14 folder and then it all works fine.
So my thinking is either I find a way to install that EXCEL.EXE file in the right folder directly or I find a way to make sure the application finds the file in its current location (which I thought I could do through registry keys).

[ItsMe]

There is nothing in this thread that explains why you need Excel.exe other than your statement that it is needed. What code are you using that requires this file?

Furthermore, placing Excel.EXE in a folder, alone, is not going to allow you to view excel files. I believe there are third party apps for viewing office files but don't know what the capabilities are. May not work so swell with pivot tables. I imagine the act of generating an excel file is going to require supporting files in addition to Excel.EXE. Installing Access Runtime will provide certain Office folders but Access is not Excel and it is not Word and it is not Power Point, etc.

There are office compatibility packs available from Microsoft that will install some of these files/folders. These compatibility packs are to provide users that have previous versions of Office to view files created with newer versions of Office. Don't believe this is going to get you what you need either.

My belief is you will need to have Excel installed on all of the workstations to accomplish "Creating an Excel Workbook" via Access.

In order to push an install of office (post 2003 versions) onto a machine you would need to create an OPK image. This image would include an OS install that would provide an Out Of The Box Experience (OOBE), I believe is the acronym. You would have to be registered as an OEM Microsoft Partner.

Another option may be to Pull an Office install from a retail version of Office stored on a server or from an install pack. The user would then have to supply the appropriate license info to complete the install.

There may be another approach to accomplish what you are trying to do but, right now, I really do not understand what your goal is.

[jerem]

I have pivot tables and pivot charts that require the reference file EXCEL.EXE. I know it is the right reference file since as soon as I manually put it in the Office14 folder, I get everything working exactly as I want it to work. No need of any third party or Ms Excel to be installed. Again, it works perfectly. My question is about Ms Access Packaging Solution. How to use the additional file feature to put EXCEL.EXE IN Office14 or how to use the registry key feature to register EXCEL.EXE in its default installation location to make sure it is used by my application.