Access VBA delete Table records with SQL using DoCMD.RunSQL Method

This Access VBA tutorial explains how to use VBA delete Table records with SQL without alert using DoCMD.RunSQL Method.

You may also want to read:

Access VBA run Query or run Action Query

Access delete Table records

In my previous post, I have explained how to delete Table records without using VBA, below is a quick recap.

Suppose we have created a table called student in Access, which contains 5 student records.

student
Student IDStudent Name
001Apple
002Betty
003Cathy
004David
005Elyse

In order to delete the record (the whole row) of Apple, create a new Query, add student table.

Under Design tab, click on Delete button. This will create a Delete Query.

access-delete-table-records-01

 

Add Student ID to the field, then type “001” in criteria, which is the student ID of Apple.

access-delete-table-records-02

 

To preview the result of Delete Query (which records will be deleted), click on the View button under Design.

To execute the Query (actually delete the records), click on Run button, then click on Yes to confirm delete row.

access-delete-table-records-03

 

Access VBA delete Table records

Similar to executing Delete Query in Access UI, you can delete Access Table records in VBA. First write a standard Delete SQL statement, then use DoCMD.RunSQL Method to execute the SQL.

For example, we have a student Table as below.

student
Student IDStudent Name
001Apple
002Betty
003Cathy
004David
005Elyse

 

Press ALT+F11 and insert the below code in Module.

Public Sub deleteRecord()
    DoCmd.SetWarnings False
    strSQL = "Delete * From [student] WHERE [Student ID]='002'"
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
End Sub

Writing DoCmd.SetWarings False is because we want to avoid Access generating the below Alert so that the subsequent statements can be executed smoothly.

 

Run the Sub, then you will see the below result where Student ID 002 is deleted.

student
Student IDStudent Name
001Apple
003Cathy
004David
005Elyse

 

Outbound References

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.runsql

Access VBA create Query using CreateQueryDef

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

expression.CreateQueryDef(Name, SQLText)
NameRequired/OptionalData TypeDescription
NameOptionalVariantA Variant (String subtype) that uniquely names the new QueryDef.
SQLTextOptionalVariantA Variant (String subtype) that is an SQL statement defining the QueryDef. If you omit this argument, you can define the QueryDef by setting its SQL property before or after you append it to a collection.

Example – Access VBA create Query

The below Procedure creates a new Query called tempQry.

Private Sub createQry()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim qdf As DAO.QueryDef
    Dim newSQL As String
    
    newSQL = "Select * From [employee_tbl] WHERE [Department]='HR'"
    Set qdf = db.CreateQueryDef("tempQry", newSQL)
End Sub

It is important to note that if you have string criteria in the SQL statement, you can use single quote instead of double quote.

If your SQL criteria is a variable, you can use & to join the statement. For example, you want to use variable depart instead of ‘HR’ in the [Department] criteria.

    newSQL = "Select * From [employee_tbl] WHERE [Department]=" & depart

Error Handling

If you already have a Query called tempQry, when you run the Procedure to create Query of the same name, you will receive an error message.

You can add an error handling to delete the Query if it already exists.

Private Sub createQry()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim qdf As DAO.QueryDef
    Dim newSQL As String
   
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "tempQry"
    On Error GoTo 0

    newSQL = "Select * From [employee_tbl] WHERE [Department]='HR'"
    Set qdf = db.CreateQueryDef("tempQry", newSQL)
End Sub

Run the new Query

At the end of the Procedure, you can use OpenQuery Method to open the Query.

DoCmd.OpenQuery ("tempQry")

 

Access VBA DoCmd.TransferText Method

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)
NameRequired/OptionalData TypeDescription
TransferTypeOptionalAcTextTransferTypeThe type of transfer you want to make. You can import data from, export data to, or link to data in delimited or fixed-width text files or HTML files. The default value is acImportDelim. Only acImportDelim, acImportFixed, acExportDelim, acExportFixed, or acExportMerge transfer types are supported in a Microsoft Access project (.adp).

