Populating a cboBox with table names. 1

[rickyoswaldiow]

In my sample program I have a vba control that lets the user create and delete tables at will. On another form I want the user to add fields to a table (s)he has created, how do I populate a combo box with all of the tables that are currently in existence?

 

[Remou]

You can use the MSysObjects table

SELECT [Name] FROM MSysObjects WHERE [Type]=1

A Type of 6 is a linked table.

 

[rickyoswaldiow]

Could you expand on that a little? I'm not sure how I would integrate that into my application.
Would I put it inside a loop on the form load to populate the combo box?

 

[Remou]

Just set the RowSource of the combo to the Select statement above. Beware of tampering with the data in system tables, use it by all means, but do not tamper.

Microsoft says that system tables are an unsupported feature, however, they are so widely used that it seems unlikely that they will be dropped.

 

[rickyoswaldiow]

Maybe I have not explained what I requrie properly. I have had a look on google but I can't seem to find anything that works for me...

Thus far I have two forms:

To create a table I have written this code:

Private Sub cmdCreateTable_Click()
    Dim strSQL As String
    Dim cnConn As ADODB.Connection
    
    Set cnConn = CurrentProject.Connection
    
    strSQL = "INSERT INTO " & Me.cboTables & _
             "  ADD COLUMN " & Me.txtFieldName & _
             "      " & DataType
    
    cnConn.Execute strSQL
    
    MsgBox "Field added to table!", vbOKOnly, "Field Command"
    
    cnConn.Close
    Set cnConn = Nothing
    
End Sub

However, notice the reference to cboTables in the strSQL variable. This combo is unpopulated because I don't know how to fill it with the names of the tables that the user has created on frmTableLifer.

I could do a workaround and create a global array that holds the names of the tables... there are a few methods I can think of. I was just hoping there was a little function I can use to directly read the names of all tables that currently exist?

 

[Remou]

A list of all tables can be obtained in the way that I indicated, that is if you set the RowSource of a combobox to:

[tt]SELECT [Name] FROM MSysObjects WHERE [Type]=1[/tt]

The combo will list all tables in the database, including system tables but excluding linked tables. You can exclude system tables by altering the SQL:

[tt]SELECT [Name] FROM MSysObjects WHERE [Type]=1 AND Left([Name],4)<>"MSys"[/tt]

Are you now saying that you want some list other than all of the tables that are currently in existence? as originally requested?

 

[rickyoswaldiow]

Ah I see, I did not realist that there were system tables included, which is obviously what I am seeing here. Thanks again for the help Remou, it works perfectly.