Calling Access Sub from Excel

Question

I'm trying to call a subroutine in an Access database from Excel. This sub then call a number of other subroutines, all contained within the database. I saw a number of other posts where this was discouraged, but for reasons, Excel needs to be the front-end for this. I tried:

Sub TestRun()
        Dim acObj As Access.Application
        Set acObj = CreateObject("Access.Application")
        acObj.Application.Visible = True
        acObj.OpenCurrentDatabase "C:\testMDB\TEST.mdb", False, "password"
        acObj.Application.Run ("TestRunAccess")
End Sub

The database is part of a workgroup with a password - running it this way still prompts for the password. I'm not very familiar with Access - how would I go about doing this? What references would I need to include?

Solution

That would be:

''Reference: Microsoft Access x.x Object Library
Dim acObj As New Access.Application
''Set acObj = CreateObject("Access.Application")
acObj.Application.Visible = True
acObj.OpenCurrentDatabase "C:\testMDB\TEST.mdb",,"ADatabasePassword"
acObj.Application.Run "TestRunAccess"

You may prefer to use late binding if you wish to avoid problems with references, in which case:

Dim acObj As Object
Set acObj = CreateObject("Access.Application")
acObj.Application.Visible = True
acObj.OpenCurrentDatabase "C:\testMDB\TEST.mdb",,"ADatabasePassword"
acObj.Application.Run "TestRunAccess"

Comments

[tttt555]

I'm still running into trouble with needing to manually provide the password, however. Do you know how to solve that?

[Fionnuala]

Is this just a database password or are you using user level security? For a database password, see the edit, it is the same as you show in your example.

[tttt555]

I think it's user level security because I believed it's defined from the workgroup.

[Fionnuala]

For user level security, I think you will need: support.microsoft.com/kb/192919/EN-US

[tttt555]

Thanks, it seems like my problem is more with the user level security than actually accessing Access for now. I'll play with that part some more.

[Fionnuala]

There is only one way according to MS and that is to shell out to launch Access using the usual command line with arguments and then GetObject to get the application just launched.