Access Runtime on PC without MS Office

[Rick in NS]

I am using Microsoft Office 2003 and Visual Studio 2005 with Access Developer
Extentions to package an application for deployment. My understanding is the
the application should run on another machine using the runtime version
included in the package. However; I am testing the application on a clean
machine which does not have any copy of MS Office on it. The application
runs most routines uneventfully. However; certain forms displays and reports
based on queries fail with "Function is not available in expressions in query
express ....."

I have found references from Douglas Steele, MVP and Ken Snell, MVP that
indicate this has to do with missing references to files with an explanation
on how to check the target machine for these missing reference.

However; the target machine does not have anything on it but the runtime
version of Access and the application front-end is in MDE format. So, it is
not possible to open a code module or the debug window to check the reference
on the target machine.

Any suggestions on how to deal with this thorny issue?

TIA

 

[Rick Brandt]

I am using Microsoft Office 2003 and Visual Studio 2005 with Access Developer
Extentions to package an application for deployment. My understanding is the
the application should run on another machine using the runtime version
included in the package. However; I am testing the application on a clean
machine which does not have any copy of MS Office on it. The application
runs most routines uneventfully. However; certain forms displays and reports
based on queries fail with "Function is not available in expressions in query
express ....."

I have found references from Douglas Steele, MVP and Ken Snell, MVP that
indicate this has to do with missing references to files with an explanation
on how to check the target machine for these missing reference.

However; the target machine does not have anything on it but the runtime
version of Access and the application front-end is in MDE format. So, it is
not possible to open a code module or the debug window to check the reference
on the target machine.

Any suggestions on how to deal with this thorny issue?

On your PC open a new blank file and then check the references. These are the
default references for an Access app in Access 2003. Then compare that to the
references in the file you are having problems with. Are there any additional
references?

If there are the solution is to remove them. No app that you intend to
distribute to multiple PCs should have anything but the default references in it
or you will have no end to these headaches.

If you need to use external libraries (Outlook , Word, etc.), then use late
binding so that you don't have to set references.

 

[Rick in NS]

Oh boy!!! I have peppered the application with code to control toolbars
using the CommandBar object which I believe is in the Microsoft Office Object
Library 11.0 which is one of the references in my application but not in the
Access 2003 default. In addition I have an Excel pivot table which relies on
the Office XP Web Componets (OWC10.DLL) to function properly. Removing
either the reference to the WDC10.DLL or to the Office Libary 11.0 kicks out
numerous compile errors.

I don't pretend to understand the concept of 'late binding'. Is there a fix
for this; or am I going to have to rewrite the application removing any
reference in the code to the CommandBar object or to a OWc10.PivotTable?

Would another approach; insisting the target machine have some version of
Office on it resolve the issue?

 

[Rick Brandt]

Oh boy!!! I have peppered the application with code to control
toolbars using the CommandBar object which I believe is in the
Microsoft Office Object Library 11.0 which is one of the references
in my application but not in the Access 2003 default. In addition I
have an Excel pivot table which relies on the Office XP Web Componets
(OWC10.DLL) to function properly. Removing either the reference to
the WDC10.DLL or to the Office Libary 11.0 kicks out numerous compile
errors.

I don't pretend to understand the concept of 'late binding'. Is
there a fix for this; or am I going to have to rewrite the
application removing any reference in the code to the CommandBar
object or to a OWc10.PivotTable?

Would another approach; insisting the target machine have some
version of Office on it resolve the issue?

No because "some version" would not be adequate. With early binding (where you
set references) the exact version is required in almost all cases even down to
having the same service packs installed.

Google these groups on "late binding". It is not very difficult to switch your
code to use that and it really is the only way to do it when you have multiple
machines that you cannot absolutely control what software is installed. In
fact, most developers use early binding first to get things working because
debugging is easier and you get to use the intellisense features in the VBA
editor, then make the few changes to late binding afterwards when everything is
working.

There are two huge advantages to late binding. If the user is totally missing a
required external library, then the features in your app that depend on that
library obviously don't work, but everything else in your app still functions
normally. As you have seen, with early binding a missing reference causes many
VBA functions to fail that have nothing at all to do with the missing library.
Your app basically falls on its face.

The second big advantage is that your external library calls become (for the
most part) version independant. In your case as long as you are not using any
Excel features that only exist in a certain version then ANY version of
Office/Excel will likely work. With early binding you normally have to have
the exact version for the app to function.

 

