Solved

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

Posted on 2013-05-29
3
625 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
3 Comments
 
LVL 84
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

759 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

19 Experts available now in Live!

Get 1:1 Help Now