DoComd.RunSQL works, Execute doesn't - WHY???

[Guest]

I would prefer to use the execute statement, but for some reason I get an
error message. Can someone PLEASE explain why.

'Code in either case
Dim strSQL As String, myTable As String, qdf As DAO.QueryDef
myTable = "tblM_" & "_" & Me.cboIUFList ' returns something like "tblM_1"
CurrentDb.TableDefs.Refresh
'I check to see if the table exists, if so I want to delete the contents
strSQL = "DELETE " & myTable & ".* FROM " & myTable & ";"

'this works
DoCmd.RunSQL strSQL

'this doesn't work - returns error "Item not found in this collection -
error 3265"
Set qdf = CurrentDb.QueryDefs(strSQL)
qdf.Execute dbSeeChanges

 

[Allen Browne]

You don't have a persistent Database variable:
Dim db As DAO.Database
Set db = CurrentDb()
Set qdf = db.QueryDefs("Query1")
qdf.Execute

Or this kind of thing:
With CurrentDb
.Execute strSql, dbFailOnError
Debug.Print .RecordsAffected
End With

CurrentDb gives a different object each time you call it.

 

[Stefan Hoffmann]

hi,

'this works
DoCmd.RunSQL strSQL

'this doesn't work - returns error "Item not found in this collection -
error 3265"
Set qdf = CurrentDb.QueryDefs(strSQL)
qdf.Execute dbSeeChanges

Use

CurrentDb.Execute strSQL

instead of your QueryDef.

Using

On Local Error Goto LocalError

Dim db As DAO.Database

Set db = CurrentDb

strSQL = "DELETE..."
db.Execute strSQL, dbFailOnError
MsgBox "Deleted " & db.RecordsAffected & " records."

Exit Sub

LocalError:
If Err.Number <> 3078 Then
MsgBox Err.Descrption
End If

gives you the number of deleted records and a error number of 3078 if
the table didn't exist.

mfG
--> stefan <--

 

[Guest]

To everone - thanks for the fast responses. Ready to implement now.

 

[A a r o n K e m p f]

you don't need

Delete * from

you just need

Delete From