Running An Append Query From Excel in Access Help (1 Viewer)

[ashleybyrdnc]

I am trying to run an append query in access from excel, but i need to turn the warrings off in access so you don't have to say yes through all the warnings. I have tried a bunch of diff things and nothing is working here is where I am right now.

here is my code in excel

Sub UpdateDatabase()

Dim objAcc As Access.Application
Set objAcc = New Access.Application
With objAcc
.OpenCurrentDatabase "\\Clr-database\Sales\Tech Action Items Database\Tech Action Items.mdb"
DoCmd.OpenModule "Jesse1", "Jesse1"
DoCmd.RunCommand acCmdCode
DoCmd.RunCommand acCmdRun
.CloseCurrentDatabase
End With
End Sub



And here is my code in access

Public Sub Jesse1()
With DoCmd
.SetWarnings False
DoCmd.OpenQuery "Update Jesse Ward Completed Tasks"
.SetWarnings True
End With


End Sub

but I am getting a 2501 Runtime error: The comand action was canceled


Please help and if there is another way to do it let me know.

Thanks!

 

[Trevor G]

If you are running a query you shouldn't need to go into the module.

Example below

Sub openaccess()
Dim objAcc As Access.Application
Set objAcc = New Access.Application
With objAcc
.Visible = True
.OpenCurrentDatabase "m:\Access Files\Test ME Today.mdb"
.DoCmd.SetWarnings False
.DoCmd.OpenQuery "Update Jesse Ward Completed Tasks"
.CloseCurrentDatabase
End With
End Sub

 

[dragofly]

DoCmd is a member of access.application and a global. But you may be confusing it without referencing it through you're application object. I.e. put a period in front of it and try that.

Also I don't have an acCmdCode

 

[SOS]

No SetWarnings necessary if you do it right:

Sub openaccess()
Dim db As DAO.Database

Set db = "m:\Access Files\Test ME Today.mdb"

db.Execute "Update Jesse Ward Completed Tasks", dbFailOnError
 
db.Close
 
Set db = Nothing

End Sub