Can I programmatically IMPORT data from XML into Access?

[Ariela-G]

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.

 

[Brendan Reynolds]

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.

 

[Ariela-G]

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]

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.

 

[Ariela-G]

I deeply appreciate your answers. They lead me to conduct few tests of my own
in order to pick a preferred solution.
From your test results I realize that my result is quite expected (writing
~35,000 records in about 50 seconds).

Thank you,
Ariela