NameValueDescription
acExportDelim2Export Delimited
acExportFixed3Export Fixed Width
acExportHTML8Export HTML
acExportMerge4Export Microsot Word Merge
acImportDelim0Import Delimited
acImportFixed1Import Fixed Width
acImportHTML7Import HTML
acLinkDelim5Link Delimited
acLinkFixed6Link Fixed Width
acLinkHTML9Link HTML
SpecificationNameOptionalVariantA string expression that’s the name of an import or export specification you’ve created and saved in the current database. For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file. To create a schema file, you can use the text import/export wizard to create the file. For delimited text files and Microsoft Word mail merge data files, you can leave this argument blank to select the default import/export specifications.
TableNameOptionalVariantA string expression that’s the name of the Microsoft Access table you want to import text data to, export text data from, or link text data to, or the Microsoft Access query whose results you want to export to a text file.
FileNameOptionalVariantA string expression that’s the full name, including the path, of the text file you want to import from, export to, or link to.
HasFieldNamesOptionalVariantUse True (–1) to use the first row of the text file as field names when importing, exporting, or linking. Use False (0) to treat the first row of the text file as normal data. If you leave this argument blank, the default (False) is assumed. This argument is ignored for Microsoft Word mail merge data files, which must always contain the field names in the first row.
HTMLTableNameOptionalVariantA string expression that’s the name of the table or list in the HTML file that you want to import or link. This argument is ignored unless the TransferType argument is set to acImportHTML or acLinkHTML. If you leave this argument blank, the first table or list in the HTML file is imported or linked. The name of the table or list in the HTML file is determined by the text specified by the <CAPTION> tag, if there’s a <CAPTION> tag. If there’s no <CAPTION> tag, the name is determined by the text specified by the <TITLE> tag. If more than one table or list has the same name, Microsoft Access distinguishes them by adding a number to the end of each table or list name; for example, Employees1 and Employees2.
CodePageOptionalVariantA Long value indicating the character set of the code page.

Export Access Query to TXT using DoCmd.TransferText Method

The below Procedure exports Query1 to the C:\test\ folder. The file will contain field name in the first row.

Public Sub export_query()
    DoCmd.TransferText transferType:=acExportDelim, TableName:="Query1", FileName:="C:\test\Query1.txt", hasfieldnames:=True
End Sub

Access VBA Export all Queries to Text File 01

By default, text files exported via DoCmd.TransferText Method contain double quote around the data and field name.

There is no argument in the Method that we can use to remove the double quote, fortunately there is workaround using Specification argument in the Method.

First, perform a manual export using non-VBA approach (right click on a Query > Export > Text File > OK > click on the Advanced button

Access VBA DoCmd.TransferText Method 05

 

In the Text Qualifier dropdown box, select none > click on Save As > OK

Access VBA Export all Queries to Text File 02

 

Now add one more parameter SpecificationName to the Procedure.

Public Sub export_query()
    DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="Query1 Export Specification", TableName:="Query1", FileName:="C:\test\Query1.txt", hasfieldnames:=True
End Sub

Double quotes are removed.

Access VBA DoCmd.TransferText Method 06

Export Access Query to CSV using DoCmd.TransferText Method

There is no argument in DoCmd.TransferText Method to define how data is separated, whether by comma, semi colon, tab, etc. Instead we should define it in the Specification first.

As explained earlier, export a Query in the non-VBA way, press Advance button to define a Specification.

Access VBA Export all Queries to Text File 03

Then we can write the below Sub Procedure using the above CSV specification, also change the file extension to .csv

Public Sub export_query()
    DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="CSV Specification", TableName:="Query1", FileName:="C:\test\Query1.csv", hasfieldnames:=True
End Sub

Export all Access Query using DoCmd.TransferText Method

For details refer to my previous post.

Public Sub export_query()
  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef

  Set db = CurrentDb()
  For Each qdf In db.QueryDefs
    DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="Query1 Export Specification", TableName:=qdf.Name, FileName:="C:\test\" & qdf.Name & ".txt", hasfieldnames:=True
  Next qdf
  Set qdf = Nothing
  Set db = Nothing
End Sub

Import TXT using DoCmd.TransferText Method

Prepare a TXT file

Access VBA DoCmd.TransferText Method 06

 

You can either create a blank Table in Access that will be used to import the TXT file, or let the Macro generate a  new one for you.

Access VBA Export all Queries to Text File 04

 

Again, you have to specify whether the text file is delimited by semi colon, tab, comma etc using the previously created Specification. Run the below Procedure.

Public Sub import_query()
    DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="Query1 Export Specification", TableName:="import_table", FileName:="C:\test\Query1.txt", hasfieldnames:=True
End Sub

Import all TXT files in the folder

Public Sub import()
    Dim FileName, FilePathName, Path, FileNameList() As String
    Dim FileCount As Integer

    DoCmd.SetWarnings False
    Path = "C:\test\"
    FileName = Dir(Path & "")
    
    While FileName <> "" And Right(FileName, 3) = "txt"
        FileCount = FileCount + 1
        ReDim Preserve FileNameList(1 To FileCount)
        FileNameList(FileCount) = FileName
        FileName = Dir()
    Wend
    
    If FileCount > 0 Then
        For FileCount = 1 To UBound(FileNameList)
            FilePathName = Path & FileNameList(FileCount)
            DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="Query1 Export Specification", TableName:="import_table", FileName:=FilePathName, hasfieldnames:=True
        Next
    End If
    DoCmd.SetWarnings True
End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff835958.aspx

 

Access VBA DoCmd.TransferSpreadSheet Method

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)
NameRequired/OptionalDescription
TransferTypeOptionalThe type of transfer you want to make. The default value is acImport.

