Solved

vb.net create and populate multiple tables in blank access database from multiple xml and xsd files

Posted on 2013-05-29
3
642 Views
Last Modified: 2013-05-30
After more than a decade of maintaining a large collection of configuration information in an access database, someone decided it would be swell to switch to xml.  Our existing tools run a series of queries against the access database to extract the needed configuration data.

Now the data exists in 70 xml files with 4 xsd files.

I can parse all of the files and create a dataset containing 21 datatables.

My question is, how do I programmatically  create the 21 tables in a blank access database and populate them from the data contained in the dataset?

The code below seems to be working fine:
        Dim objDataSet As DataSet
        objDataSet = New DataSet

        Dim FileSpec As String = "*.xml"
        For Each foundFile As String In My.Computer.FileSystem.GetFiles(SourceFolder & "\", _
            FileIO.SearchOption.SearchTopLevelOnly, FileSpec)
            objDataSet.ReadXml(foundFile)
            Dim objDataTable As DataTable

            For Each objDataTable In objDataSet.Tables
                objDataTable.BeginLoadData()
            Next

            objDataSet.ReadXml(foundFile)

            For Each objDataTable In objDataSet.Tables
                objDataTable.EndLoadData()
            Next

        Next

        Dim MBData As String = objDataSet.Tables.Count & " Tables Created" & vbCrLf
        For Each table In objDataSet.Tables
            MBData += "Table = " & table.TableName & _
                " - Row Count = " & table.rows.count & _
                " - Column Count = " & table.columns.count & vbCrLf
        Next table
        MessageBox.Show(MBData)

Open in new window

I'm just looking for a simple method to get the dataset into a blank access database.

Thanks,
0
Comment
Question by:doughamilton
[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
 
LVL 85
ID: 39207275
If you've got a Dataset loaded, you can just persist that to an Access database, it would seem.

A Dataset is very similar to a Recordset; you traverse the rows in that Dataset's Tables, and you can do with those rows as you wish. For example:

Dim str As String
Dim cmd As New System.Data.OleDb.OleDbCommand
Dim con As New System.Data.OleDb.OleDbConnection

con.ConnectionString = "Your Access DB Connection"
con.Open()

cmd.Connection = con

For Each dtr As DataRow in objDataset.Tables(0).Rows
  str = "INSERT INTO YourTable(Col1, Col2, Col3) VALUES(" & dtr.Item("Field1") & "," & dtr.Item("Field2") & "," & dtr.Item("Field3") & ")"

  cmd.CommandText = str
  cmd.ExecuteNonQuery  
Next dtr

You would obviously have to delimit your String and Date values per Access rules.

Also, if you need to do this for each Table in the Dataset, you'd have to iterate the tables also:

For each tbl As DataTable in objDataset.Tables
  For each dtr As Datarow in tbl.Rows
    etc etc
  Next dtr
Next tbl
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 39207518
If you want to create tables on the fly this code can do it for you:
Public Function ExportData(FacCode As String)

Dim SQL As String
Dim DB As DAO.Database
Dim RS As DAO.Recordset

Dim TableName As DAO.TableDef
Dim FieldName As DAO.Field
Dim FieldProperty As DAO.Property

Dim Qry As DAO.QueryDef

Dim I As Integer
Dim ResID As Integer

If DoesTblExist("LocTbl") = True Then
    DoCmd.SetWarnings False
    DoCmd.DeleteObject acTable, "LocTbl"
    DoCmd.SetWarnings True
End If

Set DB = CurrentDb()
Set TableName = DB.CreateTableDef("LocTbl")
    
    With TableName
        .Fields.Append .CreateField("Facility_Code", dbText, 5)
        .Fields.Append .CreateField("Client_Id_Number", dbText, 35)
        .Fields.Append .CreateField("Effective_Date", dbDate)
        .Fields.Append .CreateField("Adt_tofrom", dbInteger)
        .Fields.Append .CreateField("Primary_payer_code", dbText, 20)
        .Fields.Append .CreateField("Admission_type_code", dbInteger)
        .Fields.Append .CreateField("Admission_source_code", dbInteger)
        .Fields.Append .CreateField("UnitDescription", dbText, 35)
        .Fields.Append .CreateField("SeqNum", dbInteger)
        .Fields.Append .CreateField("IdNum", dbLong)
    End With
    
    With TableName
        .Fields("Facility_Code").DefaultValue = FacCode
        .Fields("IdNum").Attributes = dbAutoIncrField
    End With

DB.TableDefs.Append TableName

Set FieldName = TableName.Fields("Effective_date")
Set FieldProperty = FieldName.CreateProperty("Format", dbText, "mm/dd/yyyy")
FieldName.Properties.Append FieldProperty

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

728 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