Link to home
Start Free TrialLog in
Avatar of mlcktmguy
mlcktmguyFlag for United States of America

asked on

Access 2013 - User getting error when exporting preview reports from their machines.

I am upgrading an Access 2003 application to Access 2013.  Most of it's going very smoothly, the exception being the export options in the shortcut menu that displays when a report is previewed on the screen.

This is the shortcut menu shown when a report is previewed:

User generated image
The functions in the menu all work on the machines in our office.  One of our office test machines has Office 2013 and the other has no version of office at all.  We create the installer using Sagekey 2013 with the Access 2013 runtime included as part of the install.

The menu was created on a machine with MS Office 2013 Professional, using Dale Fyed's shortcut menu builder.

This is the error the users are getting when they try to export any report.

User generated image
The beginning of the message says:
Microsoft can't complete the output operation.
The Visusal Basic code you entered contains a syntax error or the output procedures are not available.
Make sure there isn't a syntax error in your code.  If the syntax is correct, run setup to reinstall Microsoft Access.  If you want to preserve your security or custom settings, back up the Microsoft Access workgroup information file.
Etc....


The user mentioned that the machines they are running this on have Office 2010 and not Office 2013 but as I mentioned above, one of the test machines in our office that has no issue with the export functions doesn't have any version of MS Office installed.

Any ideas on the cause?
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Also ... check the Printer settings.
If possibly the end users are not using the 'Default Printer' ... Or you created reports on a 'special printer' ... it could ... be the problem, although a long shot in this case.  Sounds more like a reference issue as Scott mentioned.
Avatar of mlcktmguy

ASKER

Here are the references:
User generated image
Not sure why you would need the last Reference ?
But still ...
I got some more information from the testers.  When they load the application on a machine with Office 2013, the report export functions all work.

Machines with Office 2010 are having the issue.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
1. I do not want to make Office 2013 a requirement to run this application, so I need to adjust accordingly.

2. Without the DAO 3.6 reference I get an error on any of the many statements like this:  Dim rsClient As DAO.Recordset
Is there something I can do to change this, other than changing all the legacy DAO to ADO?

3. I understand that this is probably a reference problem and that any Office 2010 machine wouldn't have the 15.0 references.  
I don't understand how the test machine in our office, with no version of Office, runs the report export commands perfectly.  How is this possible?

4. I am familiar with the concept of late binding but don't know how to apply it in this situation.  I am not declaring the report export options In the code I am selecting them from a list of available commands using Dale Fye's excellent tool to build shortcut menu's.  

How can I implement late binding in that scenario?

I am really stuck on this, any help would be appreciated.
Also, I have built my installer using Sagekey.  This is the list of references and support files I include with the install.
User generated imageAre their others I could include to address the reference issue?
I do find it very odd that the ribbon works ok on a machine with no office installed. I wonder however if not some of the office components found in Office 15.0 library are installed when you install access. There are some shared components in that library, so I suspect that whichever office product you install, that shared library might tag along,though I have no way of telling for sure.

I suggest we start by looking at that.
Step 1)
Does the application compile? In VBE hit Debug->Compile. If not, fix any code issues before moving on.

Step 2)
Try to de-select the Office reference, and then hit Debug->compile
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Once again, thanks for the input and suggestions.

