View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
GS[_2_] GS[_2_] is offline
external usenet poster
 
Posts: 3,514
Default get column name by ADOX

First thing to do is turn on the 'Require Variable Declaration' option
on the 'Editor' tab of the VBE Options dialog. This will cause code
execution to halt as "item1" and "szBookName" are not declared locally
in this sub, and since it doesn't appear to use module/global naming
convention techniques I assume you're letting VBA implicitly decide its
type. If you declared these elsewhere at module or global level then
you should make that apparent in your naming convention:

Global scope: g_szBookName; g_item1
Module scope: m_szBookName; m_item1
This will make your code much easier to be understood by others!<g

Anything we let VBA do by implicity requires extra processing and so
results in poor code performance when we have lots of that happening
all over the place.<g

You bothered to both explicitly declare all the other variables so why
not this one?, AND explictly destroyed the objects you created rather
than let VBA do this implicitly!

---
To get the name of Fields(1) into the listbox you have to set the
ColumnCount property to '2'.

The ListBox1 index starts at '0'. That concludes, then, that...

ListBox1.List(0, 0) = tbl.Name
ListBox1.List(0, 1) = tbl.Fields(1).Name

It might be easier to add the table names first and then loop the
listbox to add the field names...

Dim i As Integer
For i = 0 to ListBox1.ListCount - 1
With ListBox1
.List(i, 1) = cat.Tables(.List(i, 0)).Name
End With 'ListBox1
Next 'i

---
Another way...

Dim sTableNames As String, sTableName As String
For Each tbl In cat.Tables
sTableName = Replace(tbl.Name, "$", "")
sTableNames = sTableNames & "|" & sTableName
Next 'tbl

Dim vTableNames
vTableNames = Split(Mid$(sTableNames, 2), "|")
Dim lNumTables As Long, i As Long
lNumTables = UBound(vTableNames)
Dim aListItems(lNumTables, 1)
For i = LBound(vTableNames) To UBound(vTableNames)
aListItems(i, 0) = vTableNames(i)
aListItems(i, 1) = cat.Tables(i + 1).Name
Next 'i
ListBox1.ColumnCount = 2
ListBox1.List = aListItems

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc