Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Convert dataset to new access database

Posted on 2006-11-07
3
742 Views
Last Modified: 2010-05-18
Hey,

I have a bunch of XML files...of unknown structure/origin - but all having the same schema.  I need to write a tool to transform this XML document (en-masse) into a database format.  The XML documents all have the same structure, but the tool needs to be flexible enough to work with any XML document/schema.  I've got the tool opening the XML file into a dataset but here is where I come unstuck.  I've got the user selecting the MDB to import the file content to and want my application to analyse the database to see whether the datatables from the dataset exist in the database, if they do, then append the data for the corresponding table, else create the table dynamically from the schema structure of the datatable within the dataset.  Given that the dataset is dynamically built at the time the XML document is loaded, how would I go about dynamically creating the necessary tables in the database and/or appending the data from each datatable within the dataset?

Cheers
0
Comment
Question by:balabaster
3 Comments
 

Expert Comment

by:Observer123
ID: 17897909
I think you can save your xml in cvs files, which is priviously linked to access database and update and filing them, the information can be available in the access database
0
 
LVL 41

Accepted Solution

by:
graye earned 500 total points
ID: 17898410
I've got a VB.Net module that will do what I think you're after...

It's designed for "archiving" a DataSet to a new Access mdb file... so it contains lots of code for creating the MDB file (if it doesn't exist) and creating the Table (if it doesn't exist).  Then towards the bottom of the code, it actually does the copying of data.

It uses COM-based ADOX library so it's a bit faster than a "native" .Net solution.

it works like this:

    Copy_dt2MDB(DataTable, "C:\Testing.mdb", False, "Table1")
               
http://home.hot.rr.com/graye/Temp/dt2mdb.vb
0
 

Author Comment

by:balabaster
ID: 17904029
Thanks, that was fantastic...had to make a couple of minor mods to cater for dataTables having names of T-SQL reserved keywords...which was an issue as the DataSet was dynamically built from XML files having nodes named as such.  You might want to review the module and encase the TableName and FieldNames with "[" and "]".  Cheers though, all worked really well!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question