running delete query or make table query from macro or event proce

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to run a delete query or a make table from a macro or from an
event procedure?

Will
 
hi,
vb - use the docmd.openquery action.
macro - use the open query action.
in essences they are the same.
I would use vb. 1 less object in the db.
with a macro, you would have the query object and the
macro object that runs the query object.
 
Is there a way to run a delete query or a make table from a macro or from an
event procedure?

Will
Click to expand...

Certainly. Several in fact. <g>

In a Macro you can use the RunQuery action.

Better, because it traps errors and lets you check results, is to use
an event procedure. The Execute method traps query errors and avoids
the annoying prompts that you may otherwise get:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim prm As Parameter
On Error GoTo Proc_Error
Set db = CurrentDb
Set qd = db.Querydefs("YourQueryName")
' the following lets you use Parameter queries; you can omit if the
' query has no parameters
For Each prm In qd.Parameters
prm.Value = Eval(prm.Name)
Next prm
' execute the query
qd.Execute dbFailOnError
If qd.RecordsAffected = 0
<warn the user that the query didn't do anything>
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "The query didn't work: Error " & Err.Number _
& vbCrLf & Err.Description
Resume Proc_Exit
End Sub


John W. Vinson[MVP]
 
Thanks, John, that's just what i needed!

Will

John Vinson said:
Certainly. Several in fact. <g>

In a Macro you can use the RunQuery action.

Better, because it traps errors and lets you check results, is to use
an event procedure. The Execute method traps query errors and avoids
the annoying prompts that you may otherwise get:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim prm As Parameter
On Error GoTo Proc_Error
Set db = CurrentDb
Set qd = db.Querydefs("YourQueryName")
' the following lets you use Parameter queries; you can omit if the
' query has no parameters
For Each prm In qd.Parameters
prm.Value = Eval(prm.Name)
Next prm
' execute the query
qd.Execute dbFailOnError
If qd.RecordsAffected = 0
<warn the user that the query didn't do anything>
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "The query didn't work: Error " & Err.Number _
& vbCrLf & Err.Description
Resume Proc_Exit
End Sub


John W. Vinson[MVP]
Click to expand...
 
Back
Top