Run Time Error -2147221164(80040154) Class Not Registered

[mrmmickle1]

I am getting a Run Time Error -2147221164(80040154) Class Not Registered error on the below Red Line of code. I did not write this code and do not understand much about this issue but, I am hoping someone can point me in the right direction to fix it. The code works fine on other computers. The larger scope of the VBA project is to basically create a temporary database and run an SQL query to make a report, then put the report in Excel and finally delete the database. The error occurs in the creating database portion of the code.


I read a few blog entries that said this might have to do with the environment that I am working on. I am using a 64 bit system. I am questioning if this is a 32 bit COM Object? (I have no idea what this means) When I hover over this line of code it says: Provider=Microsoft.Jet.OLEDB.4.0;Data Source= MyFilePath .
I was wondering if there was an easy way to fix this without trying to add or register dll files. I am working on a system computer so I do not have Admin rights to register dll's etc...

[COLOR=#008000]'Create new database[/COLOR]
    Set Catalog = CreateObject("ADOX.Catalog")
[B][COLOR=#ff0000]    Catalog.Create dbConnectStr       [/COLOR][/B][COLOR=#008000]'<----Error on this line[/COLOR]
    Set Catalog = Nothing
    Call SetAttr(dbPath, vbNormalNoFocus)

Any help or knowledge on this subject would be great. I was hoping the fix would just be referencing a new object library or something??

 

[mrmmickle1]

bump

 

[Joe4]

The code works fine on other computers.

I was hoping the fix would just be referencing a new object library or something??

That could possibly be an issue.

Go to a computer that the code does work on, and open up the VB Editor, and from the Tools menu, select References.
Note all the references that are checked/selected.
Now go to the computer in which it is not working, and do the same thing, and check the References.
Are all the same references checked? If not, find it in the list and check it. Note that sometimes the version number might be a little different (if the computers are not on the same operating system and version).

 

[mrmmickle1]

Joe4,

I was able to compare the two computer's stats today:

On the Computer that the file works on the information is as follows:

OS:Windows 7 Professional Service Pack 1, 64 Bit
Version of Excel:2010

References:
Visual Basic For Applications
Microsoft Excel 14.0 Object Library
OLE Automation
Microsoft Office Object Library 14.0
Microsoft Forms 2.0 Object Library

For the Computer that the file does not work on the information is as follows:

OS:Windows 7 Professional Service Pack 1, 64 Bit
Version of Excel:2013

References:
Visual Basic For Applications
Microsoft Excel 15.0 Object Library
OLE Automation
Microsoft Office Object Library 15.0
Microsoft Forms 2.0 Object Library

I am still unsure of why the file doesn't work properly? Is it a possibility that the updated Object Libraries or Newer Version of Excel could be an issue or could there be DLL files missing?

Here is a little bit more of the code if it will help any:

[COLOR=#0000ff]Private Sub[/COLOR] CreateDatabaseAndTable()


  [COLOR=#0000ff]  Dim [/COLOR]dbConnectStr            [COLOR=#0000ff]As String[/COLOR]
    [COLOR=#0000ff]Dim [/COLOR]Catalog                 [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]Object
[/COLOR]    [COLOR=#0000ff]Dim [/COLOR]cnt                    [COLOR=#0000ff] As[/COLOR][COLOR=#0000ff] Object[/COLOR]
  [COLOR=#0000ff]  Dim [/COLOR]dbPath                  [COLOR=#0000ff]As String[/COLOR]
  [COLOR=#0000ff]  Dim [/COLOR]strSql                 [COLOR=#0000ff] As String[/COLOR]

[COLOR=#008000]'Author       : Lalit Mohan[/COLOR]
[COLOR=#008000]'Macro Purpose: Create an Access database on the fly[/COLOR]

[COLOR=#008000]    'Set database name here[/COLOR]
    dbPath = GetDBPath & strDBName
[COLOR=#0000ff]    On Error Resume Next[/COLOR]
    [COLOR=#0000ff]Call[/COLOR] SetAttr(dbPath, vbNormal)
    [COLOR=#0000ff]Call[/COLOR] Kill(dbPath)
    On Error GoTo 0: Err.Clear
    dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"


[COLOR=#008000]    'Create new database[/COLOR]
  [COLOR=#0000ff]  Set[/COLOR] Catalog = CreateObject("ADOX.Catalog")
    Catalog.Create dbConnectStr
   [COLOR=#0000ff] Set[/COLOR] Catalog = [COLOR=#0000ff]Nothing[/COLOR]
    [COLOR=#0000ff]Call [/COLOR]SetAttr(dbPath, vbNormalNoFocus)

 

[mohan.pandey87]

Hi Mickle,

As mentioned in this website:- Database Programming

Try to change this line

dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"

to this one

dbConnectStr = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & dbPath & ";"

 

[mohan.pandey87]

You can also use another method described in: Create Database on the fly

DAO method.

 

[mrmmickle1]

mohan.pandey87,

Thank you for getting me on the right path. The website you mentioned had a few different articles on this same issue. I tried several solutions from the website you referenced and was still unsuccessful. However, with this new line of thought I did a little more research on the subject matter and was fortunate enough to come across this website: How to use ADOX to create an existing Access database

Here are the different replacement code lines I tried before coming to the solution. (Green commented lines did not work. The blue line seemed to resolve my issues.)

[COLOR=#0000ff][B]dbConnectStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";"[/B][/COLOR]
     
[COLOR=#008000]    'dbConnectStr = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & dbPath & ";"[/COLOR]
[COLOR=#008000]    'dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"[/COLOR]
[COLOR=#008000]    'dbConnectStr = "Provider=Microsoft.Jet OLEDB:Engine Type=4;Data Source=" & dbPath & ";"[/COLOR]
[COLOR=#008000]    'dbConnectStr = "Provider=Microsoft.Jet OLEDB;Engine Type=4;Data Source=" & dbPath & ";"[/COLOR]

Is there any particular reason why this line of code works on my company's computers and the original line would not?

As always I appreciate your help! You are a real lifesaver.

 

[mohan.pandey87]

May be in Office 2013 microsoft only support Microsoft.ACE.OLEDB.12.0.

 

[mrmmickle1]

mohan.pandey87,

Thanks for the insight. I'll have to keep this in mind in the future. I appreciate all your help.