Its Me

Thursday, December 1, 2011

Run time error: 429 - ActiveX Component Cant Create Object - VBA Excel Code

NOTE: This article is for Advanced Users who know exactly what they are upto when they modify the REGISTRY keys. I stand no assurance for any damage that would after following this article. But, this certainly solved my problem with no issues.

Hello There -

For the past 3 Days, I have been breaking my head to get to the details of why machine which used to execute VBA Excel code, failed to work. I did not realize that some registry tool that was trusted would actually delete genuine registry entries. Anyways, the error was that I was not able to connect to the Database from VBA code in Excel to the SQL server. The error that I got was the standard -

"Run time error: 429

ActiveX Component Cant Create Objects."

So, I thought may be repairing my Office 2007 Suite would solve the purpose. So, I repaired it. That did not solve the problem. Then I un-installed it and re-installed it, nope no resolution. I repaired the Startup of my Computer using the Original OS disk. Nope that too did not solve the problem. So, now again I manually un-installed all instances of Office 2007 suite and then Installed Fresh Office 2010 suite. Well hello, that too did not solve my problem. I still got the same error.

Strangely, the code I have written on my machine doesn't work on the same machine. So, I decided to get to the root of the problem. After doing all the combinations above, I felt that something was missing from the Registry. So, what I did was to download Process Monitor exe (from SysInternals.com which is now part of Microsoft - http://technet.microsoft.com/en-us/sysinternals/bb896645) and then ran it on my machine filtering to show only EXCEL.EXE process name details. The tool captured the interceptions and what I found was that there were references being made to the Registry. So, I knew for sure somehow my registry got corrupted or is missing the required elements for the code to work.

Next, I sent the excel code file to another colleage of mine. Executed the excel code while ProcMon.exe was running. Filtered the interceptions to display only EXCEL.EXE process name details. Then scanned through all the Registry items one by one. Compared the same with my machine. I did find as many as 7 registries missing the crucial information for my excel code to connect to SQL. After making the changes, voila, I was able to re-run my good old VBA Excel Code.

For your information, the following are the settings in the registry that should be present so that your code can connect to SQL -
[
My machine OS: Windows 7
SQL Server: SQL Server 2008
]

1) Missing Entries for 0C7FF16C-38E3-11D0-97AB-00C04FC2AD98

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}]
@="SQLOLEDB"
"OLEDB_SERVICES"=dword:ffffffff

[HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}\ExtendedErrors]
@="Extended Error Service"

[HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}\ExtendedErrors\{C0932C62-38E5-11d0-97AB-00C04FC2AD98}]
@="SQLOLEDB Error Lookup"

[HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}\Implemented Categories]

[HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}\Implemented Categories\{D267E19A-0B97-11D2-BB1C-00C04FC9B532}]

[HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}\InprocServer32]
@=hex(2):25,00,43,00,6f,00,6d,00,6d,00,6f,00,6e,00,50,00,72,00,6f,00,67,00,72,\
00,61,00,6d,00,46,00,69,00,6c,00,65,00,73,00,25,00,5c,00,53,00,79,00,73,00,\
74,00,65,00,6d,00,5c,00,4f,00,6c,00,65,00,20,00,44,00,42,00,5c,00,73,00,71,\
00,6c,00,6f,00,6c,00,65,00,64,00,62,00,2e,00,64,00,6c,00,6c,00,00,00
"ThreadingModel"="Both"

[HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}\OLE DB Provider]
@="Microsoft OLE DB Provider for SQL Server"

[HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}\ProgID]
@="SQLOLEDB.1"

[HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}\VersionIndependentProgID]
@="SQLOLEDB"

2) Missing entries for - 3FF292B6-B204-11CF-8D23-00AA005FFE58

[HKEY_CLASSES_ROOT\CLSID\{3FF292B6-B204-11CF-8D23-00AA005FFE58}]
@="FoxOLEDB 1.0 Object"

