We help IT Professionals succeed at work.

Convert dataset to new access database

balabaster
balabaster asked
on
Medium Priority
755 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
Comment
Watch Question

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
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.