Solved

Convert dataset to new access database

Posted on 2006-11-07
3
739 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A short article about a problem I had getting the GPS LocationListener working.
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.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

929 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now