1, I don't want to require any version of office.  The 2013 version is an 'upgrade' for many existing users using the 2003 version and we never required any version of office in the past.  We included Access 2003 runtime in the installer.  If they can't use the Excel functions, that's OK.  I can tell them they'll need Office 2013 for that.  At the very least I want them to have the ability to Export a report to a Word RTF.  I found a command ('Tools', 'Publish to Word' in Access 2003 that also seems t work in 2013 but I don't have any non-2013 machines in the Office to test with.  I'll send it out to the beta testers to see if it works.

2.  Not sure about the need for DAO 3.6.  To create the 2013 version I imported everything from the 2003 version into a blank Accdb.

3.  It is very possible that the test machine with no version of Office previously had a version of Office installed at one time.  It is definitely not installed now but perhaps the references were not removed when Office was uninstalled.

4. I sent Dale a message directly with the issues I was having.  He offered some alternate solutions.

I have remove Excel from the Sagekey inclusions.  I was surprised to see it there when I took a snapshot of that screen.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I don't want to require any version of office.
Then you'll have to remove that "Microsoft Office xx Object Library" reference. That is, essentially, the "Office" reference, and if you leave it in your reference list, your application will bomb out if that version (or a later version) is not present. As we've mentioned many times previously, if you include that reference, your end user machines MUST have that library, or a later one. There is no way around that, and there is no way that your application ever worked otherwise. Either you're mistaken, and the end users have a compatible version of Office, or you've found some magical formula to run Office without ... Office.
Not sure about the need for DAO 3.6.
DAO 3.6 was used with the older JET formats. The newer ACE formats (the .accdb format) does not require 3.6. Unless you are specifically calling out the DAO 3.6 reference - and that would be very, very odd - then your .accdb file should not require it. Newer machines are increasingly having troubles with older references. Take the time now to remove that dependency, and you'll be glad you did.
It is very possible that the test machine with no version of Office previously had a version of Office installed at one time.  It is definitely not installed now but perhaps the references were not removed when Office was uninstalled.
See my first point. If your test machine has no version of Office installed, then your application should bomb out almost immediately - or at least as soon as it tries to resolve that reference.
Thanks to all.  I understand this is a reference issue and machine with older versions of Office are not going to be able to use those commands.

I am still stuck on how the test machine we are using, that has no version of office, yet can execute all of the print/export commands perfectly.  I checked with the person that set up the machine.  They said the original machine was Win 10 with a version of office.  However, they  reloaded the Win 7 operating system from scratch and formatted the entire drive when making this into out test machine.  They wiped everything out and even had to download an re-load all of the drivers.  Once complete they loaded Kapersky on it and handed it over.

Since then the only thing done on that machine is install the test application, using the Sagekey installer.  As mentioned above the installer includes the application accde, a runtime version of Access 2013 and the references that are also pictured above, except for Excel, which has been removed.

I double checked the test machine tonight and it does have an Office 15 directory.  However, the entire size of the directory is 16MB.  In the machines in our office that that have Office 2013 professional installed. the Microsoft Office 15 directory is 1.6GB.

Is it possible that the runtime Access 2013 created the minimalist Office 15 directory?  The 2013 runtime isn't like to old 2003 runtime that was just copied on to the machine.  The 2013 runtime runs through an install procedure to add itself to the target machine.

Just trying to understand how the machine which had no version of office prior to the application install can run these command.

We have several successful users of the 2003 version of the software, that have never had Office on their machines.

These are the contents of the minimalist  Office 15 directory containing only 16MB on the test machine:
User generated imageUser generated image
This was indeed a reference issue on the machine with the 2010 version of office.

Dale made a suggestion in a direct email when I inquired about how this might be resolved using his menu builder.

Revising the way the RTF and PDF were being generated removed the reference dependency.  This method of generation works with any version of Office tested so far and also works on machines  with no version of office installed.  Those machines only have the office related elements, shown in a previous post, included in the Sagekey installer.

These are the routines:

Public Function printOutputToPDF()

printOutputTo (cFormatPDF)

End Function
Public Function printOutputToRTF()

printOutputTo (cFormatRTF)

End Function

Public Function printOutputTo(passedOutputFormat As String)

' "PDF"
' "RTF"
'
'Select Output Location
'

' GetObject Name
Dim rptName As String
Dim outputExtension As String
Dim outputLocation As String
Dim OutputNameAndLocation As String

outputExtension = "." & passedOutputFormat
'
rptName = Screen.ActiveReport.Name
'
outputLocation = BrowseFolder("Select folder where output will be placed")    ' CurrentProject.Path
'
If outputLocation = vbNullString Then
    MsgBox "No output folder was selected, no output will be created"
    Exit Function
End If
'
OutputNameAndLocation = outputLocation & "\" & rptName & outputExtension

If passedOutputFormat = cFormatPDF Then
    DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, OutputNameAndLocation, True    'acformatpdf  acFormatRTF
    '
ElseIf passedOutputFormat = cFormatRTF Then
    DoCmd.OutputTo acOutputReport, rptName, acFormatRTF, OutputNameAndLocation, True     'acformatpdf  acFormatRTF
    '
End If
'
'MsgBox "Output FIle " & OutputNameAndLocation & " has been created."
'


End Function
I've said it before, and I'll say it again:

You cannot legally include the Excel.exe file in your installer. Not only is doing so illegal, it's also impractical (since you're not including all the files on which that .exe is dependent), and it's sure to cause you troubles if you try installing that onto a machine where Excel is already installed and configured.

You also cannot legally include the "mso.dll" file (which is the Microsoft Office Object Library). Same reasons, same issues.

If this is a commercial product, as you've indicated in the past, then I'd suggest you run this by your supervisors to ensure they are aware of this procedure.

Here's the 2016 License Agreement: https://www.microsoft.com/en-us/Useterms/Retail/Office/2016Professional/Useterms_Retail_Office_2016Professional_ENG.htm. It's pretty plainly stated (section 2.c) that you cannot transfer any component of Office to another user. I'm certainly no attorney, so I'd encourage you to consult one if you have any doubts.

Your test machine is working because you're including the mso.dll and excel.exe file in your install. However, removing them - which you should do - would cause the test machine environment to be unable to run your application. Only by installing Office would you be able to run your application.
Scott, thanks for the follow up.  I appreciate your input and respect your opinion.  You've helped me out on many, many other issues.

The only improvement in the application with the above changes was that the RTF and PDF work independently of the office version.  

References:
Based on your earlier comments Excel is already removed from the included references.  There are very few application features that use it and I don't have an  issue if user without Excel can't use those features.  I changed the Excel coding to use late binding so the version of Excel the user has is not as critical.

I am not a lawyer either, just a coder trying get assigned projects done in the parameters specified.  I will have the included references reviewed before releasing this application for distribution.

Thanks again for your help.
I fully understand getting things done, and have been in much the same situation as you, but distributing mso.dll is not the right way to resolve the problem. The only way to resolve the issue is to require the user to have Office installed in order for them to use your application. They can use the Access runtime, of course, but there is no such beast as an "Office runtime".