Please Make Make-Table Query Better (Embedded Flattery Offered)

[Bill (Unique as my name)]

I need code statements that save me two agonizing, finger wrenching
clicks on the mouse to execute the query.

Thank you all so much.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "qgas3" 'The name of the Make Table Query
DoCmd.OpenQuery stDocName, acNormal, acEdit
' Something here that gets me past the messages notifying me that
it is deleting
' all the records in the table and that it is pasting new records
in the table.
' Oh, by the way, did you know you're one of the most wonderful
persons in the world?

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

 

[Roger Carlson]

Two ways:
1) Use SetWarning to Off before the DoCmd and then set them back On after
the DoCmd

DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

2) Use Currentdb.Execute to run your query. The Execute method does not ask
for confirmation and it runs faster.
Currentdb.Execute "qgas3"

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

['69 Camaro]

Hi, Bill.

I need code statements that save me two agonizing, finger wrenching
clicks on the mouse to execute the query.

I couldn't agree with you more. Try:

Private Sub ExecQryBtn_Click()

On Error GoTo ErrHandler

Dim qry As QueryDef

Set qry = CurrentDb().QueryDefs("qgas3")
CurrentDb().Execute qry.SQL, dbFailOnError

CleanUp:

Set qry = Nothing

Exit Sub

ErrHandler:

'------------------------------------------------------------------
' Drop the table before executing the make table query.
'------------------------------------------------------------------

If (Err.Number = 3010) Then
Err.Clear
CurrentDb().Execute "DROP TABLE tblNew;", dbFailOnError
Resume
Else
MsgBox "Error in ExecQryBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
End If

Err.Clear
GoTo CleanUp

End Sub ' ExecQryBtn_Click( )

.. . . where qgas3 is the name of the make table query, and tblNew is the
name of the table that this query is creating.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.