Solved

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

Posted on 2013-05-29
3
634 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

910 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

21 Experts available now in Live!

Get 1:1 Help Now