Access VBA create Query using CreateQueryDef

Posted on January 18, 2017February 1, 2017 by Wyman W

This Access tutorial explains how to create Query in Access VBA using CreateQueryDef Method. You may also want to read: Change Query criteria Access VBA create Query Sometimes we don't want to create Query in Query Design View, because you may create a Query with criteria depending on a variable. To create Query in Access VBA, you can directly write a SQL statement in Access VBA, and then create a Query using the SQL statement with CreateQueryDef Method. Syntax of CreateQueryDef expres...

Access VBA DoCmd.TransferText Method

Posted on January 10, 2016June 12, 2017 by Wyman W

This Access VBA tutorial explains how to import and export Access Objects to text using DoCmd.TransferText Method. You may also want to read Access VBA Export all Queries to Excel Access VBA import txt using DoCmd.TransferText Method Syntax of DoCmd.TransferText Method DoCmd.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage) Name Required/Optional Data Type Description TransferType Optional AcTextTransferType T...

Access VBA DoCmd.TransferSpreadSheet Method

Posted on July 20, 2015August 28, 2017 by Wyman W

This Access tutorial explains how to use DoCmd.TransferSpreadSheet Method to export and import files. You may also want to read: Access VBA DoCmd.OutputTo Method Access VBA auto generate mass report by group to Excel Access Export all Queries to Excel Syntax of DoCmd.TransferSpreadSheet Method TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA) Name Required/Optional Description TransferType Optional The type of transfe...

Access VBA DoCmd.OutputTo Method

Posted on July 19, 2015February 5, 2017 by Wyman W

This Access tutorial explains how to use DoCmd.OutputTo Method and DoCmd.TransferSpreadSheet Method to export different objects to specific file types, for example, export Query to xlsx. You may also want to read: Access VBA auto generate mass report by group to Excel Access VBA DoCmd.TransferSpreadSheet Method Access Export all Queries to Excel Syntax of DoCmd.OutputTo Method DoCmd.OutputTo(ObjectType, ObjectName, OutputFormat, OutputFile, AutoStart, TemplateFile, Encoding, Output...

Access Excel FSO File Methods

Posted on May 5, 2015May 5, 2015 by Wyman W

This Access / Excel tutorial explains how to use FSO File related Methods including FileExists, DeleteFile, MoveFile, CopyFile You may also want to read: Loop workbooks in folders and subfolders Access Excel FSO Folder Methods FSO File Methods FileSystemObject (FSO) provides an API to access the Windows filesystem such as accessing Drive, TextStram, Folder, File. In this tutorial, I will only explain the use of some important File related Methods. For details, please read the MSDN lib...

Access Excel FSO Folder Methods

Posted on May 4, 2015May 5, 2015 by Wyman W

This Access / Excel tutorial explains how to use FSO Folder related Methods including FolderExists, CreateFolder, DeleteFolder, MoveFolder, CopyFolder You may also want to read: Loop workbooks in folders and subfolders FSO File Methods FSO Folder Methods FileSystemObject (FSO) provides an API to access the Windows filesystem such as accessing Drive, TextStram, Folder, File. In this tutorial, I will only explain the use of some important Folder related Methods. For details, please read...

Access Excel Scripting Dictionary store unique array item

Posted on April 7, 2015February 26, 2017 by Wyman W

This Access Excel tutorial explains how to use Scripting.Dictionary in VBA to store unique array item. You may also want to read: Access Excel VBA Array Remove duplicates in text using Dictionary Access Excel VBA Scripting.Dictionary to store unique array item An associative array, map, symbol table, or dictionary is an abstract data type composed of a collection of (key, value) pairs, such that each possible key appears just once in the collection. Put it simply, you can avoid duplica...

Send Email in Access VBA using SendObject Method

Posted on March 19, 2015March 19, 2015 by Wyman W

This tutorial explains how to send Email in Access VBA using SendObject Method Send Email in Access VBA using SendObject Method To send email using SendObject Method is simple, because it does not require SMTP sever address and port number. When you run SendObject, whoever run the code will trigger to send email from its Outlook, instead of sending from a specific server email. It is a little bit strange because if you develop an application that sends confirmation email to user who have just ...

Access DoCmd.OpenForm Method to open Access Form

Posted on March 7, 2015April 14, 2015 by Wyman W

This tutorial explains how to use Access DoCmd.OpenForm Method to open Access Form in Access VBA Access DoCmd.OpenForm Method - open Access Form DoCmd.OpenForm Method is used to open Access Form. Syntax of Access DoCmd.OpenForm Method- open Access Form DoCmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs) FormName Name of Form (in String) View Optional. How you want to view the Form Name Value Description acDesign 1 The for...

Access VBA DoCmd.RunSQL Method and Error 2342

Posted on March 6, 2015March 6, 2015 by Wyman W

This tutorial explains how to use Access VBA DoCmd.RunSQL Method command and cause of Error 2342 (due to using non-action query such as Select statement) Access VBA DoCmd.RunSQL Method and Error 2342 DoCmd.RunSQL Method runs specified SQL statement. The SQL statement can only be action query (such as create table, modifying data), but not Select statement. Syntax of DoCmd.RunSQL Method DoCmd.RunSQL(SQLStatement, UseTransaction) SQLStatement A string expression that's a valid SQL st...