Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Microsoft Access 2007 Deployment Does Not Work

Posted on 2013-11-14
Last Modified: 2013-11-24
I have prepared a full database system which my client wants as a runtime. I downloaded the tools set containing the Package Solution Wizard and after some time was able to use it confidently.

I beg your patience as the solutions offered were laboriously attempted, fairly complicated, and did not yield a satisfactory result.

This deployment tool only allows one database at a time, so I created one installation package for my front-end, with included files such as a spreadsheet which is an import sample. An Excel license is expected on the client machine. Another package was created for the back-end, and one more for the configuration database.

Installation went smoothly.

I ensured that my Microsoft Office Service Packs were up to date.

On opening the system and logging in, my relink code worked, and I had the interface in front of me. It consists of a tabbed control as my menu, with some tabs having buttons only, some having forms with subforms on them. Only one simple form without subforms, shows correctly.

However all those with subforms were blank. All controls were not visible. (Nothing to do with coding). Tabs with buttons only, were visible. On clicking any button that runs core VBA functions, such as string functions, there were errors such as you see in the "Errors" tab in the attached.

One solution offered to me was that installation references can be in the wrong order (although the development database's are correct). However that cannot be manipulated in a runtime (.accdr) file.

I tried installing to an office 2000, 2003 and 2007 machine which have no Access pre-installed.

Further, permissions was proposed as an issue. Using the Workgroup Administrator, I attempted to assign myself full control as an Administrator on the development front-end. On going into Properties of its shortcut, and clicking "Advanced", brought me to the option "Run as Administrator". But that was greyed out. However I regenerated the packages, reinstalled, and had the same result.

The core references I used were:


-MS Acc 12.0 OLB

-OLE Aut

-MSO 12.0 Acc DB Engine Objects Lib

References I included were:

-Microsoft Windows Common Controls 6.0 (SP6)

-Microsoft Office 12.0 Object Library

-Microsoft Excel 12.0 Object Library
Question by:shaunwingin
LVL 21
ID: 39649506
First, are you creating a trusted location for the front end and back end?

Are you actually using the reference for "OLE Aut". If not, remove it.

Sounds like a missing reference issue.  I would highly recommend convert as much as you can to using Late Binding. This will allow you to use whatever version oif the other Office apps that are installed.

See: Late Binding in Microsoft Access
Late binding means you can do not have to concern yourself with what version of other software, such as Word, Excel or Outlook, being installed on the target system.  Or if that software is not installed at all on the target system..  We found this problem when a client had Outlook 98 everywhere and the IT manager decided to load Outlook 2000 just to play with it.  You will, of course, have to ensure you do not use any version specific code.

You may also be having a version issue with "Microsoft Windows Common Controls 6.0 (SP6)". Curious, what are you using this to do?

What is the Windows OS versions, including 32 or 64 bit, you are using to compile the .accde?

FWIW: I compile  my accde or mde on Windows XP. This avoids Windows version reference issues.
LVL 84
ID: 39649647
You mention something that was attached, but there are not attachments to the question.

Are you certain the -Microsoft Windows Common Controls 6.0 (SP6) exists on the target system? These are not automatically installed, especially with newer machines. In fact, you should take steps to remove those controls, and use native Access controls instead. You'll find nothing but troubles moving forward with those, especially as more and more people transition over to 64 bit.

Microsoft Office 12.0 Object Library

Is Office installed on the target machine? You mention the Runtime, so it's not clear whether your target machine has a full, valid Office installation

Expert Comment

ID: 39650512
Invest in SageKey Deployment, works without a hitch everytime!
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

LVL 84
ID: 39650906
Sagekey is great, but it's not going to install references unless you tell it to do so.
LVL 24

Expert Comment

ID: 39654522

the Common Controls library had some issues because of a defect update from Microsoft in August last year which made them unavailable after this update. That issue is solved since beginning of this year so there is no need to replace them, they work without problems also in Windows 7 64 Bit. Unfortunately there is no real replacement so you do not have a chance to replace them with Access native controls, i.e. there is no Treeview control in Access.

Yes, the reference order can be a problem but it would be the same in the full and the runtime version of Access and only if you do not use the full qualified name for objects. You should always declare an object with the full name to avoid that. I.e., use "DAO.Recordset" and not "Recordset" because if you also use ADO then there is also a "Recordset" object there so the order in the references is the one which decides which one is used. If you declare it also with "ADODB.Recordset" there will be no problem.

The references will automatically adjusted by Windows/Office if you use an older one and the machine has a newer one. So if you develop something with the Office 12 library (O2007) and the machine has Office 2010 installed it will automatically use the Office 14 library - but not in the other direction. So if you develop something with the Office12 library in the references and try to run it on a machine with an older Office that will not work. So if you have different versions at the client side you are normally forced to develop with the oldest available version or recommend the client to install the same version on all clients (which should normally be the case...).

Moreover, you should no longer use the Access security system because it was officially removed in A2007, although it can be used here for compatibility reasons. In A2010 it is no longer available. You should consider to use a SQL Server as backend to which you can migrate easily and if you want you can install the Express version which doesn't cost anything (not more than a server which runs it). The security model of SQL Server is much better than Access ever was.

I personally use InnoSetup to create installers. This one is free and very comfortable and it produces very small installers.



Author Comment

ID: 39658458
To LSMConsulting: I removed the Common Controls reference. That was left in by mistake, having used one of my old systems for its existing code. I should have thought of removing redundant references. As for Microsoft Office 12.0 Object Library, my usage is the msoFileDialogFilePicker. Access Runtime is the only runtime installed. The Object Library as I understand just gives you coding ability, not requiring the entire suite of that version (12.0). Am I right? Many thanks.

To TheHiTechCoach: I removed the OLE Aut reference. Late Binding will be attempted later. Many thanks. PS what is a "trusted location?"
Fully qualified objects will be implemented. The Common Controls reference has been eliminated. Some client machines will have older Office so that will have to be taken into account. So I guess there's a danger in upgrading my system to Access 2013 to get Microsoft support spanning the next 2 versions? Many thanks.

ToBitsqueezer: I've downloaded InnoSetup. Thanks so much for this suggestion also.
LVL 24

Accepted Solution

Bitsqueezer earned 250 total points
ID: 39659137

the object library contains the code which you want to call, i.e. the Recordset object you want to call. If you add a reference you can use "Early Binding", that means, you can declare a variable with "Dim rs As DAO.Recordset". You get full IntelliSense support which shows you which objects are available and you can look into the object browser to get more information about each object and enum/constant declarations. Moreover the compiler of VBA can check if your code uses the right methods and properties of these objects and so on.
If you leave out the reference then you can only use "Late Binding", that means, whenever you want to use an object of that library you must first create an instance which will load the needed DLL into memory. Windows will take care that the current available version on the computer will be used in that case - but on the other hand you lose all the advantages above and you can declare all objects only like "Dim rs As Object" (object is a generic type which can be used for any unknown object). If you have a reference you need to have the same version of the library or, in newer cases, Windows/Access adjust that automatically in some cases.
So you should not have the newest Access to develop something, if you work with different target systems, create a virtual machine for each Access version and then develop or at least test the application using the right version. In most cases it is not good to develop in a newer version for an older one. A2007 was a good exception, I found it easy to convert the databases without big problems in versions down to A2000, but A2010 is the opposite example where it can be a problem to downgrade the application only to A2007, not to speak of older versions. A2013 is again a step in the wrong direction as it now do also not support ADPs anymore.


LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 39660116
The "msoFileDialogFilePicker" constant is part of the Office library, not Access. Your end users will need to have the correct version of Office installed on their systems in order for you to use that method. Note you cannot distribute that file with your application - the only way for your end users to get that reference is for them to purchase and install a compliant version of Office.

You could use API calls to do this instead. The mvps.org website has a nice bit of code to allow you to do this:

he Object Library as I understand just gives you coding ability, not requiring the entire suite of that version (12.0).
The Microsoft Office 12.0 Object Library is one of the core components of the Office suite, and provides much of the functionality and interoperability between the components. It cannot be distributed, as I mentioned earlier. The only way for your users to get that library is for them to purchase and install the correct version of that library. Same with the Excel library ... they would need to purchase it, either as part of Office, or as a standalone product.

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Microsoft Dynamics Roles question 4 112
How do ASP.NET and MVC work together? 4 63
Where to download Microsoft Form 2.0 Object Library. 2 70
SSRS Deployment problem 5 64
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question