Filling combo boxes with the Table names of the Database I am using

[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

 

[sunaj]

Here's the way I do it:

Public catNew As New ADOX.Catalog
Dim i as integer

'list all tables in CmbTables
For i = 0 To catNew.Tables.Count - 1
If Left(catNew.Tables(i).Name, 3) = "Tbl" Then _
CmbTables.AddItem catNew.Tables(i).Name
Next i
CmbTables = CmbTables.List(0) 'select first column

My ComboBox is called CmbTables. All my Tablenames start with "Tbl", but if yours don't I'm sure you can filter your tablenames out from what catNew.tables returns.

Sunaj

 

[ems]

Sunaj,

thanks for your reply. Tried that. Where have you this code..the Form_load() ?
Is there anything else I need?

The system is not recognising "Public catNew As New ADOX.Catalog"
Compile error
Invalid attribute in sub or function


This is a major part of my project and your help is really appreciated. Help!!

ems

 

[sunaj]

Hi,

Form_Load() would be a good place to put it. I use SS tab and run the code then the user changes tab.

You have to have a reference to Microsoft ADO Ext. 2.5 for dll and security (MSADOX.dll). I assume that you allready have a reference to Microsoft ActiveX Objects 2.5 Library (MSADO25.tlb). Or other version of these libraries.

The catalog (CatNew) does not need to be public unless you want to use it public...

Sunaj

 

[ems]

Sunaj,

I have put this code into the Form_load().
I have left out the IF stmt as I don't want to filter the tables.

The combo box does not contain any information when I run it.

I have a adodc connecting the database.

Dim catNew As New ADOX.Catalog
Dim i As Integer
'Dim catNew As String

'list all tables in CmbTables
For i = 0 To catNew.Tables.Count - 1
'If Left(catNew.Tables(i).Name, 3) = "Tbl" Then
cmbQ1Table1.AddItem catNew.Tables(i).Name
Next i
' cmbQ1Table1 = cmbQ1Table1.List(0) 'select first column
'End If

End Sub

Any ideas.
Thanks

 

[sunaj]

Here's some code that work in my compiler:

Good luck
Sunaj

'Copy this code into a form with a combobox (combo1)
'Set references to Microsoft ADO Ext. 2.5 for dll and security (MSADOX.dll)
'and Microsoft ActiveX Objects 2.5 Library (MSADO25.tlb).

Option Explicit
Dim con As ADODB.Connection
Dim catNew As New ADOX.Catalog
Dim rst As Recordset

Private Sub Form_Load()
Dim StrCon As String, i As Integer

'Create database
If Dir(&quot;c:\test.mdb&quot <> &quot;&quot; Then Kill &quot;c:\test.mdb&quot;
StrCon = catNew.Create(&quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\test.mdb&quot
Set con = catNew.ActiveConnection

'create Tabel project
con.Execute &quot;CREATE TABLE TblProject (ID COUNTER CONSTRAINT PK_projectID PRIMARY KEY,&quot; + _
&quot;name TEXT(50), comment TEXT(50))&quot;

'create Tabel Persons
con.Execute &quot;CREATE TABLE TblPerson (ID COUNTER CONSTRAINT PK_personID PRIMARY KEY,&quot; + _
&quot; projectID LONG CONSTRAINT FK_projectID REFERENCES Tblproject (ID),&quot; + _
&quot; surname TEXT(25), firstname TEXT(30),email TEXT(30))&quot;

'Insert values into tblproject
con.Execute (&quot;INSERT INTO TblProject (name) VALUES ('TESTPROJECT')&quot

'Insert values into tblPerson incl foreign key
Set rst = con.Execute(&quot;SELECT ID FROM TblProject WHERE name='TESTPROJECT'&quot
con.Execute (&quot;INSERT INTO TblPerson (projectID,surname) VALUES (&quot; + CStr(rst.Fields(0)) + &quot;,'testname')&quot

'populate combobox with table names
For i = 0 To catNew.Tables.Count - 1
If Left(catNew.Tables(i).Name, 3) = &quot;Tbl&quot; Then _
Combo1.AddItem catNew.Tables(i).Name
Next i
Combo1 = Combo1.List(0) 'select first column
con.Close
End Sub