[RESOLVED] 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.

[lmf232s]

I dont believe access has a system table that you can query to get a list of all the tables in the DB. Youll want to search on that because if you can then you can just create a select statement that will return the names of all the tables. Then while you loop that query you can do your other stuff.

If this is not available in access then the only think I can think of is to create a table and store all the names of your tables in that table. Then you can query the correct tables and again as you loop the names of the tables you can have an inner query to get your data from those tables.

[lmf232s]

Ok that was fast, I just found the system tables in access and I believe you will be able to query them.

Here is the article that I just skimed and it shows you how to view the system tables and what to look for.
http://www.databasejournal.com/features/msaccess/article.php/10895_3528491_1

Tools > Options > Make sure System Tables is selected in the Show gropbox.

Open the table MSysObjects. Your interested in the Name and Type columns. If the Type Column is 1 then its a table.

So you could query this table and select all tables where Type = 1 and Name is Like 'MD%' or 'MB%'

Here is a simple example of how it might work.

SELECT NAME
FROM MSysObjects
WHERE Type = 1
AND (NAME Like 'MD%' OR Name Like 'MB%')

If Not oRS.EOF Then
sSQL = "SELECT * FROM " & oRS("NAME") & " WHERE SOMETHING = SOMETHING"
'execute SQL
If Not oRS2.EOF then

End If
END IF

[remya1000]

Heah 1mf232s its working... now it takes all the table names Like 'MD%' and Like 'MB%'. and i can do the remaining things once i get the table names.

and the link you send is so helpfull to me. thanks a lot for your help. really thanks a lot for that one....