Solved

Convert dataset to new access database

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
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 …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

747 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

13 Experts available now in Live!

Get 1:1 Help Now