open a acess module / procedure from excel

[testeng]

I am using Access and XL 2000 and through VBA I want xl to call a procedure in an access DB I have set a reference to access in xl but it still crashes each time it trys to set the object to a new access.application

here is my code what am I doing wrong and is there a better way to do it. I also I want to take even on step further and pass in variables to the procedure.

Sub RunFunction()

Dim sDBPath As String
Dim appAccess As Access.Application
sDBPath = Workbooks(1).Path & "\Mydb.mdb"
'It bombs here and tells me it can't create Active X object
Set appAccess = New Access.Application
appAccess.OpenCurrentDatabase sDBPath

appAccess.DoCmd.OpenModule "ModuleName", "Function"
'run the function
appAccess.DoCmd.RunCommand acCmdRun

Set appAccess = Nothing
End Sub

 

[Remou]

Have you set a reference to Microsoft Access x.x Object Library?

 

[testeng]

yes i did set the reference although by the error message telling active x cant create object it doesn't appear that it is really set.

 

[Remou]

Have you tried:

Set appAccess = CreateObject("Access.Application")

?

I tried your code myself, and apart from some doubts about Workbooks(1).Path, which is available for unsaved workbooks, it ran as far as opening the db.

 

[vbajock]

Is Access Security being used? If there is a password on the database, it could cause your code to fail.

 

[testeng]

remou
yes i did try late binding but it did not help same error

vbajock

No security yet this is all in development at the moment havn't gotten that far in yet.

The machine I am testing I had to install the dll for Access 2003 for testing another application. I am starting to think that has corrupted something. Later today I am going to test is on a machine that only has office 2000 installed and set what happens.

 

[pcast01]

Hello testeng,

Also the correct you should run is

appAccess.Run "Function"

The other code brings back the error: The command or action 'Run' isn't available now.

Just some FYI...

CHAOS, PANIC, & DISORDER - my work here is done.

 

[testeng]

Well that did it I tried on a machine with a clean install of office 2000 and the code worked fine. Sorry to have bothered everyone.

 

[Remou]

Sorry to have bothered everyone.

It is not a bother