Solved

Microsoft Access 2007 Deployment Does Not Work

Posted on 2013-11-14
8
509 Views
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:

-VBA

-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
0
Comment
Question by:shaunwingin
8 Comments
 
LVL 21
Comment Utility
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.
0
 
LVL 84
Comment Utility
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
0
 
LVL 2

Expert Comment

by:dataflowjoe
Comment Utility
Invest in SageKey Deployment, works without a hitch everytime!
0
 
LVL 84
Comment Utility
Sagekey is great, but it's not going to install references unless you tell it to do so.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

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.

Cheers,

Christian
0
 

Author Comment

by:shaunwingin
Comment Utility
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.
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 250 total points
Comment Utility
Hi,

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.

Cheers,

Christian
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
Comment Utility
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:

http://access.mvps.org/access/api/api0001.htm
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.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now