Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

RUNSQL Action in MACRO and VBA

Beginners of Microsoft Access can get confused with the usage of RUNSQL Action in Macro and DoCmd.RUNSQL method of Visual Basic for Applications (VBA). Whether you run this Action in Macro or in VBA you must provide SQL Statement of an Action-Query or Data-Definition Query Types only. If you are not sure which are these types of queries then refer the following list:

Action Query Types
Query Type Statement
Append INSERT INTO
Delete DELETE
Make-Table SELECT ... INTO
Update UPDATE

Data-Definition Query Types
Query Type Statement
Create a table CREATE TABLE
Alter a table ALTER TABLE
Delete a table DROP TABLE
Create an Index CREATE INDEX
Delete an Index DROP INDEX

Using SQL Statement of any other Query type in RUNSQL Action will end up in errors. In Macro the length of an SQL statement can be maximum 256 characters or less.

The DoCmd.RUNSQL method of VBA can execute an SQL statement with a maximum length of 32768 characters or less.

Note: You are not allowed to give an existing Query Name as parameter to this command. But, in VBA you can load the SQL Statement of a predefined query into a String Variable and use it as parameter to the DoCmd.RUNSQL command. 

Example-1:

Public Function DelRecs()
'Existing Delete Query’s SQL is used in this example
Dim db As Database, qryDef As QueryDef
Dim strSQL As String

'Read and save the SQL statement from 'Query48'
'and load into strSQL string variable
Set db = CurrentDb
Set qryDef = db.QueryDefs("Query48")
strSQL = qryDef.SQL

'Execute the SQL statement after
'disabling warning messages
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

'Using DoCmd.OpenQuery Command to run a Delete Query
DoCmd.SetWarnings False
DoCmd.OpenQuery "Query48", acViewNormal
DoCmd.SetWarnings True

End Function

The RUNSQL Action of Macro Modifies/Deletes information of several records in one go. Before executing the Action Query Microsoft Access gives out a warning message, appropriate to the action specified, and waits for the User's response. The User must respond to proceed/cancel the action specified.

Once the procedure is perfected, by test running the Action Query several times, we can ask Microsoft Access to turn OFF the Warning Messages during execution of RUNSQL Action. After the Query running step, give control back to MS-Access by turning ON the Warning Message detection. From that point onward MS-Access will be watching for the unexpected errors in the System and warns the User, as and when it happens.

The SetWarnings Action in Macro and DoCmd.SetWarnings Method in VBA are used for this purpose, when data processing for report involves one or more action query steps are placed in a Macro. Check the image of a Macro, given below in design view, with the SetWarnings settings, before and after the RUNSQL Action in the Macro. The first Action in the Macro is Setwarnings with the Parameter value NO to turn off the Warning Messages, when the RUNSQL Action executes in the next step. The SetWarnings Action with the Parameter value YES turns ON the Warning Messages in step three, so that MS-Access can take care of future unexpected System Errors of any kind, as and when it happens.

Example-2: Create a Table with the Data-definition SQL

Warning:Double-check that you are not using any existing table-name for this sample run.

Public Function DataDefQuery()
Dim strSQL As String

strSQL = "CREATE TABLE Books  (Title TEXT(50) NOT NULL, Author TEXT(50) NOT NULL, PublishDate DATE, Price CURRENCY)"

DoCmd.RunSQL strSQL

End Function

The OpenQuery Action in Macro and DoCmd.OpenQuery Method of VBA uses the name of a predefined Query (of any type) to open them in any of the following Views:

  • Design View
  • Datasheet View
  • Print Preview
  • Pivot Table
  • Pivot Chart

 

Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms Functions How Tos MS-Access Security Reports Class Module msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Collection Object Event Menus and Toolbars Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Dictionary Object Form Report Calculation Command Button Data Emails and Alerts Query RaiseEvent Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item msaccessprocess security advanced Access Security Add Auto-Number Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

Running Sum in MS-Access Query

Introduction. We are familiar with creating Auto-Numbers in Query Column, through an earlier Function created with the Name QrySeq(), publi...

Labels

Blog Archive

Recent Posts