[HKEY_CLASSES_ROOT\CLSID\{3FF292B6-B204-11CF-8D23-00AA005FFE58}\InprocServer32]
@=hex(2):25,00,43,00,6f,00,6d,00,6d,00,6f,00,6e,00,50,00,72,00,6f,00,67,00,72,\
00,61,00,6d,00,46,00,69,00,6c,00,65,00,73,00,25,00,5c,00,53,00,79,00,73,00,\
74,00,65,00,6d,00,5c,00,6d,00,73,00,61,00,64,00,63,00,5c,00,6d,00,73,00,61,\
00,64,00,63,00,65,00,2e,00,64,00,6c,00,6c,00,00,00
"ThreadingModel"="both"

[HKEY_CLASSES_ROOT\CLSID\{3FF292B6-B204-11CF-8D23-00AA005FFE58}\ProgID]
@="FX.Rowset.1"

[HKEY_CLASSES_ROOT\CLSID\{3FF292B6-B204-11CF-8D23-00AA005FFE58}\VersionIndependentProgID]
@="FX.Rowset"

3) Missing entries for - 00000507-0000-0010-8000-00AA006D2EA4

[HKEY_CLASSES_ROOT\CLSID\{00000507-0000-0010-8000-00AA006D2EA4}]
@="ADODB.Command"

[HKEY_CLASSES_ROOT\CLSID\{00000507-0000-0010-8000-00AA006D2EA4}\InprocServer32]
"Class"="ADODB.CommandClass"
"Assembly"="ADODB, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
"RuntimeVersion"="v1.0.3705"
"ThreadingModel"="Both"
@="C:\\Program Files\\Common Files\\System\\ado\\msado15.dll"

[HKEY_CLASSES_ROOT\CLSID\{00000507-0000-0010-8000-00AA006D2EA4}\InprocServer32\7.0.3300.0]
"Class"="ADODB.CommandClass"
"Assembly"="ADODB, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
"RuntimeVersion"="v1.0.3705"

[HKEY_CLASSES_ROOT\CLSID\{00000507-0000-0010-8000-00AA006D2EA4}\ProgID]
@="ADODB.Command.2.8"

[HKEY_CLASSES_ROOT\CLSID\{00000507-0000-0010-8000-00AA006D2EA4}\VersionIndependentProgID]
@="ADODB.Command"

4) Missing entries for - 00000514-0000-0010-8000-00AA006D2EA4

[HKEY_CLASSES_ROOT\CLSID\{00000514-0000-0010-8000-00AA006D2EA4}]
@="ADODB.Connection"

[HKEY_CLASSES_ROOT\CLSID\{00000514-0000-0010-8000-00AA006D2EA4}\InprocServer32]
"Class"="ADODB.ConnectionClass"
"Assembly"="ADODB, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
"RuntimeVersion"="v1.0.3705"
"ThreadingModel"="Apartment"
@="C:\\Program Files\\Common Files\\System\\ado\\msado15.dll"

[HKEY_CLASSES_ROOT\CLSID\{00000514-0000-0010-8000-00AA006D2EA4}\InprocServer32\7.0.3300.0]
"Class"="ADODB.ConnectionClass"
"Assembly"="ADODB, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
"RuntimeVersion"="v1.0.3705"

[HKEY_CLASSES_ROOT\CLSID\{00000514-0000-0010-8000-00AA006D2EA4}\ProgID]
@="ADODB.Connection.2.8"

[HKEY_CLASSES_ROOT\CLSID\{00000514-0000-0010-8000-00AA006D2EA4}\VersionIndependentProgID]
@="ADODB.Connection"

5) Missing entries for - 00000535-0000-0010-8000-00AA006D2EA4

[HKEY_CLASSES_ROOT\CLSID\{00000535-0000-0010-8000-00AA006D2EA4}]
@="ADODB.Recordset"

[HKEY_CLASSES_ROOT\CLSID\{00000535-0000-0010-8000-00AA006D2EA4}\InprocServer32]
"Class"="ADODB.RecordsetClass"
"Assembly"="ADODB, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
"RuntimeVersion"="v1.0.3705"
@="C:\\Program Files\\Common Files\\System\\ado\\msado15.dll"

[HKEY_CLASSES_ROOT\CLSID\{00000535-0000-0010-8000-00AA006D2EA4}\InprocServer32\7.0.3300.0]
"Class"="ADODB.RecordsetClass"
"Assembly"="ADODB, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
"RuntimeVersion"="v1.0.3705"

