In VBA, cannot use Access.Application object

Question

This does NOT work:

Sub X()
    Dim A As Access.Application
    Set A = CreateObject("Access.Application")
    'Do Stuff
End Sub

However, this DOES work:

Sub X()
    Dim A As Object
    Set A = CreateObject("Access.Application")
    'Do Stuff
End Sub

I know they do virtually the same thing, but can anyone tell me how to make an access.application object? I should add that I have Crystal Reports 11 and on my last upgrade, it may have 'unregistered' some VBA DLLs.

(Update 2009-06-29)

In response to the first 2 questions, I am using MS Access VBA to control some other Access & Excel files. Since this will only ever run on my local machine, I can guarantee that Access will always be installed. I have also referenced the "Microsoft Access 11.0 Object Library" (MSACC.OLB).

I know there's ways around this, i.e. use early binding when coding, and switch to late binding when running it, I just don't understand why the early binding method doesn't work at all on my machine (Of course, the code works fine on another machine with Access).

Solution 1

Hello,
The code that you say is not working is legal syntax. What error are you getting? When does it occur? Do you know the line of code it happens at?

Just as a side note, this is legal syntax as well:

    Dim accApp As Access.Application
    Set accApp = New Access.Application

But to be clear, the CreateObject Syntax is legal and not the source of the problem.

Solution 2

If you are writing this in Access there is no need to do that as the Application object is already there for you. If you are writing this in Excel or Word then you need to add a reference to the Access Library. Go to Tools/References and look for Microsoft Access XX Object Library

Solution 3

Try Detect And Repair from the Help menu in MS Access. Worked perfect for me.