NameValueDescription
acExport1The data is exported.
acImport0(Default) The data is imported.
acLink2The database is linked to the specified data source.
SpreadsheetTypeOptionalThe type of spreadsheet to import from, export to, or link to.

NameValueDescription
acSpreadsheetTypeExcel30Microsoft Excel 3.0 format
acSpreadsheetTypeExcel46Microsoft Excel 4.0 format
acSpreadsheetTypeExcel55Microsoft Excel 5.0 format
acSpreadsheetTypeExcel75Microsoft Excel 95 format
acSpreadsheetTypeExcel88Microsoft Excel 97 format
acSpreadsheetTypeExcel98Microsoft Excel 2000 format
acSpreadsheetTypeExcel129Microsoft Excel 2010 format
acSpreadsheetTypeExcel12Xml10Microsoft Excel 2010 XML format
TableNameOptionalA string expression that is the name of the Microsoft Office Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Access select query whose results you want to export to a spreadsheet.
FileNameOptionalA string expression that’s the file name and path of the spreadsheet you want to import from, export to, or link to.
HasFieldNamesOptionalUse True (–1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed. When you export Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you enter for this argument.
RangeOptionalA string expression that’s a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.
UseOAOptionalThis argument is not supported.

Example 1 – export Query to xlsx

Assume that you have the below Query

TransferSpreadSheet_02

The below code exports the above Query as “test.xlsx”

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "staff_list_with_grouping", "C:\test\test.xlsx", True

Now you get the below file. Note that the headers squeeze together.

TransferSpreadSheet_03

I usually write a VBA Procedure in Access to open the workbook automatically > auto fit > close it

Public Sub autoFormat()
    wkbookPath = "C:\test.xlsx"
    Dim XL As Object
    Set XL = CreateObject("Excel.Application")
    With XL
        .Visible = False
        .displayalerts = False
        .Workbooks.Open wkbookPath
        .Columns("A:XFD").EntireColumn.AutoFit
        .ActiveWorkbook.Close (True)
        .Quit
    End With
    Set XL = Nothing
End Sub

Alternatively, you may also use DoCmd.OutputTo Method, the syntax is slightly different, and you will get well formatted headers same as that in Access Query. However, the file size is larger and it takes longer time to export.

DoCmd.OutputTo acOutputQuery, "Query1", acFormatXLSX, "C:\test\test.xlsx", False

TransferSpreadSheet_01

Example 2 – import Excel (xlsx) to Access

The below code imports the data with headers of test.xlsx to Access table “importTable”. If the table does not exist, the table will be created. If the table already exists and contains data, the newly imported data will be added beginning from the last record. Creating a Table before import also ensure the data type of each field is what you expect; otherwise the data type will be automatically determined.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "importTable", "C:\test\test.xlsx", ", True

TransferSpreadSheet_04

If you rerun the Macro, a new set of data will be imported again.

TransferSpreadSheet_05

I have created another post specifically demonstrates how to import Excel to Access, including importing all workbook all worksheets to Access Table, click here to read.

Example 3 – export Query to Excel template

Assume that we have a formatted Excel template under C:\test\template.xlsx

TransferSpreadSheet 001

In Access, you have a Query

Query1
Employee IDName
001Wyman
002Mary
003Peter

In order to export Query 1 data to the template, Name the Excel Range B4:C10 as exportRange (you can define your own name). Your export Range can be larger than the actual export size. If you specify a Range name in the argument but it doesn’t exit in Excel, the argument name would become the Worksheet name.

Run the below Procedure in Access

Public Sub xportQuery()
    DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="Query1", FileName:="C:\test\template.xlsx", Range:="exportRange"
End Sub

Now the Query is inserted into the exportRange. Unfortunately the header must need to be included when export, there is no argument to control whether the header is exported.

TransferSpreadSheet 002

As a workaround, we can write a Macro in Access to delete row4 in Excel.

Public Sub xportQuery()
    DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="Query1", FileName:="C:\test\template.xlsx", Range:="exportRange"

    Dim XL As Object
        Set XL = CreateObject("Excel.Application")
        With XL
        .Visible = False
          .displayalerts = False
          .Workbooks.Open "C:\test\template.xlsx"
          .Rows("4:4").Delete
          .ActiveWorkbook.Close (True)
          .Quit
        End With
        Set XL = Nothing
End Sub

Now after the Query is exported, the Excel is opened again > delete row 4 > and automatically close the file

TransferSpreadSheet 003

If you are interested in learning how to run Excel Macro in Access, click here.

DoCmd.TransferSpreadsheet vs DoCmd.OutputTo

DoCmd.TransferSpreadsheet Method has other functions other than exporting, such as import files and exporting to an Excel template. In terms of exporting Query to XLSX, Transferspreadsheet is quicker and the file size is smaller because it does not contain any Query formatting, and error handling is different as well.

For exporting Query to Excel, I personally prefer OutputTo Method because I can adjust the column width in Query before export.

But there is one problem with DoCmd.OutputTo Method. If you export the Query to XLSX format. Some Access formats cannot be perfectly supported, and an error pops up when users try to open the exported workbook, and users have to save the file as a new one for auto repair. To work around this issue, you can export as XLS format instead.

DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="Query1", OutputFormat:=acFormatXLS, Outputfile:="C:\test\test.xls"

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff844793.aspx

Access VBA DoCmd.OutputTo Method

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, OutputQuality)
NameRequired/OptionalDescription
ObjectTypeRequiredType of object to export

NameValueDescription
acOutputForm2Form
acOutputFunction10User-Defined Function
acOutputModule5Module
acOutputQuery1Query
acOutputReport3Report
acOutputServerView7Server View
acOutputStoredProcedure9Stored Procedure
acOutputTable0Table
ObjectNameRequiredThe Name of the Object to export, such as Query Name, Report Name.
OutputFormatRequiredThe output format

acFormatHTML
acFormatPDF
acFormatRTF
acFormatSNP
acFormatTXT
acFormatXLS
acFormatXLSB
acFormatXLSX
acFormatPS
OutputFileRequiredA string expression that’s the full name, including the path, of the file you want to output the object to.For example, “C:\test\test.xlsx”
AutoStartOptionalType True to open the exported file, False to disable opening. Default is False.
TemplateFileOptionalA string expression that’s the full name, including the path, of the file you want to use as a template for an HTML, HTX, or ASP file.
EncodingOptionalThe type of character encoding format you want used to output the text or HTML data.
OutputQualityOptionalThe type of output device to optimize for. The default value is acExportQualityPrint.

NameValueDescription
acExportQualityPrint0The output is optimized for printing.
acExportQualityScreen1The output is optimized for onscreen display.

Example – export Query to xlsx (DoCmd.OutputTo acOutputQuery)

The below example export “Query1” to C:\test\test.xlsx.

DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="Query1", OutputFormat:=acFormatXLSX, Outputfile:="C:\test\test.xlsx"

You may also use DoCmd.TransferSpreadsheet Method, the syntax is slightly different.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query1", "C:\test\test", True

DoCmd.TransferSpreadsheet vs DoCmd.OutputTo