[Rick in NS]

I have searched the MVP site for 'late binding' and wonder if the following
in anyway represents the actual code that will take care of the error:


Dim oXL As Object
Set oXL = CreateObject("Excel.Application")

Sub MyQuery()
Select Left(Clients.ClientName1,7) AS MySort
End Sub

oXL.Quit

Have no idea if I am anywhere close. It puzzles me why you have to create
an Excel object unless that is the only application that addresses the Left
function.

 

[Rick in NS]

--
Rick in NS
I have no idea if I have grasped the methodology at all with regard to 'late
binding' after having read everything I could find on the subject in the MVP
site. Here is my feeble attempt to address the issue:

Dim objXL As Excel.Application
Set objXL = New Excel.Application

Sub My Sub()
Select Left(ClientName,1,7) As MySort
End Sub

objXL.Quit

Will this code address the Left function needed in the query?

 

[Douglas J. Steele]

You don't need a reference to Excel in order to get Left to work in a query.
The Left function actually comes from the VBA library, which is usually the
first reference in the list. Unfortunately, if any of the other references
are broken, it affects functions from the VBA library, because that's
actually one of the last places Access looks for functions (this allows you
to override the built-in function with your own function with the same name
should you so desire)

Tony Toews has an introduction to Late Binding at
http://www.granite.ab.ca/access/latebinding.htm

 

[Rick in NS]

After reading and re-reading the material on early and late binding I went
back to the 2002 copy of the application and checked the registered libraries
which were as in this order as follows:

Visual Basic for Applications
Microsoft Office 10.0 Ojbect Library
OLE Automation
Micsrosoft DAO 3.6 Object Library
Microsoft ActiveXData Objects 2.1 Libary
Microsoft Office XP Web Components

I copied the application front end and back end to the Microsoft Office 2003
which also has the Visual Studtio Microsoft Access 2003 Developer Extensions
and packaged the product including the OWC10.DLL (Web components). I ran the
install from the package wizard on the original development (Office XP)
machine.

I can run the application from Microsoft Access 2002 fully without any
difficulty.

However; when I run Microsoft Access 2003 one of the functions fails. It is
a routine that simply passes some mail merge data to a .txt file

When the routine runs is is interrupted by the following error:

"2281 The format that enable you to output data as a Miscrosft Excel,
rich-text format, MS-Dos text, or HTML file are missing or incorrectly
registered in the windows registry"

I have checked the the reference again and now find the following:

Visual Basic for Applications
Microsoft Office 11.0 Ojbect Library
Microsoft Office 10.0 Ojbect Library
OLE Automation
Micsrosoft DAO 3.6 Object Library
Microsoft ActiveXData Objects 2.1 Libary
Microsoft Office XP Web Components

I'm baffled. Any idea how I can correct the failure?

If I must 'late bind' some object can you be specific about what and how I
do that.
For example; where is the code entered; in a module or in the code
generating the text file? Excuse my ignorance but I am a bean counter
wishing I was a programmer right now.

TIA

 

[paddy]

Rick,

I have just had a problem like yours. When I would run my app in ACCES
as an MDB or after "make mde" in ACCESS everything when well. I als
used the "runtime" ("C:\Program Files\Microsof
Office\OFFICE11\MSACCESS.EXE" /runtime "c:\myapp\myapp.mde" ) switch o
the command line of and desktop icon and it worked okay too. Drove m
nuts. Everytime I would try to run it on a users machine (w/o OFFICE)
would get "on click-Open Report canceled". The problem was I had t
actually "enable" the toolbar even though I had allowed them via th
Startup Options.

Here's what worked for me. Using the Startup Wizard via Dev.Extentions
the last step asks "Do you want to run..?". When I ran the app fro
there the error was re-created which allowed me to identify better wha
was happening.

The only thing I can come up with is there is a difference between th
ACCESS runtime (mode/switch) and the actual runtime engine. I believ
the Startup Wizard provides a more accurate "runtime test" by using th
real runtime engine. This may or may not be of help.

Good Luck

Patric

 

[Rick in NS]

Hi Patrick:

Thank you for taking the time to try to help. I have attempted to run the
application written in Access 2002 with the /runtime switch in 2003 and can't
get a handle on where the error originates. I suspect it has to do with
Microsoft Outlook which is the utility used in the module. I am going to
start a new thread on this subject since it appears no one has the answer
here. Thanks anyway.