Export multiple MS Access tables to XML file using VBA

Export table to Xml File

This article is about to export Microsoft Access table to XML file using VBA. MS Access has inbuilt function to export table to Xml file. But sometime we need to do it with VBA code to export data more dynamically. In this article we are create a sub routine for export access data to XML. For export data we are using ExportXML method. ExportXML method will help to export XML data, schemas and presentation information from Microsoft Access database engine. This method required the application object. objectType and dataSource are two required parameters for this function. objectType represents the type of AccessObject object to export and dataSource is the name of the AccessObject object to export. We can use the Application object to apply methods or property settings to the entire Microsoft Access application. In this article we are export the multiple tables to XML. When the ExportXML method is called from within an AccessObject object, the default behavior is to overwrite any existing files specified in any of the arguments.

For implementing this, firstly we need to create tables. And for implementation of method, we need to create sub routine. After implementing the method we have to debug the code. After successful debugging the message will prompt and show XML file is created as shown in Fig 1.1.

Export MS Access table to XML using VBA . Fig-1.1


For review the file we can open this file with any XML reader tool as shown in Fig 1.2.

Export MS Access table to XML using VBA . Fig-1.2


VBA code

Option Compare Database
function tableToxml()
Dim ObjMgt As AdditionalData
Dim ObjMgtdes As AdditionalData
Set ObjMgt = Application.CreateAdditionalData
Set ObjMgtdes = ObjMgt.Add("student")
ObjMgt.Add ("account")
ObjMgt.Add ("detail")
Application.ExportXML ObjectType:=acExportTable, DataSource:="tbl_mgt", DataTarget:="management.xml", AdditionalData:=ObjMgt
MsgBox ("Xml file created")
End function


It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. Access Guru will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.