DoCmd.TransferSpreadsheet Method has other functions other than exporting, such as import files and exporting to an Excel template. In terms of exporting Query to XLSX, Transferspreadsheet is quicker and the file size is smaller because it does not contain any Query formatting, and error handling is different as well.

For exporting Query to Excel, I personally prefer OutputTo Method because I can adjust the column width in Query before export.

But there is one problem with DoCmd.OutputTo Method. If you export the Query to XLSX format. Some Access formats cannot be perfectly supported, and an error pops up when users try to open the exported workbook, and users have to save the file as a new one for auto repair. To work around this issue, you can export as XLS format instead.

DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="Query1", OutputFormat:=acFormatXLS, Outputfile:="C:\test\test.xls"

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff844793.aspx

Access Excel FSO File Methods

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 library.

You should be able to run FSO in Excel 2013. If you fail to run FSO Object, open VBE (ALT+F11) > Tools > References > Check the box Microsoft Scripting Runtine

loop_through_workbook

FileExists Method

Check if a file already exists in the directory, returning True or False. See the below example.

DeleteFile Method

DeleteFile Method deletes a File, returns an error if File does not exist.

Syntax

object.DeleteFile filespec[, force]
objectRequired. Always the name of a FileSystemObject.
filespecRequired. The name of the file to delete. The filespec can contain wildcard characters in the last path component.
forceOptional. Boolean value that is True if files with the read-only attribute set are to be deleted; False (default) if they are not.

Example

The below Procedures deletes a file if it already exists.

Public Sub delFile()
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FileExists("C:\Users\WYMAN\Desktop\testDel.xlsx") = True Then
        FSO.deleteFile ("C:\Users\WYMAN\Desktop\testDel.xlsx")
    End If
End Sub

MoveFile Method

MoveFile Method moves a file to another location. Error occurs if source or destination path cannot be found.

Syntax

object.MoveFile source, destination
objectRequired. Always the name of a FileSystemObject.
sourceRequired. The path to the file or files to be moved. The source argument string can contain wildcard characters in the last path component only.
destinationRequired. The path where the file or files are to be moved. The destination argument can’t contain wildcard characters.

Example

The below Procedures move a file from Desktop to C:\test\, overwriting existing file in destination.

Public Sub movFile()
    movFromPath = "C:\Users\WYMAN\Desktop\testDel.xlsx"
    movToPath = "C:\test\testDel.xlsx"
   
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FileExists(movFromPath) = True Then
        If FSO.FileExists(movToPath) = False Then
           FSO.movefile movFromPath, movToPath
        Else
           FSO.deleteFile movToPath
           FSO.movefile movFromPath, movToPath
        End If
    End If
End Sub

CopyFile Method

Copy a File to another location. If the File already exists in destination, an error occurs.

Syntax

object.CopyFile source, destination[, overwrite]
objectRequired. Always the name of a FileSystemObject.
sourceRequired. Character string file specification, which can include wildcard characters, for one or more files to be copied.
destinationRequired. Character string destination where the file or files from source are to be copied. Wildcard characters are not allowed.
overwriteOptional. Boolean value that indicates if existing files are to be overwritten. If True, files are overwritten; if False, they are not. The default is True. Note that CopyFile will fail if destination has the read-only attribute set, regardless of the value of overwrite.

Example

The below Procedures copy a File from Desktop to C:\test\, overwriting existing File.

Public Sub cpyFile()
    copyFromPath = "C:\Users\WYMAN\Desktop\testDel.xlsx"
    copyToPath = "C:\test\testDel.xlsx"
   
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FileExists(copyFromPath) = True Then
        FSO.copyfile copyFromPath, copyToPath, True
    End If
End Sub

Other File related Methods

Below are some less commonly used Methods related to File, click on the hyperlink to read the details.

GetFileReturns a File object corresponding to the file in a specified path.
GetFileNameReturns the last component of specified path that is not part of the drive specification.

 

Access Excel FSO Folder Methods

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 the MSDN library.

You should be able to run FSO in Excel 2013. If you fail to run FSO Object, open VBE (ALT+F11) > Tools > References > Check the box Microsoft Scripting Runtine

loop_through_workbook

FolderExists Method

Check if a folder already exists in the directory, returning True or False. See the below example.

CreateFolder Method

CreateFolder Method creates a Folder, it returns an error if Folder already exists.

