running Access procedure from Excel
 

Hello,

I'm trying to run a procedure in access using excel VBA. I set the
access object library reference and ran this code but it hangs on the
appAcc.run command; Run-time error '440': Method 'Run' of object
'_Application' failed.


Sub GetKenyaAPRdata()
Dim appAcc As Access.Application
Set appAcc = New Access.Application
appAcc.Visible = False
appAcc.OpenCurrentDatabase ("C:\PMS\PEPFARresults.mdb")
appAcc.Run "getdata_Kenya"
appAcc.Quit
Set appAcc = Nothing
End Sub

Thanks in advance!

Dave

running Access procedure from Excel
 

is "getdata_Kenya" the name of a query in your database? A module
name? A sub procedure name? If it's a query, you could use:
appAcc.DoCmd.OpenQuery "getdata_Kenya"

If a function, try this:
appAcc.DoCmd.OpenFunction "getdata_Kenya"
savalou wrote:
Hello,

I'm trying to run a procedure in access using excel VBA. I set the
access object library reference and ran this code but it hangs on the
appAcc.run command; Run-time error '440': Method 'Run' of object
'_Application' failed.


Sub GetKenyaAPRdata()
Dim appAcc As Access.Application
Set appAcc = New Access.Application
appAcc.Visible = False
appAcc.OpenCurrentDatabase ("C:\PMS\PEPFARresults.mdb")
appAcc.Run "getdata_Kenya"
appAcc.Quit
Set appAcc = Nothing
End Sub

Thanks in advance!

Dave

running Access procedure from Excel
 

On Sep 14, 2:16 pm, JW wrote:
is "getdata_Kenya" the name of a query in your database? A module
name? A sub procedure name? If it's a query, you could use:
appAcc.DoCmd.OpenQuery "getdata_Kenya"

If a function, try this:
appAcc.DoCmd.OpenFunction "getdata_Kenya"



savalou wrote:
Hello,

I'm trying to run a procedure in access using excel VBA. I set the
access object library reference and ran this code but it hangs on the
appAcc.run command; Run-time error '440': Method 'Run' of object
'_Application' failed.

Sub GetKenyaAPRdata()
Dim appAcc As Access.Application
Set appAcc = New Access.Application
appAcc.Visible = False
appAcc.OpenCurrentDatabase ("C:\PMS\PEPFARresults.mdb")
appAcc.Run "getdata_Kenya"
appAcc.Quit
Set appAcc = Nothing
End Sub

Thanks in advance!

Dave- Hide quoted text -

- Show quoted text -

It's a sub procedure.

Thanks,

Dave

running Access procedure from Excel
 

Works for me. Could this be a security issue where you need to enable macros
to be run? In Access 2003 I specifically have to click "Open" in a dialog
because it has code in it.

"savalou" wrote in message
oups.com...
Hello,

I'm trying to run a procedure in access using excel VBA. I set the
access object library reference and ran this code but it hangs on the
appAcc.run command; Run-time error '440': Method 'Run' of object
'_Application' failed.


Sub GetKenyaAPRdata()
Dim appAcc As Access.Application
Set appAcc = New Access.Application
appAcc.Visible = False
appAcc.OpenCurrentDatabase ("C:\PMS\PEPFARresults.mdb")
appAcc.Run "getdata_Kenya"
appAcc.Quit
Set appAcc = Nothing
End Sub

Thanks in advance!

Dave

running Access procedure from Excel
 

On Sep 14, 3:24 pm, <- wrote:
Works for me. Could this be a security issue where you need to enable macros
to be run? In Access 2003 I specifically have to click "Open" in a dialog
because it has code in it.

"savalou" wrote in message

oups.com...



Hello,

I'm trying to run a procedure in access using excel VBA. I set the
access object library reference and ran this code but it hangs on the
appAcc.run command; Run-time error '440': Method 'Run' of object
'_Application' failed.

Sub GetKenyaAPRdata()
Dim appAcc As Access.Application
Set appAcc = New Access.Application
appAcc.Visible = False
appAcc.OpenCurrentDatabase ("C:\PMS\PEPFARresults.mdb")
appAcc.Run "getdata_Kenya"
appAcc.Quit
Set appAcc = Nothing
End Sub

Thanks in advance!

Dave- Hide quoted text -

- Show quoted text -

I have the macros enabled. This is working for me now. I was getting
an error message, but it was actually peforming the sub routine, so I
put an 'On error resume next' before the appAcc.Run "getdata_Kenya" .
Still don't know what the error is all about.

thanks again for your time.

Dave

running Access procedure from Excel
 

Looks like there's something in "getdata_Kenya" that's erroring out.
Possibly the error doesn't happen when you run it from Access, but just from
Excel?


"savalou" wrote in message
oups.com...
On Sep 14, 3:24 pm, <- wrote:
Works for me. Could this be a security issue where you need to enable
macros
to be run? In Access 2003 I specifically have to click "Open" in a dialog
because it has code in it.

"savalou" wrote in message

oups.com...



Hello,

I'm trying to run a procedure in access using excel VBA. I set the
access object library reference and ran this code but it hangs on the
appAcc.run command; Run-time error '440': Method 'Run' of object
'_Application' failed.

Sub GetKenyaAPRdata()
Dim appAcc As Access.Application
Set appAcc = New Access.Application
appAcc.Visible = False
appAcc.OpenCurrentDatabase ("C:\PMS\PEPFARresults.mdb")
appAcc.Run "getdata_Kenya"
appAcc.Quit
Set appAcc = Nothing
End Sub

Thanks in advance!

Dave- Hide quoted text -

- Show quoted text -

I have the macros enabled. This is working for me now. I was getting
an error message, but it was actually peforming the sub routine, so I
put an 'On error resume next' before the appAcc.Run "getdata_Kenya" .
Still don't know what the error is all about.

thanks again for your time.

Dave