Access VBA Query Syntax to List Field Names of Table

I am trying to create a VBA procedure in Access 2007 that will populate a listbox with field names from a table. The list box would have 2 columns - field name and caption. Can someone point me in the right direction for the query syntax used to select the field names from a table using ADO? Thanks!
LVL 1
jandrews404Asked:
Who is Participating?
 
MikeMcEvoyCommented:
You can use ADOX.

You will have to add references to  the Microsoft ActiveX Data Objects Library as well as the Microsoft ADO Ext For Dll and Security.

This example prints the info in debug. You can alter it to insert the items to a table or recordset.


Sub GetTableNamesWithFieldsUsingADOX()
  Dim Conn As New ADODB.Connection

  'Open connection you want To get database objects
  Set Conn = CurrentProject.Connection

  'Create catalog object
  Dim Catalog As New ADOX.Catalog
  Set Catalog.ActiveConnection = Conn

  'List tables And columns
  Dim Table As ADOX.Table, Column As ADOX.Column
  For Each Table In Catalog.Tables
    For Each Column In Table.Columns
      Debug.Print Table.Name & ", " & Column.Name
    Next
  Next
End Sub

Open in new window

0
 
aikimarkCommented:
@jandrews404

What is "caption"?
0
 
Tony HungateDirector of TrainingCommented:
Try  setting the RowSourceType of the listbox property to Field List and the RowSource to the table name.

~:TLH:~
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Nick67Commented:
That's an interesting question!
If the field has no caption then the routine throws error 3270.
So, that error is caught

This code assume DAO 3.6
Disambiguate if you have ADO on the go.
Call the function and pass in the desired table name from somewhere on the form in question.
The listbox needs to be set to Value List, and 2 columns

Again, that was a neat question!
Have a gander here, too
http://bytes.com/topic/access/answers/870485-update-tables-caption
Public Function BuildValueList(TableName As String)
On Error GoTo myerror
Dim FinalString As String
Dim db As Database
Dim rs As Recordset
Dim myfield As Field
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from " & TableName & " where 1 = 2;", dbOpenDynaset, dbSeeChanges)

For Each myfield In rs.Fields
    FinalString = FinalString & myfield.Name & ";" & Nz(myfield.Properties("Caption"), "no caption") & ";"
Next myfield

Me.List0.RowSource = FinalString  '<--------------------replace List0 with your listbox name

Exit Function

myerror:
If Err.Number = 3270 Then 'no existing caption
    FinalString = FinalString & myfield.Name & ";" & "no caption" & ";"
    Resume Next
End If

End Function

Open in new window

0
 
Jeffrey CoachmanMIS LiasonCommented:
You can also just use a combobox.

Set the RowSoureType property to: Field List
Set the RowSource property to the name of the table

Click the dropdown on the combobox, and a list of the fields in the table are listed.
0
 
Nick67Commented:
@boag2000
He wants the field captions as a column in the listbox. I don't think a field list will do that
0
 
Jeffrey CoachmanMIS LiasonCommented:
Nick,

OK, I just read this as the question title...
0
 
Nick67Commented:
It's a neat question.
Until I poked at it, I didn't realize that the labels of bound controls would pick up the field caption.
All of those gazillions of labels whose caption I changed from MyFieldName to My Field Name that could have been done for me if I had just added a caption to the field.
All of the combo boxes bound to ID fields that I could have had a caption for and not needed to deep-six the ID from the label.

Sigh.

You learn something new every day :)
0
 
Jeffrey CoachmanMIS LiasonCommented:
Nick,
<You learn something new every day :)>
Yes,
   "Everyday is a school day"


The catch is that sometimes I change the Label caption and forget to make the corresponding change to the Table field caption property.
:-(

BTW, on a side note...
Just curious...
Is there a reason why you don't contribute more as an Expert here?

Jeff

0
 
Nick67Commented:
Time constraints.
Expertise constraints.

Politeness.  If someone else is working with an author and going in the right direction, there's no reason to chime in.
You, @DatabaseMx, @JDettman, and @LSMConsulting seem to have the same skill set I do, and do an excellent job of answering questions :)

If I see something Access-related languishing, or not going the right direction, I comment.

Nick67
0
 
aikimarkCommented:
@Nick67

Even if other experts are headed in the right direction, you might offer a slightly different code structure or performance boost that would add to the quality of the solution.  Sometimes, it is nice to have another expert support my approach.
0
 
Jeffrey CoachmanMIS LiasonCommented:
jandrews404,

You have several solution on the table....
0
 
Nick67Commented:
<grin>
By the right direction, I mean solved in the way I think would be elegant, most efficient, compact, well-documented and beneficial to the author and others who might google it afterwards.
</grin>
0
 
jandrews404Author Commented:
Sorry for the delayed response. That was exactly what I was looking for.....thanks!!
0
 
Nick67Commented:
<grin>
I'm not sure how your accepted answer gave you the field captions, never mind put them in a listbox -- but it did use ADO.
If you did alter the solution's code to get what you needed, can you post it?
There's a DAO solution here, but no ADO one.
It'd be good to have them both
</grin>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.