Home  Fees/Services  Access 2010 Template  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links
Macro Programming Examples
Run SQL Query DoCmd
 





DoCmd AddMenu
DoCmd ApplyFilter
DoCmd CancelEvent
DoCmd Close
DoCmd CopyObject
DoCmd DeleteObject
DoCmd FindRecord
DoCmd FindNext
DoCmd GotoControl
DoCmd HourGlass
DoCmd Min/Max/Restore
DoCmd OpenForm
DoCmd OpenQuery
DoCmd OpenReport
DoCmd OutputTo
DoCmd RunSQL
DoCmd SendObject
DoCmd TransferDatabase
DoCmd TransferText

Create Export Specs

 

Microsoft Office:
  MS Access 2003
  Access 2007
  Access 2010
  Access 2013


Docmd.RunSQL Access Database Example

The Microsoft Access RunSQL method performs the RunSQL action in Visual Basic.  This command is used to execute sql query code within Access Visual Basic.
MS Access Runsql Example

SQL Statement -  A required variant string expression that is  a valid SQL statement for an action query or a data-definition query. Examples are Update, Delete, Insert Into, etc., queries.

Use Transaction - Optional Variant. Use True (�1) to include this query in a transaction. Use False (0) if you don't want to use a transaction. If you leave this argument blank, the default (True) is assumed.

MS Access RunSQL Example:

Public Sub RUN_Query

    Dim SQL_Text as String

    SQL_Text = "Delete * from M_Employees"

    Docmd.RunSQL (SQL_Text, false)

End Sub

The RunSQL command is one of the most powerful features of Access Visual Basic.  The programmer has the ability to perform all the logic and data validation functions within VBA and has the power of SQL all within the same environment. 

New! Download a running example of the Docmd.RunSQL Method

Such functions as creating temporary tables and building queries based on the user's responses to question creates an environment of almost infinite flexibility.

Below is an example of appending data to a temporary table based on the records within a master form on the screen.  This gives us programmatic control of the details form data.  Not that the records for the detail form comes from separate tables.  This query could also have been done using a Union query:

Private Sub Form_Current()
Dim SQLText As String

' JW Dean Blue Claw Database Design 7/25/2006
' disable editing if previous order number has been filledin
'
If IsNull(Me.Previous_Order_) = True Then
Me.AllowEdits = True
Else
Me.AllowEdits = False
End If


' clear out temp table
DoCmd.RunSQL "Delete * from t_orders"
'
'create and run the append queries
'
SQLText = "INSERT INTO T_Orders ( Order_Numb, ITEMDESC, XTNDPRCE, QUANTITY ) SELECT SOPNUMBE, ITEMDESC, XTNDPRCE, QUANTITY " & _
"FROM SOP10200 where SOPNumbe='" & Me.Previous_Order_ & "' or sopnumbe='" & Me.ReplOrder_ & "' or sopnumbe='" & Me.CR_ & "'"
'
DoCmd.RunSQL SQLText
'
'
SQLText = "INSERT INTO T_Orders ( Order_Numb, ITEMDESC, XTNDPRCE, QUANTITY ) SELECT SOPNUMBE, ITEMDESC, XTNDPRCE, QUANTITY " & _
"FROM SOP30300 where SOPNumbe='" & Me.Previous_Order_ & "' or sopnumbe='" & Me.ReplOrder_ & "' or sopnumbe='" & Me.CR_ & "'"
'
DoCmd.RunSQL SQLText
' refresh the form
Form_F_Previous_Details.Requery
End Sub

More runsql examples:

Docmd
DoCmd Run SQL, Run an SQL action query by using the SQL command. You may also run a DDL query. DoCmd Send Object, Include the specified datasheet, form,...

Inventory Calculations Example - Single User Databases
This Access database download shows how you can use the Docmd.Runsql statement in the after update (afterupdate) event to increment and decrement inventory ...

Multi-Select Parameter Forms
select name,address1,address2,city,state,zip from q_daily_prospect_Pitney" DoCmd.RunSQL (sqltext) Set rst = db.OpenRecordset("select count(name) as icount ...







Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013



A Blue Claw Database Design Template:

Meal Delivery Service Solution
 










A Blue Claw Database Design Article:

Repair Corrupted Access Table
 









Blue Claw Database Design Downloadable Tutorial:
TransferText & OutputTo Microsoft Access Download.  



Contact Information

Copyright 2000-2014 Blue Claw Database Design