?
Solved

Convert dataset to new access database

Posted on 2006-11-07
3
Medium Priority
?
745 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
[X]
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
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 2000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

This is about my first experience with programming Arduino.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Progress
Introduction to Processes

801 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