UtterAccess Discussion Forums > Access 14.0 Object Library In Excel

Full Version: Access 14.0 Object Library In Excel

UtterAccess Discussion Forums > Microsoft® Access > Access Automation

RCollard

Mar 5 2012, 03:08 PM

The code below demonstrates a problem I’ve noticed when an Excel workbook uses early binding with the Access 14.0 object library to retrieve information from a running instance of Access 2002. Attempting to access the Name property of elements of the Forms and Reports collections and Screen.ActiveReport all generate error 430 “Class does not support automation or does not support expected interface”. The same code works as expected with the Access 10.0 object library and with late binding. The problem with the Access 14.0 object library occurs with both Excel 2002 and Excel 2010. Any ideas why the Access 14.0 object library throws the 430 error?

CODE

Private Sub Workbook_Open()

    Dim objAccess As Access.Application     'early binding
    'Dim objAccess As Object                 'late binding
    Dim i As Long
    Dim sMsg As String

    'On Error Resume Next
    Set objAccess = GetObject(, "Access.Application")
    If Err = 0 Then
        sMsg = "DBEngine.Version: " & objAccess.DBEngine.Version & vbCrLf & _
                "CurrentDb.Name: " & objAccess.CurrentDb.Name & vbCrLf & _
                "Forms:"
        For i = 1 To objAccess.Forms.Count
            sMsg = sMsg & vbCrLf & vbTab & objAccess.Forms(i - 1).Name      'error 430
        Next i
        sMsg = sMsg & vbCrLf & "Reports:"
        For i = 1 To objAccess.Reports.Count
            sMsg = sMsg & vbCrLf & vbTab & objAccess.Reports(i - 1).Name    'error 430
        Next i
        sMsg = sMsg & vbCrLf & "CurrentObjectName: " & objAccess.CurrentObjectName
        sMsg = sMsg & vbCrLf & "ActiveReport.Name: " & objAccess.Screen.ActiveReport.Name   'error 430
        MsgBox sMsg, vbInformation, Application.ActiveWorkbook.Name
    End If

End Sub

strive4peace

Mar 27 2012, 01:51 AM

Hi Rick,

here is some code you can look at ... and maybe use instead. It documents Form and Report Names and RecordSources, among other things...

Document Query SQL, Form RecordSource, Report RecordSource, Object List
UtterAccess Code Archive
http://www.UtterAccess.com/forum/Code-Docu...l-t1251569.html

RCollard

Apr 9 2012, 03:39 PM

Crystal,

Thanks for the reply. However, my problem is specifically related to Excel automating Access. I have since learned that the problem is caused when the Access 14.0 instantiated object attempts to call into the wrong point in the Access 2002 executable. Late binding is the solution but that will also fail if there are multiple copies of Access running such as our runtime and another full copy. I had thought that GetObject(, "Access.Application.10") would find Access 2002 but it simply finds the first instance of Access started.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.