Unit 4 – Access VBA 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.

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.

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

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

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.

Outbound References

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

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)

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")

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)

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

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

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

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.

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.

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

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.

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

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)

Example 1 – export Query to xlsx

Assume that you have the below Query

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.

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

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

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

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

In Access, you have a Query

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.

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

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