Filling a combo box with table names of a database 1

[ems]

Hi,

could you help me please. I need to fill a combo box with all the names of the tables in a database I am using. Any ideas of a good way to go about it.

Thanks in advance
ems

 

[Qaroven]

What sort of database?
In SQL Server easy enough ..
(QA example)

use <database name>
select name from sysobjects where type = 'u' order by name asc

Never done it in Access, but trying just now ..

select name from msysobjects where flags=0 and parentid=251658241 order by name asc

that seems to work, however I'm not sure if those values will be the same in your case..

Ben
+61 403 395 052

 

[ems]

Ben,

it is an Access DB. CAn I just run a few things by you. I have a DB called QuizDetails and there a about 10 tables in it.
On a form I have a combo box that I want to display all these 10 (or more ..if more tables are added) tables of the QuizDetails DB.

Thanks for the code but I don't really understand how it works. Can you shed some light on it?

Thanks in advance
ems

 

[Qaroven]

So if you have tables:
table1, table2, table3, etc in the QuizDetails database
you want your combobox to list table1, table2, etc, correct?

OK in Access go into Tools >> Options and tick the Show System Objects flag.

Now when you look through the tables in the database window should also list MSysObjects, etc.

Right-click on the combobox and display it's properties.

Within the Data tab there is the RowSource property.

Copy the above SQL statement (the Access one) into that property, then change to form view and test.

If it doesn't work you may have to change some of the values above to match values in your MSysObjects table (generally there seems to be a value in parentid which is the same for all tables - not surprising - but not sure if that changes DB to DB)


Ben
+61 403 395 052

 

[ems]

Thanks Ben,

that works perfectly.
ems

 

[MatsHulten]

Or you could do it the ADO way.

Set a referance to ADOX and type the following:

    Dim oCat As New ADOX.Catalog
    Dim oTable As New ADOX.Table
    Dim strResult As String
    
    oCat.ActiveConnection 
 "provider=Microsoft.jet.oledb.4.0;" & _
data source = C:\test.mdb"
    
    For Each oTable In oCat.Tables
        strResult = strResult & oTable.Name & " " & _
                    oTable.Type & vbCrLf
    Next
    
    MsgBox strResult

Good Luck!
-Mats