Syntax

object.CreateFolder(foldername)
objectRequired. Always the name of a FileSystemObject.
foldernameRequired. String expression that identifies the folder to create.

Example

The below Procedures create a folder if not exists.

Public Sub mkFolder()
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FolderExists("C:\Users\WYMAN\Desktop\testDel") = False Then
        FSO.createFolder ("C:\Users\WYMAN\Desktop\testDel")
    End If
End Sub

DeleteFolder Method

DeleteFolder Method deletes a Folder regardless whether it has contents inside. The method returns an error if Folder does not exist.

Syntax

object.DeleteFolder folderspec[, force]
objectRequired. Always the name of a FileSystemObject.
folderspecRequired. The name of the folder to delete. The folderspec can contain wildcard characters in the last path component.
forceOptional. Boolean value that is True if folders with the read-only attribute set are to be deleted; False (default) if they are not.

Example

The below Procedures deletes a folder if it already exists.

Public Sub delFolder()
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FolderExists("C:\Users\WYMAN\Desktop\testDel") = True Then
        FSO.deleteFolder ("C:\Users\WYMAN\Desktop\testDel")
    End If
End Sub

MoveFolder Method

MoveFolder Method moves a folder to another location. Error occurs if source or destination cannot be found.

Syntax

object.MoveFolder ( source, destination );

objectRequired. Always the name of a FileSystemObject.
sourceRequired. The path to the folder or folders to be moved. The source argument string can contain wildcard characters in the last path component only.
destinationRequired. The path where the folder or folders are to be moved. The destination argument can’t contain wildcard characters.

Example

The below Procedures move a folder from Desktop to C:\, overwriting existing folder in destination.

Public Sub movFolder()
    copyFromPath = "C:\Users\WYMAN\Desktop\testDel"
    copyToPath = "C:\testDel"
   
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FolderExists(copyFromPath) = True Then
        If FSO.FolderExists(copyToPath) = False Then
           FSO.movefolder copyFromPath, copyToPath
        Else
           FSO.deleteFolder copyToPath
           FSO.movefolder copyFromPath, copyToPath
        End If
    End If
End Sub

CopyFolder Method

Copy a Folder to another location. If the Folder already exists in destination, an error occurs.

Syntax

object.CopyFolder ( source, destination[, overwrite] )
objectRequired. Always the name of a FileSystemObject.
sourceRequired. Character string folder specification, which can include wildcard characters, for one or more folders to be copied.
destinationRequired. Character string destination where the folder and subfolders from source are to be copied. Wildcard characters are not allowed.
overwriteOptional. Boolean value that indicates if existing folders are to be overwritten. If true, files are overwritten; if false, they are not. The default is true.

Example

The below Procedures copy a folder from Desktop to C:\, overwriting existing Folder.

I tested the overwrite argument but it fails to overwrite an existing folder, so I delete the folder instead.

Public Sub cpyFolder()
    copyFromPath = "C:\Users\WYMAN\Desktop\testDel"
    copyToPath = "C:\testDel"
   
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FolderExists(copyFromPath) = True Then
        If FSO.FolderExists(copyToPath) = False Then
           FSO.copyfolder copyFromPath, copyToPath
        Else
           FSO.deleteFolder copyToPath
           FSO.copyfolder copyFromPath, copyToPath
        End If
    End If
End Sub

Other Folder related Methods

Below are some less commonly used Methods related to Folder, click on the hyperlink to read the details.

GetFolderReturns a Folder object corresponding to the folder in a specified path.
GetParentFolderNameReturns a string containing the name of the parent folder of the last component in a specified path.
GetSpecialFolderReturns the special folder object specified.

 

Access Excel Scripting Dictionary store unique array item

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

Excel VBA filter value then copy data to new worksheet

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 duplicate values to be added in the associative array and make sure the array items are unique.

In  this article, I will explain the use of Dictionary, which is a standard object provided by the Microsoft Scripting Runtime (scrrun.dll) dependency.

If you are looking for tutorial for standard Array which allows duplicates, click here.

Access Excel VBA Scripting.Dictionary – Add Item

To begin, create the Dictionary Object using the below code.

Dim objDict As Object
Set objDict = CreateObject("Scripting.Dictionary")

