help out! docmd.runsql "..." get error 2051

[holywin]

Using VBA in Excel 2002 , Access 2003 created a report tool .runs well.
until last week we update access2003 to 2007.(excel still 2002).
get error for grammar :" docmd.RunSQL "update .... " "
Error message show "The RunSQL action was canceled."

Reference:
1. Visual Basic for Application
C:\Program Files\Common Files\Micorsoft Shared\VBA\VBE6.DLL
2. OLE Automation
C:\WINDOWS\SYSTEM32\STDOLE2.TLB
3. Microsoft Access 12.0 Object Library
C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE12\MSACC.OLB
4. Microsoft Excel DAO Object Library
C:\Program Files\Common Files\Micorsoft Shared\DAO\DAO360.DLL
5. Microsoft ActiveX Data Objects 2.8 Library
C:\Program Files\Common Files\system\ado\msado15.tlb
6. Micorsoft Forms2.0 Object Library
C:\WINDOWS\SYSTEM32\FM20.DLL
7. Micorsoft Office 12.0 Object Library
C:\Program Files\Common Files\Micorsoft Shared\OFFICE12\MSO.DLL
8. Microsoft Excel 10.0 Object Library
C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\EXCEL10.EXE

 

[boblarson]

Well, perhaps it would be good to include the CODE you are using as well as the references. It is hard to say that there might be a coding problem if we can't see the code.

 

[holywin]

Thanks for the fast response. Please notice I am using Excel 2002 and access 2007.

code attached.

Sub run_sub_report()
Dim App As New Access.Application
Dim path As String

On Error GoTo Error_Handler

path = ActiveWorkbook.path
With App
.OpenCurrentDatabase (path & "\CRUNCH.MDB")
DoCmd.RunSQL "delete * from tbl_sub"
End With

Exit_Procedure:

App.CurrentDb.Close
Exit Sub
Error_Handler:
MsgBox "An error has occurred in ReportMarco. Please follow information list below: " _
& vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
& Err.Description, _
Buttons:=vbYesNo

Resume Exit_Procedure

End Sub

 

[boblarson]

So you are trying to run this query IN ACCESS but from Excel code?

 

[holywin]

Exactly!
it works in excel 2002 and access 2003.
now we just update access to 2007.
This happened.
I test in excel 2007 and access 2007.
define a string for the SQL statement
strSQL = "delete ...."
app.docmd.runSQL strSQL
it also work.
but our office be approved only updating access to 2007.
this still got error 2051.
gave me headache!
I think it maybe caused by reference lib file.

 

[boblarson]

Exactly!
it works in excel 2002 and access 2003.
now we just update access to 2007.
This happened.
I test in excel 2007 and access 2007.
define a string for the SQL statement
strSQL = "delete ...."
app.docmd.runSQL strSQL
it also work.
but our office be approved only updating access to 2007.
this still got error 2051.
gave me headache!
I think it maybe caused by reference lib file.

Just use DAO to run the query.
(make sure you have the reference set to DAO 3.x (where x is 51 or 6) in Excel.

Dim db As DAO.Database
Dim path As String
Dim strSQL As String

path = ActiveWorkbook.path

strSQL = "DELETE * from tbl_sub"

Set db = OpenDatabase(path & "\CRUNCH.MDB")

db.Execute strSQL, dbFailOnError

db.Close

Set db = Nothing

 

[holywin]

Thanks boblarson !
current my reference is
1. Visual Basic for Application
C:\Program Files\Common Files\Micorsoft Shared\VBA\VBE6.DLL
2. OLE Automation
C:\WINDOWS\SYSTEM32\STDOLE2.TLB
3. Microsoft Access 12.0 Object Library
C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE12\MSACC.OLB
4. Micorsoft Forms2.0 Object Library
C:\WINDOWS\SYSTEM32\FM20.DLL
5. Micorsoft Office 12.0 Object Library
C:\Program Files\Common Files\Micorsoft Shared\OFFICE12\MSO.DLL
6. Microsoft Excel 10.0 Object Library
C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\EXCEL10.EXE
7. Microsoft DAO 3.6 Object Library
C:\Program Files\Common Files\Micorsoft Shared\DAO\DAO360.DLL

but still won't go through
docmd.runSQL strSQL

Thanks again

 

[boblarson]

docmd.runSQL strSQL

DO NOT USE docmd.runSQL strSQL

USE THIS CODE INSTEAD:

Dim db As DAO.Database
Dim path As String
Dim strSQL As String

path = ActiveWorkbook.path

strSQL = "DELETE * from tbl_sub"

Set db = OpenDatabase(path & "\CRUNCH.MDB")

db.Execute strSQL, dbFailOnError

db.Close

Set db = Nothing

 

[holywin]

bob,you are great! It works.

But I define an access.application because we have lot qureies being setup in an Access file. some report analysis don't use VBA.
My VBA's function is focus on automation the process and format output.
the Report analyst don't need to know VBA, and they can just change qureies when they working.
Unfortunately, the access application object seem not work the same way as 2003,
although now we fix the docmd.runsql using the object database.
a new charllenge appear. it is docmd.openqurey .
As I said , there is no way to define every qurey because Report Analyst need to change them in access.
why the method in object access application 's docmd not working same way as before? Is the lib in different version change?
Appreciate for your help.

 

[boblarson]

holywin -

I am still a bit confused as to why you aren't just having the users work in Access. It seems to me that would eliminate a lot of your problems here.

 

[holywin]

Finally resolved.
It is just a trust center setting of Access 2007.
The VBA code in Excel will show this kind of errors,unless set the VBA program as an trusted resource for the Access file you refer to.

Thanks lot boblarson man!!!
I am new to access 2007.
I thought just enable Macro is enough for runing VBA, actually need also setup trust center.