Solved

Object based code returning Error 429 - Active X component cannot create object

Posted on 2014-07-28
9
422 Views
Last Modified: 2014-07-31
Hi Experts,

This one has be puzzled. I've current experienced this in Access 2010 and a new clean install on a new PC of Access 2013. The syntax , I have used for years without issue.

Without all the other bits I start with

Dim xl As Object

A little later I have
Set xl = CreateObject("Excel.Application")

I have a command button that fires the sub with this in. If I use it, I get the error message in the title. If I put a break point on the second line shown and run the code., when it breaks at the second line, I can press F8 to step the code and it works! Let the rest of the code run - all good - lots of Excel object based code that all works.

Remove the break point and try again - and the same  - it gets to the CreateObject line - thinks about it for about 30 seconds and returns the error.

I'm out of ideas! Any one else got a bright idea. I do not want to have to reference Excel as the will be being deployed onto many PCs of varying versions, and keeping it object based stops the problem of being backward compatible.

Kelvin
0
Comment
Question by:Kelvin Sparks
9 Comments
 
LVL 33

Assisted Solution

by:Norie
Norie earned 125 total points
ID: 40223793
Kevin

Do you have any instances of Excel running when you run the code?
0
 
LVL 33

Assisted Solution

by:ste5an
ste5an earned 125 total points
ID: 40223860
First of all: You can stick to this method ;)

It is either a corruption of your database or Excel itself. So create a new one and try your code their (don't import anything). Or try a repair installation of Excel and/or check whether you have some Excel plugins which may interfere here.
0
 
LVL 84
ID: 40223887
In addition to the suggestions above, you should also insure that the machine is fully up to date in regard to Windows and Office installations.
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 40225062
OK, thanks. Will try an another db this evening. The current PC is a brand new one with Win 8.1 and Office Professional 2013 (fully licensed). It is a week old and is fully patched with auto updated permitted. The issue first appeared on my "old" pc, which was Win 7 Prof and Office 2010 professional - although intermittently. Having said that, the database is on the network and so that hasn't changed - which could support the theory of database corruption. Anyway, good thoughts for me to try and report back.


Kelvin
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 40228367
Back again. Since last post I have:
1.  Created an empty Access accdb in Access 2013.
                Connected my tables to BE database.
                Copied the VBA into new modules.
                Created anew form and copied the existing VBA behind it
2.  Unregistered and reregistered Excel
2.  Ran the functionality
              It still works when a breakpoint I inserted for the offending line and F8 is pressed to step that line and F5 to            run the remaining code (extensive Excel based activity)
             It still fails with same error if the code is run without a breakpoint - same as on an earlier PC with Office Professional 2010 and Win Professional 7 (new PC is Win 8.1 Prof and Office Prof 2013.

The PC is fully patched and is set to automatically update Windows and other MS applications.

I'm out of ideas - I've been developing in Access since the mid 90's and am now stumped.

Kelvin
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 125 total points
ID: 40228730
Kelvin,

Have you tried changing over to early binding, just to test if that works?

For my Office automation, I always, initially add the reference to Excel, then set dimension my objects like

Dim xl as Excel.Application     'Object     '

which makes it easy for me go change to:

Dim xl as Object     'Excel.Application     '

when I'm done working with intellisense.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 40228763
You may need to uninstall/reinstall Office on the machine. I've had bad installs on new machines more than once ...
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 40229951
Dale,

Thanks - we think along similar lines - was how I developed it

Scott,

again thanks - will be worth a shot. Not sure that is the cause - 1st happened on one PC, then followed to a completely new pc with different OS and version of Office.

Kelvin
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 40231220
Solved it!

I added a 1 second pause immediately prior to the line
Set xl = CreateObject("Excel.Application")

No more issues - all that happened before this was a warning to users that existing Excel instances were being closed and a check for any (and closing them if there were) - it didn't matter previously whether there were any open or not - still got the error.

I appreciated the support and points issued appropriately.


Thanks


Kelvin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

930 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

11 Experts available now in Live!

Get 1:1 Help Now