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,......M B67 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.Op en()

Dim schemaTable As DataTable
schemaTable = myConnection.Ge tOleDbSchemaTab le(OleDb.OleDbS chemaGuid.Table s, New Object() {Nothing, Nothing, Nothing, "TABLE"})

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

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

myXWriter.Forma tting = Formatting.Inde nted
myXWriter.Inden tation = 2

Dim i As Integer
For i = 0 To schemaTable.Row s.Count - 1
Dim pTableName As String = schemaTable.Row s(i)!TABLE_NAME .ToString
If Microsoft.Visua lBasic.Left(pTa bleName, 2) = "MD" Then
Dim strSQL As String = "Select ItemID,pl1 from " & pTableName & " where ItemID <> 0"
Dim myCommand As OleDbCommand = New OleDbCommand(st rSQL, myConnection)
Dim myReader As OleDbDataReader = myCommand.Execu teReader
While myReader.Read
myXWriter.Write StartElement("M enuItem")
myXWriter.Write AttributeString ("CV", myReader(0))
myXWriter.Write AttributeString ("PL1", myReader(1))
myXWriter.Write EndElement()
End While
myReader.Close( )
ElseIf Microsoft.Visua lBasic.Left(pTa bleName, 2) = "MB" Then
Dim strSQL As String = "Select ItemID,pl1 from " & pTableName & " where ItemID <> 0"
Dim myCommand As OleDbCommand = New OleDbCommand(st rSQL, myConnection)
Dim myReader As OleDbDataReader = myCommand.Execu teReader
While myReader.Read
myXWriter.Write StartElement("M enuItem")
myXWriter.Write AttributeString ("CV", myReader(0))
myXWriter.Write AttributeString ("PL1", myReader(1))
myXWriter.Write EndElement()
End While
myReader.Close( )
End If
Next

myXWriter.Write EndDocument()
myXWriter.Flush ()

myStream.Seek(0 , SeekOrigin.Begi n)
Dim strConfig2 As String = New StreamReader(my Stream).ReadToE nd()
myWriter = File.CreateText ("C:\Program\Ex ample.xml")
myWriter.WriteL ine(strConfig2)
myWriter.Close( )