-3

I'm trying to get the names of all tables of ms access database and store in a array. Is there any way to do that? or modification of this, I copied this code so I DONT KNOW WHAT IS IT FOR

    con1.Open()
    Dim Restrictions() As String = {Nothing, Nothing, "Table1", Nothing}
    Dim CollectionName As String = "Columns"
    Dim dt As DataTable = con1.GetSchema(CollectionName, Restrictions)
    For Each TableRow As DataRow In dt.Rows
        Console.WriteLine(TableRow.Item("COLUMN_NAME").ToString)
    Next
    con1.Close()
Vivek
  • 5
  • 4
  • Do you know what GetSchema does? [Read the docs](https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbconnection.getschema?view=dotnet-plat-ext-6.0) – Steve Jan 17 '22 at 13:50
  • I absolutely know nothing about getschema. I'm just trying to find get the code cause I'm new to this. Can you please provide me the exact code in vb. Net . I'm quite confused. – Vivek Jan 17 '22 at 13:54
  • GetSchema returns a DataTable where each row describes some information about the database according to the Collection and Restriction parameter. In your case you are asking to get all the informations about the columns in the table "Table1". One of these informations is the COLUMN_NAME. So you just need to put each COLUMN_NAME value in an array of strings instead of writing it on the Console. Then you can return this array from the method. – Steve Jan 17 '22 at 14:06
  • Actually, I copied this code from somewhere but I want to store all the names of tables in the database in a array – Vivek Jan 17 '22 at 14:08
  • This is the question I'm looking an answer to, https://stackoverflow.com/questions/70738360/count-all-records-in-all-tables-in-ms-access-database-in-vb-net?noredirect=1#comment125061106_70738360 – Vivek Jan 17 '22 at 14:09
  • Then use _con.GetSchema("TABLES", Restrictions)_ where Restrictions is an array of 4 nothing values. The info is in a column named _TABLE_NAME_ – Steve Jan 17 '22 at 14:19
  • I know this might be a silly question but info for what? – Vivek Jan 17 '22 at 14:24

1 Answers1

2

To get all the table's names in your database you should use a restriction with 4 Nothing values and a collection named TABLES.

con1.Open()

' No filter on the returned values
Dim Restrictions() As String = {Nothing, Nothing, Nothing, Nothing}

' Just interested in the TABLES collection
Dim CollectionName As String = "TABLES"

' Get the datatable with informations about the tables in the current db
Dim dt As DataTable = con1.GetSchema(CollectionName, Restrictions)
con.Close()

Now to pass everything back to the calling code in an array of strings you could use

Dim names = dt.Rows.Cast(Of DataRow).Select(Function(x) x("TABLE_NAME")).ToArray()
return names
Steve
  • 213,761
  • 22
  • 232
  • 286
  • Is it necessary to specify some value in array because my database is dynamic and there are more than 4 tables – Vivek Jan 17 '22 at 14:29
  • The _names_ array is created dynamically and will contains all the tables in your database. But did you try it? – Steve Jan 17 '22 at 14:30
  • I'm just going to try it but I just want to know what is the use of restrictions array? – Vivek Jan 17 '22 at 14:40
  • This documentation explains everything https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-database-schema-information – Steve Jan 17 '22 at 15:22
  • Thanks so much for this bro. But this is actually the step 1 of the problem I'm trying to solve. Can you help me with this question: https://stackoverflow.com/questions/70738360/count-all-records-in-all-tables-in-ms-access-database-in-vb-net?noredirect=1#comment125061106_70738360 – Vivek Jan 17 '22 at 16:01
  • bro can you please answer this question:- https://stackoverflow.com/questions/70768873/change-value-of-selected-records-from-ms-access-database-in-datagridview-in-vb-n – Vivek Jan 19 '22 at 10:25