Few important ms access VBA (DoCmd) objects

''********************** DoCmd objects in access VBA *********************************

'' This option will hide the alert messages like inserting/deleting any values in a table etc.
DoCmd.SetWarnings False

'' you can write down your sql queries but only DML queries will alloweded here"
DoCmd.RunSQL "Delete From Table_Employee"

'' By using this you can close any of the ms-access objects (like form, report, table, queries,macro, module etc.
'' change the acTable table parameter with any other above given access objects
DoCmd.Close acTable, "TableName", acSaveYes

'' By using this you can delete any of the ms-access objects (like form, report, table, queries,macro, module etc.
'' change the acTable table parameter with any other above given access objects
DoCmd.DeleteObject acTable, "TableName"

'' you can run/open any of your query object using this option (it require a Query object Name)
DoCmd.OpenQuery "QueryName"

'' By using this you can rename any of the ms-access objects (like form, report, table, queries,macro, module etc.
'' change the acTable table parameter with any other above given access objects
DoCmd.Rename "NewTableName", acTable, "OldTableName"

'' Hide ms access ribbon using Access VBA
DoCmd.ShowToolbar "Ribbob", acToolbarNo

'' Run your macros objects created in msacess
DoCmd.RunMacro "MacroName"

'' you can send your output automatically by the default email system (object Table, Query, form, Report etc.)
'' change the acSendTable table parameter and acFormatXLS as per your choice with any other above given access objects
DoCmd.SendObject acSendTable, "TableName", acFormatXLS, "abc@gmail.com", "xyz@yahoomail.com", "Test Email", "Hello"

'' Exit from msaccess
DoCmd.Quit acQuitSaveAll
Application.Quit acQuitSaveAll

Import CSV file into Ms Access database and Convert into XML

'Access VBA code to import the CSV file and create a table and then export the table's data into XML File.
'' Note: Open access database -> press Alt+F11 (to open the VBA Editor) ->Insert->Module
'' Copy the below code and paste into the module
' Press F5 to run the below code
'********************************************************************************
Sub Convert_CSV_TO_XML()
    Dim fPath As String
    Dim fOutputFile As String
   
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Title = "please select csv file"
        .Filters.Clear
        .Filters.Add "CSV Files", "*.csv"
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "file not selected...", vbOKOnly + vbInformation, "Task Cancelled"
            Exit Sub
        Else
            fPath = .SelectedItems(1)
            fOutputFile = CurrentProject.Path & "\"
        End If
    End With
   
    If fPath <> vbNullString Then
        DoCmd.TransferText acImportDelim, "", "Tbl_ImportData", fPath, True
        Application.ExportXML acExportTable, "Tbl_ImportData", fOutputFile & "\output.xml", fOutputFile & "\output.xsd"
        CurrentDb.Execute "Drop table Tbl_ImportData"
        MsgBox "file created in the below folder " & Chr(13) & fOutputFile, vbOKOnly + vbInformation, "XML file has been created"
    End If
End Sub

VBA code for unzip the files (multiple files can be selected)

Option Explicit
Sub Unzip_Files()
  Dim fCounter As Integer
  Dim UzipFolderPath As String
  Dim oApp As Object

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = True
    .Filters.Add "Zip Files", "*.zip"
    .Title = "please select zip files"
    .Show
    If .SelectedItems.Count = 0 Then
        MsgBox "file not selected", vbOKOnly + vbInformation, "Task Cancelled"
        Exit Sub
    Else
        'Extract the files into the newly created folder
        Set oApp = CreateObject("Shell.Application")
     
        UzipFolderPath = CurrentProject.Path & "\UnZippedFiles"
        MkDir UzipFolderPath
        For fCounter = 1 To .SelectedItems.Count
            oApp.Namespace(UzipFolderPath & "\").CopyHere oApp.Namespace(.SelectedItems(fCounter)).items
        Next
    End If
End With
    Set oApp = Nothing
End Sub

'************************************************************************
'check this out and let me if it works.
Note: The above code has been written in msaccess
So, if you want to run this in excel. change the above syntax:
* CurrentProject.Path to thisworkbook.path

Access VBA methods to delete ms access objects

'' Access VBA methods to delete ms access object

'************** Delete your table or query
DoCmd.RunSQL "Drop table yourTableName or yourQueryName"
CurrentDb.Execute "Drop table yourTableName or yourQueryName"


'*** Below method will allow to delete any of the access objects (Table, Query, Form, Report) *****
DoCmd.DeleteObject acQuery, "YourQueryName"

'Select any of the below access object types that you want to delete
'acTable
'acQuery
'acForm
'acReport

Access VBA Methods to export your Table, Query, Form, Report data

'Access VBA methods to export the data

'*******************You can export the data from a table , query********************

'acSpreadsheetTypeExcel12Xml for xlsx file
'acSpreadsheetTypeExcel12 for xlsb format
'acSpreadsheetTypeExcel9 for xls format

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qRY_Final", "Destination file path"


'*****************************************************************************
'Below method will allow to export the table, form, report, query data into (Excel,txt,pdf) formats
'acFormatXLS
'acFormatTXT
'acFormatPDF
'acFormatRTF
DoCmd.OutputTo acOutputForm, "frm", acFormatTXT, "Destination file path & filename & fileextenstion"

'change the acOutput type as per your choice to select access object type
'acOutputTable
'acOutputForm
'acOutputReport
'acOutputQuery

Access VBA to Import data from Excel File

!......VBA program to Import data from Excel file & Creates a Table......!

Sub Import_From_Excel()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "New TablenName", "Source file path"
End Sub