The next step is to add an item to Dictionary using Add Method.

objDict.Add (Key, Item)
ArgumentDescription
KeyThe unique identifier in the array, can be String or Number. You may not add the same Key more than one times.
ItemThe value to store in array for particular Key

For example, you can add two names with employee ID 001 and 002 as Key.

objDict.Add "001", "John"
objDict.Add "002", "Mary"

Before you add another new item, you should check if key already exists in Dictionary.

objDict.exists(Key)

Access Excel VBA Scripting.Dictionary – Remove Item

You can delete specific item using Remove Method.

objDict.Remove Key

Or delete all items using RemoveAll Method.

objDict.RemoveAll

Access Excel VBA Scripting.Dictionary – Modify Item

You can rename a Key using Key Property

objDict.Key(old_key_name)= new_key_name

You can modify an Item using Item Property

objDict.Item(Key) = Item

Access Excel VBA Scripting.Dictionary – Return all Item or Key

To return all Items, use

objDict.Items()

To return all Keys, use

objDict.Keys()

You can count number of Items using Count Method

objDict.Count

Use For Each Loop to loop through all Items, for example

For Each k In objDict.Keys
    MsgBox (objDict.Item(k))
Next

Count Unique Value in a Range

This Function stores unique values in Dictionary and then counts how many unique values in a Range (case sensitive).

Public Function countUnique(rng As Range)
  Dim objDict As Variant
  Set objDict = CreateObject("Scripting.Dictionary")
 
  For Each rng1 In rng
    If Not objDict.exists(rng1.Value) Then
      objDict.Add rng1.Value, rng1.Value 'I don't care what key it is, I just want to store item in the key so that it becomes unique
    End If
  Next rng1
 
  countUnique = objDict.Count
End Function

I have a more practical use of dictionary where I add unique value in a column to the dictionary and then add Autofilter to filter data, finally export data to separate worksheet. Click here to read more.

Access Excel VBA Scripting.Dictionary – Example

Below is the sample code to remove duplicate in Text using Dictionary. Click here if you want to read details of this Function.

Public Function wUniqueStr(sinput As String, delimiter As String, Optional Compare As Integer = 0) As String
    Dim objDict As Object
    Dim arrInput As Variant
    Dim uniqStr As String
    arrInput = Split(sinput, delimiter)
    Set objDict = CreateObject("Scripting.Dictionary")
    If Compare = 0 Then 'case insensitive
        For i = 0 To UBound(arrInput) 
                If objDict.exists(UCase(Trim(arrInput(i)))) Then
            Else
                objDict.Add UCase(Trim(arrInput(i))), i
                uniqStr = uniqStr & arrInput(i) & delimiter
            End If
        Next i
        wUniqueStr = Left(uniqStr, Len(uniqStr) - Len(delimiter))
    Else  'case sensitive
        For i = 0 To UBound(arrInput)
                If objDict.exists(Trim(arrInput(i))) Then
            Else
                objDict.Add Trim(arrInput(i)), i
                uniqStr = uniqStr & arrInput(i) & delimiter
            End If
        Next i
        wUniqueStr = Left(uniqStr, Len(uniqStr) - Len(delimiter))
    End If
End Function

Outbound References

http://www.stealthbot.net/wiki/Scripting.Dictionary

Send Email in Access VBA using SendObject Method

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 signed up the system, the user will see he is sending himself a confirmation, not a confirmation sent from the server.

Note that before the email is sent, a security message will pop up, you must press “Allow” button to send the email.

Access_Conditional_Formatting_10

To get rid of the above message to set Allow as default action, you may want to read the below article, but I have not tested it personally.

http://www.tek-tips.com/faqs.cfm?fid=5230

Syntax of SendObject Method

Docmd.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)
ObjectTypeOptional. To attach data of Access Object in the email message.

Object NameValueDescription
acSendForm2Send a Form.
acSendModule5Send a Module.
acSendNoObject-1(Default) Don’t send a database object.
acSendQuery1Send a Query.
acSendReport3Send a Report.
acSendTable0Send a Table.

You will be prompted to choose a file format to attach before sending the email.

Access_Conditional_Formatting_09