[HKEY_CLASSES_ROOT\CLSID\{00000535-0000-0010-8000-00AA006D2EA4}\ProgID]
@="ADODB.Recordset.2.8"

[HKEY_CLASSES_ROOT\CLSID\{00000535-0000-0010-8000-00AA006D2EA4}\VersionIndependentProgID]
@="ADODB.Recordset"

6) Missing entries for - 2206CDB0-19C1-11D1-89E0-00C04FD7A829

[HKEY_CLASSES_ROOT\CLSID\{2206CDB0-19C1-11D1-89E0-00C04FD7A829}]
@="MSDAINITIALIZE Class"
"AppID"="{2206CDB0-19C1-11D1-89E0-00C04FD7A829}"

[HKEY_CLASSES_ROOT\CLSID\{2206CDB0-19C1-11D1-89E0-00C04FD7A829}\ExtendedErrors]
@="Extended Error Service"

[HKEY_CLASSES_ROOT\CLSID\{2206CDB0-19C1-11D1-89E0-00C04FD7A829}\ExtendedErrors\{2206CDB3-19C1-11D1-89E0-00C04FD7A829}]
@="MSDASC Error Lookup"

[HKEY_CLASSES_ROOT\CLSID\{2206CDB0-19C1-11D1-89E0-00C04FD7A829}\InprocServer32]
@=hex(2):25,00,43,00,6f,00,6d,00,6d,00,6f,00,6e,00,50,00,72,00,6f,00,67,00,72,\
00,61,00,6d,00,46,00,69,00,6c,00,65,00,73,00,25,00,5c,00,53,00,79,00,73,00,\
74,00,65,00,6d,00,5c,00,4f,00,6c,00,65,00,20,00,44,00,42,00,5c,00,6f,00,6c,\
00,65,00,64,00,62,00,33,00,32,00,2e,00,64,00,6c,00,6c,00,00,00
"ThreadingModel"="Both"

[HKEY_CLASSES_ROOT\CLSID\{2206CDB0-19C1-11D1-89E0-00C04FD7A829}\ProgID]
@="MSDASC.MSDAINITIALIZE.1"

[HKEY_CLASSES_ROOT\CLSID\{2206CDB0-19C1-11D1-89E0-00C04FD7A829}\VersionIndependentProgID]
@="MSDASC.MSDAINITIALIZE"

7) Missing entries for - ECABB0C0-7F19-11D2-978E-0000F8757E2A

[HKEY_CLASSES_ROOT\CLSID\{ecabb0c0-7f19-11d2-978e-0000f8757e2a}]
@="DispenserManager"

[HKEY_CLASSES_ROOT\CLSID\{ecabb0c0-7f19-11d2-978e-0000f8757e2a}\InprocServer32]
@=hex(2):25,00,73,00,79,00,73,00,74,00,65,00,6d,00,72,00,6f,00,6f,00,74,00,25,\
00,5c,00,73,00,79,00,73,00,74,00,65,00,6d,00,33,00,32,00,5c,00,63,00,6f,00,\
6d,00,73,00,76,00,63,00,73,00,2e,00,64,00,6c,00,6c,00,00,00
"ThreadingModel"="Both"

[HKEY_CLASSES_ROOT\CLSID\{ecabb0c0-7f19-11d2-978e-0000f8757e2a}\ProgID]
@="COMSVCS.DispenserManager"

[HKEY_CLASSES_ROOT\CLSID\{ecabb0c0-7f19-11d2-978e-0000f8757e2a}\VersionIndependentProgID]
@="COMSVCS.DispenserManager"

I had all the above registry entries missing in HKCR\CLSID Registry folder.

You can try to copy each of the above registry entries to a notepad and save it with ".reg" extension. Then execute it, so that they get copied over to the registry on the machine that they are run.

Let me know if this article helped you.

Cheers.

1 comment:

Milestone said...

I would like to how to add this reg key to my machine.You told to execute.But I don't know how to execute.
Best Regards,
Zay Yar Min
maungzay@gmail.com