need help in automatic updating database from xml (1 Viewer)

pushkar1986

Registered User.
Local time
Today, 13:24
Joined
Aug 24, 2009
Messages
14
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 access database?

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
Tim Getsch
Frank Rice
Microsoft Corporation
May 2004
Applies to:
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)
Contents

Introduction
Overview
Import XML and Create an Access Table
Function to Determine the Table Name
Routine to Bind a Recordset to an Unbound Form
Conclusion
Introduction

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.

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

    Application.ExportXML _
    ObjectType:=acExportTable, _
    DataSource:="Customers", _
    DataTarget:="Customers.xml", _
    SchemaTarget:="CustomersSchema.xsd"


  3. 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:
    1. Start Access and open the Northwind sample database. By default, this database is located at C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb.
    2. Click the Customers form.
    3. On the File menu, click Save As, type a name for the new form (Customers1, for example), and then click OK.
    4. Make sure that the Record Source property is blank by opening the form in Design view.
    5. On the View menu, click Properties to open the Properties window for the form.
    6. Click the All tab, scroll to the top to the Record Source box and clear it, if necessary.
  4. 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.​
  5. Add a command button by performing the following steps:
    1. In the Database window, click Forms under Objects.
    2. Click the New button on the Database window toolbar.
    3. In the New Form dialog box, click Design View. Leave the table or query source drop-down box blank. Click OK.
  6. 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.
  7. Click Cancel on the Command Button Wizard dialog box.
  8. Position the command button in the center of the form.
  9. Add code to the command button with the following steps:
    1. 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.
    2. Click Build button next to the property box to display the Choose Builder dialog box.
    3. Double-click Code Builder to display the event procedure Sub and End Sub statements in the form module.
  10. 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:
    1. On the Tools menu, click References.
    2. In the Available References box, select the check box next to the Microsoft Office 11.0 Object Library library.
    3. Next, select the check box next to the Microsoft DAO 3.6 Object Library library reference, if necessary.
  11. 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"
    fd.Show

    'User didn't enter a file path.
    If fd.SelectedItems.Count = 0 Then
    MsgBox "You must select an " & _
    "XML file. Please try again."
    Exit Sub
    End If

    '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
    Exit Sub
    End If

    '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

    Exit_Sub:
    Exit Sub

    Path_Err:
    If Err.Number = 31527 Then
    MsgBox "The XML file was not found. Check the spelling " & _
    "or that the file exists and try again."
    GoTo Exit_Sub
    Else
    MsgBox "Operation aborted for the following reason. " & _
    vbCrLf & "Error Number: " & Err.Number & " " & _
    vbCrLf & "Error Description: " & " " & Err.Description
    GoTo Exit_Sub
    End If


This subroutine essentially imports a XML file and creates an Access table. It also calls the other procedures in the application.
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.

  1. 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
    Else
    strNewestTableName = tbl.Name
    datDateComp = tbl.DateCreated
    End If
    End If
    Next tbl
    RetrieveNewestTableName = strNewestTableName
    Debug.Print RetrieveNewestTableName
    End Function


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.
DoCmd.OpenForm strFormName
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
Next

Exit_Sub:
Exit Function

ErrorHandler:
Select Case Err.Number
Case 2102
MsgBox "The form was not found. Check the spelling " & _
"or that the form exists and try again."
GoTo Exit_Sub
Case 2494
MsgBox "You must type a name for the form."
GoTo Exit_Sub
Case Else
MsgBox "Operation aborted for the following reason. " & vbCrLf & _
"Error Number: " & Err.Number & " " & vbCrLf & _
"Error Description: " & " " & Err.Description
GoTo Exit_Sub
End Select
End Function


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

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.

_________________________________________________________________
 

muskilh

Registered User.
Local time
Today, 03:54
Joined
Jun 8, 2009
Messages
14
From what I understand, you have the solution right there.
This line of code imports and appends to existing tables (if they exist, if not it creates the tables automatically):
Code:
Application.ImportXML strPathToXML, acAppendData

the only variable is "strPathToXML" which is a string holding the path to the XML file...

To automate it so it runs this everytime you open your database, create a macro named "AutoExec" and have that run your code module.

--Muskilh
 

pushkar1986

Registered User.
Local time
Today, 13:24
Joined
Aug 24, 2009
Messages
14
I dont know much about access & vb programming.can you give me the code & tell me how to use it(i mean i want it to execute while i open the database)?I am using access 2003.please answer in brief plz..as I dont know any thing in programming..but i want to get this done.

Please help me out with this.....thank you in advance
 

pushkar1986

Registered User.
Local time
Today, 13:24
Joined
Aug 24, 2009
Messages
14
Please help me out...please tell me how what & where to enter the code...As i am a commerce student i dont know any thing about access & vb programming but I need to complete this thats y need assistance.


thanking you in advance
 

muskilh

Registered User.
Local time
Today, 03:54
Joined
Jun 8, 2009
Messages
14
make a module and name it "nub", type copy/pase this in:
Code:
sub jaja()
    const strPathToXML as string = "C:\Path\To\File.xml"
   Application.ImportXML strPathToXML, acAppendData
end sub
Note: you change "C:\Path\To\File.xml" to whatever the path is to your xml file.

Then make a macro, name it "AutoExec"
==> Under ACtion choose "RunCode"
======>At the bottom where it says "Function Name", click in the white area and a littl ebutton with 3 dots should magically appear next to it. click that button.
=========>You should see a picture of a folder with "Functions" written next to it. double click on the folder.
=============>single click on your database's name (NOT "Built-In Functions")
================> in the next column select "nub"
====================>in the last column select "jaja" by doube clicking it. ("jaja" appears after choosing "nub")
=========================>press ok, save everything, close it.

Now, everytime the database is open, it'll import the xml file for you. Now I'm guessing what you want is to automate it, I'm not 100% sure how to do this, but try using the windows schedular to open your database daily.
 

pushkar1986

Registered User.
Local time
Today, 13:24
Joined
Aug 24, 2009
Messages
14
Problem solved thank you.....
 
Last edited:

tushek

New member
Local time
Today, 08:54
Joined
May 27, 2013
Messages
1
Hello,

one question. I have imported the xml to my access database,
created these steps:


make a module and name it "nub", type copy/pase this in:

Code:
sub jaja() const strPathToXML as string = "C:\Path\To\File.xml" Application.ImportXML strPathToXML, acAppendDataend sub
i change "C:\Path\To\File.xml" to myinternetsite/myxml.xml..

but problem is when i'am making a macro, and in all columns are the same like you write, only in the last column is nothing.. there must be jaja, but it's empty..

any sollution for this?

thanks a lot
 

Users who are viewing this thread

Top Bottom