ObjectNameOptional. Name of Object to include
OutputFormatOptional.
ToOptional. Recipient’s Email address, use comma to separate email. For example, “abc@a.com, def@b.com”
CcOptional. Cc Email address
BccOptional. Bcc Email address
SubjectOptional. Subject of Email
MessageTextOptional. Message of Email
EditMessageOptional. Set TRUE to edit message before send, FALSE to send without edit. Deafult is TRUE
TemplateFileOptional. The path of HTML file to use as template

Example of sending email using SendObject Method

The below code sends an email to receiver@hotmail.com and receiver2@hotmail.com.

DoCmd.SendObject , , , "receiver@hotmail.com,receiver2@hotmail.com",, , "This is the subject", "This is the content", False

The below code attach a table “enrollment” to receiver@hotmail.com

DoCmd.SendObject acSendTable ,"Enrollment" , , "receiver@hotmail.com,receiver2@hotmail.com",, , "This is the subject", "This is the content", False

Other methods to send email in Access VBA

There are two more alternatives in my another article, click the below link to read more

Send Email in Access VBA using CDO SendObject olMailItem

 Outbound References

https://msdn.microsoft.com/en-us/library/office/ff197046.aspx?f=255&MSPPError=-2147217396

 

Access DoCmd.OpenForm Method to open Access Form

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)
FormNameName of Form (in String)
ViewOptional. How you want to view the Form

NameValueDescription
acDesign1The form opens in Design view.
acFormDS3The form opens in Datasheet view.
acFormPivotChart5The form opens in PivotChart view.
acFormPivotTable4The form opens in PivotTable view.
acLayout6The form opens in Layout view.
acNormal0(Default) The form opens in Form view.
acPreview2The form opens in Print Preview.
FilterNameInput SQL String or existing Query name to use its Where Clause (but not Record Source)
WhereConditionInput Where Clause in String (without Where keyword)
DataModeOptional. How you want the input mode to be in Form view or Datasheet view

NameValueDescription
acFormAdd0The user can add new records but can’t edit existing records.
acFormEdit1The user can edit existing records and add new records.
acFormPropertySettings-1The user can only change the form’s properties. (Default)
acFormReadOnly2The user can only view records.
WindowModeOptional. Set the display Windows mode

NameValueDescription
acDialog3The form or report’s Modal and PopUp properties are set to Yes.
acHidden1The form or report is hidden.
acIcon2The form or report opens minimized in the Windows taskbar.
acWindowNormal0(Default) The form or report opens in the mode set by its properties.
OpenArgsOptional. OpenArgs property can also be referred to in macros and expressions. For example, suppose that the form you open is a continuous-form list of clients. If you want the focus to move to a specific client record when the form opens, you can specify the client name with the OpenArgs argument, and then use the FindRecord method to move the focus to the record for the client with the specified name.

Example of using SQL String in FilterName argument

FilterName argument extracts the criteria in SQL / Query to use in Form Filter. If you only want to write the criteria to use it in Form, use WhereCondition argument (see below).

The following code defines a SQL string and use its criteria (not Record Source) in Result_Form when Result_Form is opened.

emplID_global = EmplID_tb
bookingDate_global = DateSerial(Year(bookingDate_tb), Day(bookingDate_tb), Month(bookingDate_tb))
searchCriteria = "[Empl ID] =" & "'" & emplID_global & "'" & " AND [Booking Date]=#" & bookingDate_global & "#"    
searchSQL_global = "Select * From [Schedule_Table] WHERE " & searchCriteria
DoCmd.OpenForm "Result_Form", , searchSQL_global

Example of using Query in FilterName argument

The following code uses criteria in Query (tempQry) in Result_Form when Result_Form is opened.

DoCmd.OpenForm "Result_Form", , "tempQry"

Example of using String in WhereCondition argument

The following code uses searchCriteria as criteria (statement after WHERE Clause) in Result_Form when Result_Form is opened.

emplID_global = EmplID_tb
bookingDate_global = DateSerial(Year(bookingDate_tb), Day(bookingDate_tb), Month(bookingDate_tb))
searchCriteria = "[Empl ID] =" & "'" & emplID_global & "'" & " AND [Booking Date]=#" & bookingDate_global & "#"    
DoCmd.OpenForm "Result_Form", , ,searchCriteria

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff820845.aspx?f=255&MSPPError=-2147217396