Re: Can I programmatically IMPORT data from XML into Access?

Tech-Archive recommends: Fix windows errors by optimizing your registry

I'm afraid I can't really be very specific, for two reasons.

First, I know Access 2000 didn't have the ImportXML method, but I'm not sure
whether it was first introduced in Access 2002 or Access 2003. Perhaps
someone else can answer that?

Second, *if* both versions support the method, then the question arises of
how you reference Access in your C# app. In VBA or 'classic' VB, if you
wanted an app to work with more than one version of an external component,
you'd have to use late binding. But I'm not sure how that works in .NET and
COM Interop. You may need to ask that question in a .NET app if you really
want to pursue this option.

Given the overhead involved in COM Interop and Automation, I think it is
extremely unlikely that going that route would improve performance. For
example, on the PC I'm using to type this, it takes 5 seconds to launch
Access. How many records can I write to a database in 5 seconds? Well, let's
find out. This is probably not a very accurate test, but I'm not looking for
high levels of accuracy here, just a ballpark comparison ...

Public Sub TestSub3()

Dim dtmStart As Date
Dim rst As ADODB.Recordset

CurrentProject.Connection.Execute "DELETE * FROM tblTest"
dtmStart = Now
Do Until DateDiff("s", dtmStart, Now()) >= 5
CurrentProject.Connection.Execute "INSERT INTO tblTest (TestText)
VALUES ('" & CStr(Now()) & "')"
Loop
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open "SELECT Count(*) AS TheCount FROM tblTest"
Debug.Print rst.Fields("TheCount")
rst.Close

End Sub

Result in the Immediate window ...

testsub3
3560

So, given that I can write 3,560 records to a database in the time it takes
to load Access into memory, I really don't think you're going to gain any
performance advantages from going the automation route.

As for general tips, I suspect what you need may be a *specific* tip - if
you can provide more information about how you're writing to the database
and where the data is coming from, then perhaps someone may be able to
advise.

--
Brendan Reynolds (MVP)

"Ariela-G" <Ariela-G@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E1FC77CF-A74D-4FD0-AD48-EB063EFBFCC8@xxxxxxxxxxxxxxxx
> Thank you for your answer.
> a) Can you be more specific about the Access version required for the
> installation?
> b) Currently my application always inserts new rows (no updates).
> Do you have any general tips regarding the performance of writing into a
> database?
>
> Regards,
>
>
> "Brendan Reynolds" wrote:
>
>> The Access.Application object has an ImportXML method. However, to use it
>> you would have to automate Access, which would mean that a) your users
>> must
>> have a specific version of Access installed, and b) you'd have to use COM
>> Interop, which has its own performance implications.
>>
>> You might be better advised to investigate why writing to the database is
>> taking so long.
>>
>> --
>> Brendan Reynolds (MVP)
>>
>> "Ariela-G" <Ariela-G@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:5852DE6E-0B3E-45C9-BB9A-0D54167E415F@xxxxxxxxxxxxxxxx
>> >I have a .NET application (C#) that uses Access Database as the
>> >application's
>> > storage.
>> > I found-out that writing the data directly into an .mdb file takes much
>> > longer than writing from DataSet to XML, and than manually importing
>> > that
>> > content into the .mdb file. I need a way to complete this process
>> > programmatically and not manually.
>> >
>> > Please advise.
>> >
>>
>>
>>


.

Relevant Pages

  • Re: Can I programmatically IMPORT data from XML into Access?
    ... >From your test results I realize that my result is quite expected (writing ... > Given the overhead involved in COM Interop and Automation, ... How many records can I write to a database in 5 seconds? ... > performance advantages from going the automation route. ...
    (microsoft.public.access.externaldata)
  • Re: Bit flipping .. will this work in VB6?
    ... such as you or another programmer on the project writing ... reporting some odd behavior can send me the file they are working on, ... in the database even though they had 190gig free on the hdd. ... If you're writing a financial app though.... ...
    (microsoft.public.vb.general.discussion)
  • Re: Structuring of large application
    ... Have you thought about possibly writing the business logic & data layer in ... one application as a web service, and writing the UI in your regular web ... Again your central login database could be another web services ... > At the centre is the User login facility - everyone using the site must ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: general archetecture questions.
    ... > first app i'm writing will be using XML as a database of sorts, using> dataset and dataview and wrapped up in a set of classes. ... in the past i used> ".getinstance" in java to get a single instance of a class contained in a application variable to ensure i dont have 3-4> different instances hitting the database and worse now writing 3-4 different> xml files. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Can I programmatically IMPORT data from XML into Access?
    ... The Access.Application object has an ImportXML method. ... You might be better advised to investigate why writing to the database is ... > longer than writing from DataSet to XML, and than manually importing that ... > content into the .mdb file. ...
    (microsoft.public.access.externaldata)