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.OleDbSchema Guid.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()