run access append query from excel

PeterBaileyUk
PeterBaileyUk used Ask the Experts™
on
I tried this (found on ee) but hoping to run a query called qryTotalCWcarsYearsBreakdown but it fails on a.docmd.runmacro "qryTotalCWcarsYearsBreakdown"

so question is can i run an append query in this way from within excel?
Public Function RunAccessMacro()
     
'    Application.ScreenUpdating = False
'
'    Set A = CreateObject("Access.Application")
'
'    strDB = "N:\data\SimpleStats2.mdb"
'
'    A.OpenCurrentDatabase strDB
'    A.Visible = False
'    A.DoCmd.RunMacro "QryTotalCWCarsYearsBreakdown"
'    A.Quit
'
'    Set A = Nothing
'    Application.ScreenUpdating = True
     
End Function
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Try

A.DoCmd.RunQuery "YourQuery"

RunMacro would be used to run a Macro in the database.
MIS Liason
Most Valuable Expert 2012
Commented:
or:
A.Docmd.OpenQuery "YourQuery"

Author

Commented:
It stalls by saying object doesnt support this property or method runtime error 438

amended as advised to
Public Function RunAccessMacro()
     
    Application.ScreenUpdating = False

    Set A = CreateObject("Access.Application")

    strDB = "N:\data\SimpleStats2.mdb"

    A.OpenCurrentDatabase strDB
    A.Visible = False
   
   
 A.DoCmd.RunQuery "QryDeleteCW" ' delete table entries
A.DoCmd.RunQuery "QryTotalCWCarsYearsBreakdown" 'append to table
 A.DoCmd.RunQuery "QryDeleteSMMT" ' delete table entries
A.DoCmd.RunQuery "QryTotalSMMTCarsYearsBreakdown"  'append to table
    A.Quit

    Set A = Nothing
    Application.ScreenUpdating = True
     
End Function

Author

Commented:
As the queries were append queries I had to use the runsql command but your suggestions got me there thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial