?
Solved

Recieving Error 430 From MS Access 2003 and Windows 7 Ultimate

Posted on 2011-10-07
6
Medium Priority
?
365 Views
Last Modified: 2012-05-12
Greetings;

I developed a Microsoft Access 2003 application using VBA approximately 2 years ago. At the time both my client and I were running Windows XP Pro SP3. The Access application was successfully developed and delivered. All was well.

Last month the same client asked for changes on this same Access application. I since updated my operating system to Windows 7 Ultimate. I still used Microsoft Office 2003. I received an error from Access:


Run-time error '430': Class does not support Automation or does not support expected interface

The offending statement was:


    cnnExcel.Open "Provider=Microsoft.jet.OLEDB.4.0;" & _
                  "Data Source=" & strSelectedPath & _
                  ";Extended Properties=""Excel 8.0;"""

The Access module references under Windows XP SP3 and under Windows 7 are the same and in the same order, namely:

Visual Basic For Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Office 11.0 Object Library
Microsoft Excel 11.0 Object Library

Any assistance on how to correct the problem without implementing the changes from last month using Windows XP SP3 would be most helpful.

Much thanks!
0
Comment
Question by:David Bach
  • 3
  • 2
6 Comments
 
LVL 10

Expert Comment

by:plummet
ID: 36935031
Hi

Could you post the whole of the function, or at least the part up until the error?

Thanks
0
 

Author Comment

by:David Bach
ID: 36937142
Hi Plummel;

Sure, the subroutines up to the error statement follows:

Private Sub ddlSheets_Change()

    Dim dbTourism As Database
    Dim rsTourism As Recordset
    Dim cnnExcel As New ADODB.Connection
    Dim rsExcel As New ADODB.Recordset
    Dim intMessageBox As Integer
   
    If Len(Me.ddlSheets.Text) = 0 Then
        Me.ddlSheets.SetFocus
        Exit Sub
    End If
   
    intMessageBox = MsgBox("Are you sure you wish to load sheet " & Me.ddlSheets.Value & "?", vbYesNo, "Confirm Sheet Name")
   
    If intMessageBox = vbYes Then
    Else
        Exit Sub
    End If

    cnnExcel.Open "Provider=Microsoft.jet.OLEDB.4.0;" & _
                  "Data Source=" & strSelectedPath & _
                  ";Extended Properties=""Excel 8.0;"""

Much thanks for your assistance ... David
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36937288
That error can be associated with outdated versions of references.

Excel 8.0, I believe is for Excel 2002 and earlier.

Try a couple of things:

1.  Without a specific version --

     cnnExcel.Open "Provider=Microsoft.jet.OLEDB.4.0;" & _
         "Data Source=" & strSelectedPath

2.  With Excel 11 (Excel 2003)

    cnnExcel.Open "Provider=Microsoft.jet.OLEDB.4.0;" & _
                  "Data Source=" & strSelectedPath & _
                  ";Extended Properties=""Excel 11.0;"""

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Accepted Solution

by:
David Bach earned 0 total points
ID: 36937824
Greetings mbizup;

I tried your suggestions, however, I received the message 'Run-time error '-2147467259 (80004005)': Unrecognized database format '<full path to Excel 2003 file>'

I seem to have accidently resolved my problem. When I received the error in my original post, I stopped the VBA code in debug mode. I then made a single benign change to the code by deleting a blank line and readding it. I subsequently selected Compile from the Debug menu and ran the application again - this time no error occurred and the results were as expected.

I repeated this process several times to ensure I received the same results which I did. I'm not sure why compiling the VBA code from the Debug menu would resolve the problem, but it has.

Thank you mbizup and plummet very much for your time and suggestions.

David
0
 
LVL 10

Expert Comment

by:plummet
ID: 36938292
Hi David

I'm glad it worked. I tried it here, as I have a similar setup, and I couldn't get it to fail, so I was a bit stumped as to what to try next.

All the best
John
0
 

Author Closing Comment

by:David Bach
ID: 36967128
The solutions came about as a 'What the heck' type of action. I didn't realize what I had done solved my symptom until approximately an hour later.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

850 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