[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 651
  • Last Modified:

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

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
doughamilton
Asked:
doughamilton
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Jim P.Commented:
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now