I am trying to import records from tally erp 9 through xml.I have created database & form to import from xml file created by
tally(tally is name of my accounting software).its also importing database but every time i have to do this import thing
manually.Do any one have any macro for automatic importing the data from the specified xml file every time i open the ms
I am using ms access 2003.while importing data from the xml file i keep on the option "append data to existing table(s)" to
get only update entries (file>get external data>import>xml>options>append data to existing table(s))
Please help me out.....its very urgent......thank you.
Waiting for reply
while surfing i came accross one page from msdn database which might be useful
Bind an Access Form's Record Source from an XML File at Runtime
Microsoft® Office Access 2003
Summary: Learn how to bind a form dynamically to a recordset created from an XML file. This simple application can be the
starting point for a powerful solution for your customers and should be considered a part of your arsenal when developing
Access application. (8 printed pages)
Import XML and Create an Access Table
Function to Determine the Table Name
Routine to Bind a Recordset to an Unbound Form
With the prominence of Extensible Markup Language (XML), it is likely that at one time or another while developing solutions
in Microsoft® Office Access 2003, you will need to import and use data from an XML file. Likewise, there may be instances
where you need to bind the record source of a form at runtime. Fortunately, both of these operations are relatively easy to
create and are equally easy to combine into one. Among the benefits of combining the operations into one smooth function are
that you remove a level of complexity from your users. This also makes your solutions appear more professional as these
operations are completed behind the scenes and away from your customers.
This article demonstrates programmatically importing an XML file to an Access table. A recordset is then created from the
table and dynamically bound to a form. Note The examples in this article are written using the Data Access Objects (DAO)
library. For similar examples using ActiveX Data Objects (ADO), see the book Programming Microsoft Office Access 2003 from
Microsoft Press written by Rick Dobson.Overview
This application works by first prompting the user for the path and name of the XML file to be imported. The code then sets
a variable to the current date and time. This variable is used later to help find the table created during the import
operation. Next, the procedure imports the XML file and creates the table. Then, the RetrieveNewestTableName function is
called with the current data and time variable created earlier. This function retrieves the name of the table created during
the import. The returned value is then checked to make sure it isn't empty and thus, generate an error. Next, the user is
prompted for the name of the unbound form. The recordset will be bound to this form. Finally, the subroutine calls the
BindRstToUnboundForm function that binds the table's recordset to the form's Recordset property.
Next we'll look at the function that determines the name of the newly created table. To understand why this procedure is
necessary, consider that the table created during import is based on the name of the XML file. If the name of the input file
matches the name of an existing table in the database, the new table uses the name of the existing table concatenated with a
number incremented by 1. So, for example, if the name of the XML file is Employees.xml and the Employees table already exists
in the database, the new table is created as Employees1. If you import the file again and the Employees and Employees1 tables
exist, the new table is designated Employees2. So to make sure that the recordset is created from the correct table, it is
important to determine which table is the correct one.
To determine the correct table, the code loops through the list of tables and first checks for ImportErrors table and, if
found, ignores the table. The ImportErrors table is created if there are errors during the import of data from, for example,
the XML file. Because this could be listed as the latest table, it is eliminate in the code. Next, the variable containing
the current date and time is compared to the DateCreated property of the each table and the name of the latest table is
stored in a variable. The name of the table is then returned to the calling procedure.
Once the code determines the table name, the name of the table and form are passed to another function that creates a
recordset based on the table. The procedure then assigns the recordset to the Recordset property of the form. The form is
displayed and the code displays a test record in the Immediate window of the Microsoft Visual Basic® Editor to verify that
the routine is working correctly.
Import XML and Create an Access Table
The code in this section demonstrates how to use the ImportXML method to import data from an XML file and create a table.
Ensure that you have an XML and XSD file in the correct format such as those created by the ExportXML method. You create XML
and XSD files from Access by using the ExportXML method in a standard module in the Visual Basic Editor or on the File menu,
by clicking Export and then selecting XML in the Save as type drop-down box.
To use the ExportXML method, the following code example exports the table named Customers in the current database to an XML
file; the data and schema are exported as separate files.
Next, ensure that you have an unbound form with fields similar to the imported table to bind to the recordset. For the
purpose of this article, you can create one from the existing Customers form in the Northwind sample database. To do
this:Start Access and open the Northwind sample database. By default, this database is located at C:Program FilesMicrosoft
Click the Customers form.
On the File menu, click Save As, type a name for the new form (Customers1, for example), and then click OK.
Make sure that the Record Source property is blank by opening the form in Design view.
On the View menu, click Properties to open the Properties window for the form.
Click the All tab, scroll to the top to the Record Source box and clear it, if necessary.
Then, create another unbound form. Note This form is different that the form created in step 3 and is used to run the
procedures that make up the application.
Add a command button by performing the following steps:In the Database window, click Forms under Objects.
Click the New button on the Database window toolbar.
In the New Form dialog box, click Design View. Leave the table or query source drop-down box blank. Click OK.
Double-click a command button from the Toolbox to add it to the form. If the Toolbox isn't visible, click the View menu and
then click Toolbox.
Click Cancel on the Command Button Wizard dialog box.
Position the command button in the center of the form.
Add code to the command button with the following steps:With the form in Design view, double-click the command button. In the
Property window, click the Event tab, and then click the OnClick property.
Click Build button next to the property box to display the Choose Builder dialog box.
Double-click Code Builder to display the event procedure Sub and End Sub statements in the form module.
Next, set references to the Microsoft Office 11.0 Object Library library and the Microsoft DAO 3.6 Object Library library
with the following steps:On the Tools menu, click References.
In the Available References box, select the check box next to the Microsoft Office 11.0 Object Library library.
Next, select the check box next to the Microsoft DAO 3.6 Object Library library reference, if necessary.
Add the following code to the OnClick event procedure between Sub and End Sub statements:
Dim fd As Office.FileDialog
Dim strTableName As String
Dim strFormName As String
Dim datBeforeImport As Date
Const strFileExt = ".xml"
On Error GoTo Path_Err
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Filters.Add "XML", "*.XML"
'User didn't enter a file path.
If fd.SelectedItems.Count = 0 Then
MsgBox "You must select an " & _
"XML file. Please try again."
'Set variable later used to find table created
'by ImportXML method.
datBeforeImport = Now
'Invoke the ImportXML method against the xml file.
Application.ImportXML fd.SelectedItems(1), acStructureAndData
'Check to make sure that RetrieveNewestTableName
'function doesn't return an error code which would
'generate a false error in this procedure.
strTableName = RetrieveNewestTableName(datBeforeImport)
If strTableName = "" Then
'Prompt user for name of form to use.
strFormName = InputBox("Type the name of the form you want to " & _
"use. It should have the same fields as the " & _
"recordset (table) the form will be based on.")
BindRstToUnboundForm strTableName, strFormName
If Err.Number = 31527 Then
MsgBox "The XML file was not found. Check the spelling " & _
"or that the file exists and try again."
MsgBox "Operation aborted for the following reason. " & _
vbCrLf & "Error Number: " & Err.Number & " " & _
vbCrLf & "Error Description: " & " " & Err.Description
This subroutine essentially imports a XML file and creates an Access table. It also calls the other procedures in the
The procedure starts by displaying a file dialog box using the FileDialog method of the Application object, filtered for XML
files in order to prompt the user for the name and path to the XML file. Then, the code sets the variable that compares
against the creation date and time of the table created by the ImportXML method. Next, the subroutine calls the ImportXML
method to read the XML file and create a table for the data. The subroutine then calls the function RetrieveNewestTableName
and checks to see if that function has returned an empty String. Next, the procedure prompts the user for the name of the
form that the recordset will be bound to. Then the BindRstToUnboundForm function is called.
Function to Determine the Table Name
The following function cycles through a filtered list of tables in the database and determines the name of the table just
created during the import of the XML file.
Add the following function in the area outside of the previous subroutine.
Function RetrieveNewestTableName(datStartDate As Date) As String
Dim datDateComp As Date
Dim strNewestTableName As String
Dim tbl As DAO.TableDef
datDateComp = datStartDate
For Each tbl In CurrentDb.TableDefs
If tbl.DateCreated >= datDateComp Then
If Left(tbl.Name, 12) = "ImportErrors" Then
' Ignore ImportErrors tables
strNewestTableName = tbl.Name
datDateComp = tbl.DateCreated
RetrieveNewestTableName = strNewestTableName
This procedure determines the name of the table created during the import operation. It does this by comparing the
DateCreated property of the tables.
The procedure first sets a local variable to the date and time that later will be compared to the creation date and time of
each table. Then, the procedure loops through each table in the TableDefs collection of the CurrentDB object and, first,
determines if the current table is the ImportErrors table. If there were errors during the import process, the records that
weren't imported are stored in this table. Since this table might be the latest created, it would erroneously be bound to the
form if selected. Next, the For Each...Next loop compares the creation date to (initially) the current date. If the creation
date and time of the table is larger, then the name and creation date of the table are stored in variables. The cycle repeats
and the comparison is now between the last table that met the criteria and the current table. Either way, when all of the
lists of tables are examined, the code passes the name of the latest table to the RetrieveNewestTableName procedure and the
table name is then displayed to the user.
Routine to Bind a Recordset to an Unbound Form
The following subroutine illustrates dynamically assigning a recordset to an unbound form at runtime. Insert the procedure
to the area outside the previous function:
Function BindRstToUnboundForm(strDataSource As String, strFormName As String) As DAO.Recordset
On Error GoTo ErrorHandler
Set BindRstToUnboundForm = CurrentDb.OpenRecordset(strDataSource, dbOpenDynaset)
'Assign recordset to Recordset property of form.
Set Application.Forms(strFormName).Recordset = BindRstToUnboundForm
'Print record to check that things went smoothly.
Dim fld As DAO.Field
For Each fld In BindRstToUnboundForm.Fields
Debug.Print fld.Name & " = " & fld.Value
Select Case Err.Number
MsgBox "The form was not found. Check the spelling " & _
"or that the form exists and try again."
MsgBox "You must type a name for the form."
MsgBox "Operation aborted for the following reason. " & vbCrLf & _
"Error Number: " & Err.Number & " " & vbCrLf & _
"Error Description: " & " " & Err.Description
This subroutine starts out creating a recordset from the table created with XML data. It does by using the OpenRecordset
method of the CurrentDB object. Next, the unbound form is displayed using the OpenForm method of the DoCmd object. Then the
table's recordset is assigned to the Recordset property of the form. And finally, a test record is displayed to verify that
the application executed as expected.
Dynamically setting the record source of a form is a handy way to make your forms more versatile. Likewise, programmatically
importing an XML file to a table is a quick way to put that data to use. Combining the two operations hides a level of
complexity from your users. This simple application is the starting point for an even more powerful solution for your
customers and should be considered a part of your arsenal when developing Access application.