loop through tables inside .mdb file using VB.NET

[remya1000]

i'm using VB.NET and Microsoft Access.

and in one of my Access table is called Emp.mdb. and in that Emp.mdb i have lots of tables like MD1, MD2,......MD58 and MB1,MB2,......MB67 and some other tables too.

so when a button is pressed i need to check all the tables MD# and MB# and retrive some data to create an XML. and other tables were also there, but i don't need to check that tables. and the Number of MD tables and MB tables varies. so i need to check all the tables with MB# and MB#.


for checking Single table i used this code. how can i loop through all the tables MB# and MD# in Emp.mdb.

TextBox1.Text = "C:\Program\Emp.mdb"
If File.Exists(TextBox1.Text) Then
            Dim strSQL As String = "Select ItemID,pl11,pl12 from MG10 where ItemID <> 0"
            Dim myConnection As New OleDbConnection(strConn)
            myConnection.Open()
            Dim myCommand As OleDbCommand = New OleDbCommand(strSQL, myConnection)
            Dim myReader As OleDbDataReader = myCommand.ExecuteReader
            
            Dim myXWriter As XmlTextWriter
            Dim myWriter As StreamWriter
            Dim myStream As MemoryStream
            myStream = New MemoryStream

            myXWriter = New XmlTextWriter(myStream, Encoding.UTF8)
            myXWriter.Formatting = Formatting.Indented
            myXWriter.Indentation = 2

            myXWriter.WriteStartDocument()
            myXWriter.WriteStartElement("Employee")

           While myReader.Read
                myXWriter.WriteStartElement("Item")
                myXWriter.WriteAttributeString("CV", myReader(0))
                myXWriter.WriteAttributeString("PL1", myReader(1))
               myXWriter.WriteAttributeString("PL2", myReader(2))
                myXWriter.WriteEndElement()
            End While

            myXWriter.WriteFullEndElement()
            myXWriter.WriteFullEndElement()
            myXWriter.WriteEndDocument()
            myXWriter.Flush()

           myStream.Seek(0, SeekOrigin.Begin)

            Dim strConfig2 As String = New StreamReader(myStream).ReadToEnd()
            myWriter = File.CreateText("C:\Example.xml")
            myWriter.WriteLine(strConfig2)
            myWriter.Close()
        End If

if you have any idea please let me know. and if you can provide an example then it will be great helpfull for me.

thanks in advanace.

 

[remya1000]

heah its working too.... here is the code that works....

Dim myConnection As New OleDbConnection(strConn)      myConnection.Open()

 Dim schemaTable As DataTable
 schemaTable = myConnection.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

 Dim myXWriter As XmlTextWriter
 Dim myWriter As StreamWriter
 Dim myStream As MemoryStream

 myStream = New MemoryStream
 myXWriter = New XmlTextWriter(myStream, Encoding.UTF8)

 myXWriter.Formatting = Formatting.Indented
 myXWriter.Indentation = 2
            
 Dim i As Integer
 For i = 0 To schemaTable.Rows.Count - 1
 Dim pTableName As String = schemaTable.Rows(i)!TABLE_NAME.ToString
 If Microsoft.VisualBasic.Left(pTableName, 2) = "MD" Then
     Dim strSQL As String = "Select ItemID,pl1 from " & pTableName & " where ItemID <> 0"
     Dim myCommand As OleDbCommand = New OleDbCommand(strSQL, myConnection)
     Dim myReader As OleDbDataReader = myCommand.ExecuteReader
     While myReader.Read
          myXWriter.WriteStartElement("MenuItem")
          myXWriter.WriteAttributeString("CV", myReader(0))
          myXWriter.WriteAttributeString("PL1", myReader(1))
          myXWriter.WriteEndElement()
     End While
     myReader.Close()
 ElseIf Microsoft.VisualBasic.Left(pTableName, 2) = "MB" Then
     Dim strSQL As String = "Select ItemID,pl1 from " & pTableName & " where ItemID <> 0"
     Dim myCommand As OleDbCommand = New OleDbCommand(strSQL, myConnection)
     Dim myReader As OleDbDataReader = myCommand.ExecuteReader
     While myReader.Read
         myXWriter.WriteStartElement("MenuItem")
         myXWriter.WriteAttributeString("CV", myReader(0))
         myXWriter.WriteAttributeString("PL1", myReader(1))
         myXWriter.WriteEndElement()
     End While
     myReader.Close()
  End If
  Next

 myXWriter.WriteEndDocument()
 myXWriter.Flush()

 myStream.Seek(0, SeekOrigin.Begin)
 Dim strConfig2 As String = New StreamReader(myStream).ReadToEnd()
 myWriter = File.CreateText("C:\Program\Example.xml")
 myWriter.WriteLine(strConfig2)
 myWriter.Close()